Sql-server – Update Query DeadLock in SQL Server when ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled

concurrencydeadlocksql serversql-server-2008update

Scenario:

We had learnt that the read operations are locked by MSSQL, causing other operations to slow down.

Later, we decided to turn on two factors.

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT for the entire database.

Upon enabling them, we face update query dead lock from multiple customers (having SQL Server 2008, 2012, 2014)

Blocking Transaction:-

enter image description here

On identifying and running the procedure to identify blocked query, we find that select query on table X has blocked the update / delete process of multiple tables.

One such blocked update statement is

BLOCKED UPDATE: UPDATE "ManagedComputer" SET "AGENT_EXECUTED_ON"= @P0 , "STATUS_UPDATED_TIME"= @P1 , "REMARKS"= @P2 , "MODIFIED_TIME"= @P3  WHERE ( ("ManagedComputer"."RESOURCE_ID" =  @P4 ) );

BLOCKING SELECT: SELECT "UserParams"."USER_ACCOUNT_ID", "UserParams"."PARAM_NAME", "UserParams"."PARAM_VALUE"  FROM "UserParams" WHERE  (("UserParams"."USER_ACCOUNT_ID" = 2) AND ("UserParams"."PARAM_NAME" COLLATE SQL_Latin1_General_CP1_CS_AS = 'stop_agent_workflow')) ;

Isn't the sole purpose of READ_COMMITTED_SNAPSHOT is not to block Update/ Insert statements ?

Query that I executed to find the blocking and blocked statement is as described in the article here

Best Answer

Writers block other writers of the same row regardless of isolation level. To avoid blocking during data modifications, examine execution plans to ensure only needed rows are touched by the query and perform query and index tuning as needed.

The READ_COMMITTED_SNAPSHOT database option avoids readers from blocking writers in the default READ COMMITTED isolation level by using row versioning instead of locking for statement level read consistency but at the cost of additional overhead for the row version store and in-row version data. You do not need to set ALLOW_SNAPSHOT_ISOLATION to use READ_COMMITTED_SNAPSHOT. ALLOW_SNAPSHOT_ISOLATION allows use of the separate SNAPSHOT isolation level, which provides transaction level (not just statement level) consistency using row versioning.

Related Question