Update and select concurrently running on same set of rows but different indexes are referred?

Question

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?

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.