Lock Escalations in the Isolation Level SERIALIZABLE

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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:

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.

A Clustered Index Scan with a residual predicate

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.

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

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.

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.

Like or share this blog posting to get the source code.

Thanks for your time,

-Klaus

2 Comments

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top