0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user10025519
  • 31
  • 6

1 Answers1

2

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 1
    I have added a clustered unique index on the column Code for the table and the behavior has changed; now the transaction performing SELECT doesn't wait for the completion of the transaction with the DELETE query. Thank you for your fast and qualified answer! – user10025519 Apr 04 '20 at 20:19
  • If this answer has resolved your issue, then please accept the answer as well as voting it up. – MJH Apr 04 '20 at 23:34
  • @David Browne: With a clustered index on column Code I begin a SERIALIZABLE transaction, without commit or rollback, with query SELECT * FROM MyTable WHERE Code = 6. Then I start yet another SERIALIZABLE transaction with query INSERT INTO dbo.MyTable (Code, Data) VALUES (5, 'Fifth'). The second transaction hangs waiting for commit or rollback of the first transaction. Could you please additionally clarify why this time I have hanging transaction #2 (with the insert query)? These transactions work with completely different values with cluster indexing on the column Code. – user10025519 Apr 05 '20 at 18:21
  • Let me guess, there's no row with Code=6. In that case, the Key range lock for the SERIALIZABLE SELECT will lock the adjacent key values. SERIALIZABLE is full of surprises. Most of them bad. – David Browne - Microsoft Apr 05 '20 at 20:16
  • @David Browne: Thank you for your reply. Regarding the case with SELECT and INSERT described in my comment above: I've tried SELECT with Code = 3 (which exists and selected successfully) and with INSERT unchanged (inserts a row with values 5 and Fifth) and the result is the same - the transaction with INSERT hangs waiting. Both transactions have the SERIALIZABLE isolation level. – user10025519 Apr 06 '20 at 07:54
  • That doesn't happen on my little repro. Check `sys.dm_tran_locks`, and make sure you've rolled back all your transactions at the beginning of your test. – David Browne - Microsoft Apr 06 '20 at 13:44
  • 1
    @David Browne: Thank you again! There was another transaction; from a clean start the behavior is not reproducible any more, and the SERIALIZABLE isolation level works as expected. – user10025519 Apr 06 '20 at 15:34