UPDLOCK when using a ISEMPTY

rcverbeekrcverbeek Member Posts: 32
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

Best Answer

  • skiddooskiddoo Member Posts: 19
    edited 2021-05-05 Answer ✓
    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"  
    

Answers

  • skiddooskiddoo Member Posts: 19
    Hey rcverbeek,

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

    Regards
    skiddoo
  • rcverbeekrcverbeek Member Posts: 32
    Hi Skiddoo, unfortunately we haven't found the cause.
  • skiddooskiddoo Member Posts: 19
    That's too bad. Thanks anyway. I hope I remember to update this thread once I found the cause.
  • skiddooskiddoo Member Posts: 19
    edited 2021-05-05 Answer ✓
    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"  
    
  • rcverbeekrcverbeek Member Posts: 32
    Thanks skiddoo, glad to see you found it!!
Sign In or Register to comment.