We are running into deadlocks when we run concurrent instances of following query :
BEGIN TRAN IF NOT EXISTS (SELECT TOP 1 * FROM Table1 WITH (NOLOCK) WHERE Col1 = Val1 AND Col2 = Val2) BEGIN INSERT INTO Table1 (ID, Col1, Col2) VALUES(-1, Val1, Val2) END COMMIT TRAN
The deadlock results in INSERT being aborted. I am trying to understand the working of NOLOCK hint for the above. This link says that NOLOCK acquires table level shared lock and database level schema lock. However, there is no page level lock. So if there is no shared lock to exclusive lock conversion (ie, from SELECT to INSERT), why would there be a deadlock ? Is there lock escalation with NOLOCK if the SELECT query is run over large table ? Am I better off using UPDLOCK instead of NOLOCK for SELECT, as there will not be lock conversion conflicts ?