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.

In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of having deadlock in such situation? clustered index is used by update statement for example Cluster Index Update <- Clustered index seek whereas non clustered index used by select statement for example select <- nonclustered index seek What happens when one thread updates and other is using select assuming same rowids referred(where clause returns same rows)? Are there any chances of having deadlock? how such situations can be handled if application allows update and select to run concurrently?

What are the chances of occurring index row level locking deadlock?

share|improve this question
1  
If your selects run under snapshot isolation, they will never deadlock with modifications - it is that simple. –  AlexKuznetsov Mar 22 '12 at 14:02

1 Answer 1

What you are looking at is more of a blocking scenario. Transactions usually happen quickly and you would not notice a difference when an update and select are issued at the same time.

As per your scenario, the update will happen first. Then depending on your concurrency level, the select will be blocked till the end of the update transaction. In a good case scenario, this happens at the micro second level, and the end user does not notice it.

One way a deadlock can occur: the update transaction, while blocking the select transaction, should request for a resource that is held by the select transaction.

share|improve this answer
    
I disagree with this: "For a deadlock to occur: the update transaction, while blocking the select transaction, should request for a resource that is held by the select transaction." You are not considering conversion deadlocks. –  AlexKuznetsov Mar 22 '12 at 13:59
    
@AlexKuznetsov good tip on the conversion lock. I have edit my text to reflect what I was implying. –  StanleyJohns Mar 22 '12 at 14:17

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.