In today’s blog posting I will talk about how you can have Lock Escalations in the isolation level SERIALIZABLE, and how they can be avoided. I have already blogged back in February 2014 about the basic concept of Lock Escalations and why they are needed in SQL Server. So please go back to this blog posting to get a basic understanding of this very important concept.

The Isolation Level SERIALIZABLE

The isolation level SERIALIZABLE is used to prevent so-called Phantom records. To prevent them, SQL Server uses a Key-Range Locking technique. Let’s have a look at the following SELECT statement:

SELECT * FROM Person.Address
WHERE StateProvinceID BETWEEN 10 AND 12
GO

This statement requests all records where the column StateProvinceID is between 10 and 12. If you run that statement in the isolation level SERIALIZABLE, the range between these IDs is locked to protect it from data modifications:

• You can’t INSERT new records into the protected range
• You can’t DELETE existing records from the protected range
• You can’t move existing records into the protected range by an UPDATE statement

Changes outside this range are permitted, because SQL Server has only locked that particular range.

Lock Escalations

The most important thing about the key range locking technique is that you need a supporting Non-Clustered Index on your search predicate. In our case this is the column StateProvinceID. If you have no supporting index defined on that, the Query Optimizer has to choose a Clustered Index Scan/Table Scan operator in the execution plan. This means that you have to scan your complete table (with a residual predicate) to find matching rows.

And when you run your SELECT statement in the isolation level SERIALIZABLE, you will trigger a Lock Escalation when you acquire more than 5000 locks during the scan. The following listing demonstrates how you trigger the Lock Escalation when there is no supporting Non-Clustered Index.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION

-- The following statement causes a Lock Escalation, because there is no
-- supporting Non-Clustered index on the column "StateProvinceID"
SELECT * FROM Person.Address
WHERE StateProvinceID BETWEEN 10 AND 12

-- There is only a S lock on the table itself!
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK
GO

Now let’s create a supporting Non-Clustered Index.

-- Create a supporting Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_StateProvinceID ON Person.Address(StateProvinceID)
GO

When you look at the execution plan now, you can see that the Query Optimizer references this newly created index in combination with a Bookmark Lookup.

When you now run the SELECT statement again in the isolation level SERIALIZABLE, you will not trigger the lock escalation anymore, because you have physically only read the 20 requested rows.

Summary

The isolation level SERIALIZABLE is the most restrictive one, and prevents phantom records. Internally SQL Server uses a key range locking technique to keep a range of requested rows stable. The most important thing to remember here is that you need a supporting Non-Clustered Index on your search predicate. Otherwise you need to scan your complete table, and you will trigger a Lock Escalation if you have read more than 5000 rows.

Thanks for your time,

-Klaus