UPDLOCK when using a ISEMPTY

rcverbeek

Hi,

Recently one of our customers is having deadlock issues on their NAV 2018 environment. In one specific case we captured the deadlock trace using SQL Profiler. The victim process was trying to get an exclusive lock for performing a DELETE. The other process had this SQL command:

SELECT TOP 1 NULL  FROM "some db".dbo."some prefix$some table" "77047"  
WITH(UPDLOCK)  
WHERE ("77047"."Document No_"=@0 AND "77047"."AS400 History"=@1) 
 OPTION(OPTIMIZE FOR UNKNOWN)

To my knowledge the IS NULL is only issued with an ISEMPTY statement. There are two ISEMPTY statemenst found in the entire code.
The UPDLOCK hint baffles me: why on earth does an ISEMPTY lead to an UPDLOCK hint? There are no LOCKTABLE's involved.

Any ideas?

Thanks

skiddoo

I testet around a bit and could reproduce it. THE MODIFY behaves like a LOCKTABLE

ShipmentMethod.FINDFIRST;
ShipmentMethod.MODIFY;
IF ShipmentMethod.ISEMPTY THEN;

SELECT TOP 1 NULL  FROM "<Company>$Shipment Method" "10"  

skiddoo

Hey rcverbeek,

I'm facing a similar issue. Do you found the root cause?

Regards
skiddoo

rcverbeek

Hi Skiddoo, unfortunately we haven't found the cause.

skiddoo

That's too bad. Thanks anyway. I hope I remember to update this thread once I found the cause.

skiddoo

I testet around a bit and could reproduce it. THE MODIFY behaves like a LOCKTABLE

ShipmentMethod.FINDFIRST;
ShipmentMethod.MODIFY;
IF ShipmentMethod.ISEMPTY THEN;

SELECT TOP 1 NULL  FROM "<Company>$Shipment Method" "10"  

rcverbeek

Thanks skiddoo, glad to see you found it!!