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"
WHERE ("77047"."Document No_"[email protected] AND "77047"."AS400 History"[email protected])
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.
I'm facing a similar issue. Do you found the root cause?