Isolation levels, locking and blocking

So far you have been the only user of BuildMart. A real shop has many sessions at once: the website, the till, a stock report. The I in ACID promises that each transaction behaves as if it had the database to itself. SQL Server keeps that promise mainly with locks, and this lesson shows what they cost.

Locks a transaction holds

Before a session changes a row it takes an exclusive (X) lock on it, and it keeps the lock until its transaction ends. sys.dm_tran_locks lists the locks every session holds or is waiting for:

1BEGIN TRANSACTION;2 3UPDATE dbo.Products SET Price = 95.00 WHERE ProductId = 2;4 5SELECT resource_type, request_mode, request_status6FROM sys.dm_tran_locks7WHERE request_session_id = @@SPID8  AND resource_type <> 'DATABASE'9ORDER BY resource_type;10 11ROLLBACK;

resource_type  request_mode  request_status
-------------  ------------  --------------
KEY            X             GRANT
OBJECT         IX            GRANT
PAGE           IX            GRANT

The KEY lock is on the row itself (a row in an index is identified by its key). The intent locks (IX) on its page and on the table are signposts: they tell other sessions “someone holds an X lock somewhere inside” without making them check every row. A request to lock the whole table sees the IX at once and waits.

Blocking

When a session asks for a lock that conflicts with one another session holds, it waits. That is blocking. Session 1 changes the drill’s price and leaves its transaction open:

1BEGIN TRANSACTION;2UPDATE dbo.Products SET Price = 95.00 WHERE ProductId = 2;

Session 2 reads the same row. It needs an S lock, the row has an X lock, so the query does not return: SSMS shows “Executing query…” and the clock keeps running.

1SELECT Name, Price FROM dbo.Products WHERE ProductId = 2;

From a third window you can see who is waiting for whom. sys.dm_exec_requests shows each running request and the session blocking it:

1SELECT r.session_id, r.blocking_session_id, r.wait_type, r.command,2       t.text AS waiting_sql3FROM sys.dm_exec_requests AS r4CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t5WHERE r.blocking_session_id <> 0;6 7SELECT resource_type, request_mode, request_status, request_session_id8FROM sys.dm_tran_locks9WHERE resource_database_id = DB_ID() AND resource_type <> 'DATABASE'10ORDER BY request_session_id, resource_type;

session_id  blocking_session_id  wait_type  command  waiting_sql
----------  -------------------  ---------  -------  ----------------------------------------------------------------------------
        54                   51  LCK_M_S    SELECT   (@1 tinyint)SELECT [Name],[Price] FROM [dbo].[Products] WHERE [ProductId]=@1

resource_type  request_mode  request_status  request_session_id
-------------  ------------  --------------  ------------------
KEY            X             GRANT                           51
OBJECT         IX            GRANT                           51
PAGE           IX            GRANT                           51
KEY            S             WAIT                            54
OBJECT         IS            GRANT                           54
PAGE           IS            GRANT                           54

Session 54 is waiting (LCK_M_S: waiting for a shared lock) on session 51. The lock list shows the conflict exactly: 51 has been granted X on the key and 54 is waiting for S on it. The waiting query text also shows that SQL Server turned the literal 2 into a parameter (@1) so the plan can be reused.

As soon as Session 1 commits, its locks go and Session 2’s SELECT returns, with the new price:

1COMMIT;

1SELECT Name, Price FROM dbo.Products WHERE ProductId = 2;

Name            Price
--------------  -----
Cordless drill  95.00

Three things that can go wrong

Without isolation, a transaction reading data that another transaction is changing can get three kinds of wrong answer:

The isolation level of a session decides which of these it is protected from, and the price is more blocking. Set it with SET TRANSACTION ISOLATION LEVEL; it lasts until the session changes it.

BEGIN TRANSACTION;
UPDATE dbo.Products SET Price = 9.99
WHERE ProductId = 2;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT Price FROM dbo.Products
WHERE ProductId = 2;

