Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
1  
Please see this: samsaffron.com/blog/archive/2007/04/04/14.aspx –  Jon Seigel Apr 30 '13 at 2:34
    
@JonSeigel : Came across such solutions.. Was just wondering if NOLOCK table level lock is getting converted to XCLUSIVE table level lock for INSERT considering the table size.. and thus the deadlock. –  kanu Apr 30 '13 at 2:55
2  
can you post the deadlock graph? –  StrayCatDBA Apr 30 '13 at 2:57
    
Looks like UPDLOCK is better. Will post the deadlock graph as soon as I get it. Will help me understand the situation. –  kanu Apr 30 '13 at 4:12
    
A SELECT statement doesn't acquire any type of write lock (update, or exclusive) unless you specifically hint for it. –  Jon Seigel Apr 30 '13 at 4:13
show 5 more comments

1 Answer

Why don't you put the BEGIN TRAN statement after the conditional statement has evaluated to true ? There's no need to keep the transaction open for the check. Try this, it might improve your locks a little :

  IF NOT EXISTS (SELECT TOP 1 * FROM Table1 WITH (NOLOCK) WHERE Col1 = Val1 AND Col2 = Val2)
  BEGIN
   BEGIN TRAN
    INSERT INTO Table1 (ID, Col1, Col2)
    VALUES(-1, Val1, Val2)
  COMMIT TRAN
  END

You might also want to consider your transaction level isolation settings.

share|improve this answer
3  
"There's no need to keep the transaction open for the check." -- yes, there is. If the lock(s) are released after the SELECT completes, this means the code is vulnerable to a race condition. –  Jon Seigel Apr 30 '13 at 4:06
    
...aside from the fact that these locking hints do not create a critical section in the code. –  Jon Seigel Apr 30 '13 at 13:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.