SQL Server READ_COMMITTED_SNAPSHOT Isolation Level - Shared Lock Issue

331 Views Asked by At

In microsoft documentation about transaction isolation level it states that;

If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

Means, with READ_COMMITED_SNAPSHOT is set to OFF, if i do a SELECT on a certain record inside a transaction, it should holds a shared lock that will block other transactions from doing an update. I tested this scenario, but it doesn't do that. Update statement succeeded without a blocking.

Why is that? Does the documentation is wrong? Or I understand incorrectly?

This is my database current isolation level set to OFF as per the document.

enter image description here

These are the steps i used to test. I used StackOverflow public data dump as my DB.

  1. Window #1. Ran the below SELECT query
BEGIN TRANSACTION

SELECT * FROM dbo.Posts  WHERE  Id=4175774
  1. Window #2. Ran the below UPDATE query
BEGIN TRANSACTION

UPDATE dbo.Posts SET Score=36
WHERE  Id=4175774

Expected Result:

UPDATE query should get locked and not succeed until I commit the Window #1 Transaction.

Actual Result:

UPDATE query got succeeded instantly.

0

There are 0 best solutions below