CODEWITHSUNDEEP
sqlsql-servertransaction-isolation
user10025519
user10025519

Reputation: 83

Unexpected behaviour of the Serializable isolation level

Test setup

I have a SQL Server 2014 and a simple table MyTable that contains columns Code (int) and Data (nvarchar(50)), no indexes created for this table.

I have 4 records in the table in the following manner: 

1, First
2, Second
3, Third
4, Fourth

Then I run the following query in a transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

DELETE FROM dbo.MyTable 
WHERE dbo.MyTable.Code = 2

I have one affected row and I don't issue either Commit or Rollback.

Next I start yet another transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT TOP 10 Code, Data
  FROM dbo.MyTable
  WHERE Code = 3

At this step the transaction with the SELECT query hangs waiting for completion of the transaction with the DELETE query.

My question

I don't understand why the transaction with SELECT query is waiting for the transaction with the DELETE query. In my understanding the deleted row (with code 2) has nothing to do with the selected row (with code 3) and as far as I understand the specific of isolation level SERIALIZABLE SQL Server shouldn't lock entire table in this case. Maybe this happens because the minimal locking amount for SERIALIZABLE is a page? Then it could produce an inconsistent behavior for selecting rows from some other pages if the table would have more rows, say 1000000 (some rows from other pages wouldn't be locked then). Please help to figure out why the locking takes place in my case.

Upvotes: 0

Views: 1190

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

Under locking READ COMMITTED, REPEATABLE READ, or SERIALIZABLE a SELECT query must place Shared (S) locks for every row the query plan actually reads. The locks can be placed either at the row-level, page-level, or table-level. Additionally SERIALIZABLE will place locks on ranges, so that no other session could insert a matching row while the lock is held.

And because you have "no indexes created for this table", this query:

SELECT TOP 10 Code, Data
  FROM dbo.MyTable
  WHERE Code = 3

Has to be executed with a table scan, and it must read all the rows (even those with Code=2) to determine whether they qualify for the SELECT.

This is one reason why you should almost always use Row-Versioning, either by setting the database to READ COMMITTED SNAPSHOT, or by coding read-only transactions to use SNAPSHOT isolation.

Upvotes: 2

Related Questions