ROLLBACK;

(the waiting SELECT finishes)

READ UNCOMMITTED and NOLOCK: dirty reads

At READ UNCOMMITTED a reader takes no S locks, so it is never blocked, and it reads whatever is in the row, committed or not. Session 1 starts a price change it will cancel:

1BEGIN TRANSACTION;2UPDATE dbo.Products SET Price = 9.99 WHERE ProductId = 2;

1SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;2SELECT Name, Price FROM dbo.Products WHERE ProductId = 2;

Name            Price
--------------  -----
Cordless drill   9.99

1ROLLBACK;

Session 2 has told someone the drill costs 9.99. That price never existed: it was rolled back. The table hint WITH (NOLOCK) does the same for one table in one query, and gave the same 9.99 here.

READ COMMITTED: the default

At READ COMMITTED a reader takes an S lock on each row as it reads it and releases it straight away. So it never sees uncommitted data (it waits, as you saw above), but nothing stops the row changing between two reads in the same transaction:

1BEGIN TRANSACTION;2SELECT Name, Stock FROM dbo.Products WHERE ProductId = 2;

Name            Stock
--------------  -----
Cordless drill     12

1UPDATE dbo.Products SET Stock = Stock - 10 WHERE ProductId = 2;

1SELECT Name, Stock FROM dbo.Products WHERE ProductId = 2;2COMMIT;

Name            Stock
--------------  -----
Cordless drill      2

A non-repeatable read. If Session 1 had checked “12 in stock, enough for 5” and then sold 5, it would be selling stock that is no longer there.

REPEATABLE READ: keep the read locks

REPEATABLE READ keeps every S lock until the transaction ends. Run the same steps with Session 1 at REPEATABLE READ: Session 2’s UPDATE now waits for Session 1, Session 1 reads 12 both times, and the UPDATE goes through after Session 1 commits.

It locks only the rows it has read, though. A new row that matches the query can still be inserted:

1SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;2BEGIN TRANSACTION;3SELECT COUNT(*) AS Tools FROM dbo.Products WHERE CategoryId = 1;

Tools
-----
    3

1INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)2VALUES (N'Spirit level', 1, 14.00, 20, N'each', '2024-07-01');

1SELECT COUNT(*) AS Tools FROM dbo.Products WHERE CategoryId = 1;2COMMIT;

Tools
-----
    4

The spirit level is a phantom: a row that appeared inside Session 1’s transaction.

SERIALIZABLE: lock the ranges too

SERIALIZABLE locks the range a query covered, not just the rows it found, so no one can insert into it. With the same steps at SERIALIZABLE, Session 2’s INSERT waits and Session 1 counts 3 tools both times. You can see range locks from a single session. This query reads products 2 to 4 by the primary key:

1SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;2BEGIN TRANSACTION;3 4SELECT ProductId, Name FROM dbo.Products WHERE ProductId BETWEEN 2 AND 4;5 6SELECT resource_type, request_mode, COUNT(*) AS Locks7FROM sys.dm_tran_locks8WHERE request_session_id = @@SPID AND resource_type <> 'DATABASE'9GROUP BY resource_type, request_mode10ORDER BY resource_type;11 12ROLLBACK;13SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

ProductId  Name
---------  ----------------
        2  Cordless drill
        3  Tape measure 8 m
        4  Cement 25 kg

resource_type  request_mode  Locks
-------------  ------------  -----
KEY            RangeS-S          4
OBJECT         IS                1
PAGE           IS                1

RangeS-S is a shared lock on a key and the gap before it. There are 4: on keys 2, 3 and 4, and on key 5, which closes the gap after 4 so nothing can be inserted between 4 and 5. Without an index to lock ranges on, SERIALIZABLE has to lock much more, often the whole table.

Lock escalation

Every lock costs memory. When one statement holds more than 5,000 locks on one table, SQL Server swaps them for a single table lock: lock escalation. It saves memory, but a table X lock blocks every other session’s access to the whole table:

