Sql-server – Update and select concurrently running on same set of rows but different indexes are referred

clustered-indexdeadlocksql-server-2008

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?

Best Answer

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.

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.

Sql-server – What happens to non clustered index when included column is updated using update statement

The blog post you reference also indicates how you could have answered this yourself.

If you execute

SET SHOWPLAN_TEXT ON;
GO
UPDATE T SET A = 19;

The plan looks like

  |--Clustered Index Update(OBJECT:([AdventureWorks2008].[dbo].[T].[TPK]), OBJECT:([AdventureWorks2008].[dbo].[T].[TF]), SET:([AdventureWorks2008].[dbo].[T].[A] = [@1]))
       |--Compute Scalar(DEFINE:([Expr1009]=[Expr1009]))
            |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN CASE WHEN [AdventureWorks2008].[dbo].[T].[A] = [@1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END))
                 |--Top(ROWCOUNT est 0)
                      |--Index Scan(OBJECT:([AdventureWorks2008].[dbo].[T].[TF]), ORDERED FORWARD)

showing a per-row / narrow plan where the index TF is listed as one of the objects updated.

Best Answer

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

Sql-server – What happens to non clustered index when included column is updated using update statement

Related Question