**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](https://i.stack.imgur.com/qhvFk.png)](https://i.stack.imgur.com/qhvFk.png)

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](https://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/)