1

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

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • Thanks, for your input. reiterated my findings in the problem statement. Share your points if any.
    – surendhar_s
    Commented Apr 10, 2017 at 11:55
  • @surendhar_s, what is the isolation level of the select query? Assuming the READ_COMMITTED_SNAPSHOT database option is on, it will block writers only of running REPEATABLE READ or SERIALIZABLE.
    – Dan Guzman
    Commented Apr 10, 2017 at 12:37
2

@Surendhar_s, writers still block writers in Read Committed Snapshot Isolation.

You can start investigating snapshot isolation levels by reading this great article by Brent Ozar:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

Improve this answer
1
  • Thanks, for the data provided. reiterated my findings in the problem statement. Share your points if any.
    – surendhar_s
    Commented Apr 10, 2017 at 11:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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