0

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.

Dhanuka Jayasinghe
  • 147
  • 2
  • 7
  • This is not how `read committed` works (and you should think about it this way instead of "read committed snapshot is off"). Locks are held at most for the _statement_, as the rest of that paragraph described. Try again with `SELECT * FROM dbo.Posts WITH (SERIALIZABLE) WHERE Id=4175774;`. – Aaron Bertrand Feb 16 '23 at 03:37
  • Thanks for the insight @AaronBertrand. Putting a table hint (SERIALIZABLE) or doing setting the isolation level before the transaction using `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` indeed hold a shared lock on that row. But my question is why the document says `DB engine will use shared lock while running a read operations?` While it's not. Even my DB default isolation level is READ COMMITED? – Dhanuka Jayasinghe Feb 17 '23 at 01:20
  • The read operation is your SELECT (not the entire transaction). If you want the shared lock to be hold longer than the select statement, you need to say so. By default it’s only set up to protect the data you’re reading from being modified _while you’re reading it_. To protect it longer you use serializable (aka holdlock), designed to hold locks for the remainder of the transaction. I think the big miscue is you’re interpreting “operation” as a batch, not a statement. – Aaron Bertrand Feb 17 '23 at 01:25
  • @AaronBertrand So when i do just a SELECT there will be a shared lock on that row for the time the SELECT statement is get executed? If i want to hold it longer (such as end of a batch), i want to explicitly say it? – Dhanuka Jayasinghe Feb 17 '23 at 01:31

0 Answers0