1DROP TABLE IF EXISTS dbo.Sales;2CREATE TABLE dbo.Sales (SaleId int NOT NULL PRIMARY KEY, Amount decimal(10,2) NOT NULL);3INSERT INTO dbo.Sales (SaleId, Amount)4SELECT value, 10.00 FROM GENERATE_SERIES(1, 10000);5GO6BEGIN TRANSACTION;7UPDATE dbo.Sales SET Amount += 1 WHERE SaleId <= 4000;8 9SELECT resource_type, request_mode, COUNT(*) AS Locks10FROM sys.dm_tran_locks11WHERE request_session_id = @@SPID AND resource_type <> 'DATABASE'12GROUP BY resource_type, request_mode13ORDER BY resource_type;14ROLLBACK;15 16BEGIN TRANSACTION;17UPDATE dbo.Sales SET Amount += 1 WHERE SaleId <= 7000;18 19SELECT resource_type, request_mode, COUNT(*) AS Locks20FROM sys.dm_tran_locks21WHERE request_session_id = @@SPID AND resource_type <> 'DATABASE'22GROUP BY resource_type, request_mode23ORDER BY resource_type;24ROLLBACK;

resource_type  request_mode  Locks
-------------  ------------  -----
KEY            X              4000
OBJECT         IX                1
PAGE           IX               11

resource_type  request_mode  Locks
-------------  ------------  -----
OBJECT         X                 1

4,000 rows: 4,000 key locks, plus intent locks on 11 pages and the table. 7,000 rows: one X lock on the whole table and nothing else. The documented trigger is 5,000 locks, but SQL Server only checks now and then as locks are added: on this table it still held 6,200 row locks, and had escalated by 6,300. If big updates block everyone, do them in batches of a few thousand rows, each in its own transaction.

Lost updates

A common bug in application code: read a value, work out the new value, write it back. Two sessions run this at the same time to sell one drill each (the WAITFOR stands for the time the application takes between its two statements):

1BEGIN TRANSACTION;2DECLARE @Stock int = (SELECT Stock FROM dbo.Products WHERE ProductId = 2);3WAITFOR DELAY '00:00:02';4UPDATE dbo.Products SET Stock = @Stock - 1 WHERE ProductId = 2;5COMMIT;

1SELECT Name, Stock FROM dbo.Products WHERE ProductId = 2;

Name            Stock
--------------  -----
Cordless drill     11

Two drills sold, one taken out of stock. Both sessions read 12 and both wrote 11: the second write overwrote the first. That is a lost update, and READ COMMITTED does not prevent it, because the S lock is released as soon as the row has been read.

The best fix is to avoid the separate read. One UPDATE that does the arithmetic itself is atomic, and it can check the stock in the same statement:

1UPDATE dbo.Products2SET Stock = Stock - 13OUTPUT inserted.Name, deleted.Stock AS Before, inserted.Stock AS After4WHERE ProductId = 2 AND Stock >= 1;

Name            Before  After
--------------  ------  -----
Cordless drill      12     11

When you really must read first, read WITH (UPDLOCK). It takes an update lock and keeps it to the end of the transaction. Plain readers are not blocked, but a second UPDLOCK reader waits. With UPDLOCK in both sessions above, Session 2 waited, read 11, and the drill ended at 10, which is right.

1UPDATE dbo.Products2SET Stock = Stock - 53OUTPUT inserted.Name, deleted.Stock AS Before, inserted.Stock AS After4WHERE ProductId = 8 AND Stock >= 5;5 6UPDATE dbo.Products7SET Stock = Stock - 58OUTPUT inserted.Name, deleted.Stock AS Before, inserted.Stock AS After9WHERE ProductId = 9 AND Stock >= 5;10 11IF @@ROWCOUNT = 0 PRINT 'Out of stock';

Name               Before  After
-----------------  ------  -----
Wood screws (200)     150    145

Name  Before  After
----  ------  -----

Out of stock