Lock escalation in serializable isolation levels

In today's article I'll discuss the lock Escalations in the serializable (SERIALIZABLE) isolation level, and how you can avoid it. On July 14 last month, I introduced the basic concepts of lock Escalations in SQL Server and why they were needed. So please go back to this article to understand this very important concept.

Serializable (SERIALIZABLE) Isolation level

the Serializable (SERIALIZABLE) isolation level is used to block so-called Phantom Records (Phantom Records). To prevent them, SQL Server uses key-range locking (Key-range Locking) technology. Let's look at the following SELECT statement:

1 SELECT *  from person.address 2 WHERE between Ten  and  A 3 GO

This statement requests all records that are stateprovinceid between 10 and 12. If you run this statement at the Serializable (SERIALIZABLE) isolation level, IDs in these scopes will be locked and protected from data modification:

• You can't insert a new record into the protection range.
• You can't delete an existing record from the scope of protection
• You cannot move existing data to the protection range with the UPDATE statement

modifications outside this range are allowed because SQL Server locks only that particular range.

Lock Upgrade (Escalations)

Key-range Locking (Key-range Locking) technology The most important is the nonclustered indexes that need to be supported on your query predicate. In our example is the StateProvinceID column. If there is no supported index definition on it, the query optimizer selects the clustered Index Scan/table scan (Clustered index scan/table scan) operator in the execution plan. This means that you have to scan your entire table (with a residual predicate (residual predicate)) to find a matching record.

When you run your SELECT statement in the Serializable (SERIALIZABLE) isolation level, you will trigger a lock upgrade (lock Escalations) when you get more than 5,000 locks during your scan. The following code demonstrates how you can trigger a lock upgrade (lock Escalations) when there are no supported nonclustered indexes.

1 SET TRANSACTION Isolation  Level SERIALIZABLE2 GO3 4 BEGIN TRANSACTION5 6 --The following statement causes a Lock escalation, because there is no7 --supporting non-clustered Index on the column "StateProvinceID"8 SELECT *  fromperson.address9 WHEREStateProvinceIDbetween Ten  and  ATen  One --there is only a S lock on the table itself! A SELECT *  fromsys.dm_tran_locks - WHERErequest_session_id= @ @SPID -  the ROLLBACK - GO

Now let's create a nonclustered index that is supported.

1 -- Create a supporting non-clustered Index 2 CREATE nonclustered INDEX  on person.address (StateProvinceID) 3 GO

Now when you look at the execution plan, you'll see that the query optimizer references the newly created index and combines it with the bookmark lookup (bookmark lookup).

When you run the SELECT statement again in the Serializable (SERIALIZABLE) isolation level, you will not trigger the lock escalation because you are physically reading only 20 request lines.

Summary

The Serializable (SERIALIZABLE) isolation level is one of the most restrictive, and it blocks Phantom Records (Phantom Records). SQL Server uses key-range locking (Key-range Locking) technology internally to keep the request-range rows stable. Here you have to remember that the most important thing is that you have a supported nonclustered index on your search predicate. Otherwise you need to scan your entire table, and if you read more than 5000 lines, you will trigger the lock upgrade (lock Escalations).

Thanks for your attention!

Lock escalation in serializable isolation levels