Lock Escalation

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server.

Lock hierarchy start in in Order as Database->Table-> Page –Row.

The lock hierarchy starts at the database level, and goes down to the row level. You always have a Shared Lock (S) on the database level itself. When your query is connected to a database (e.g. USE MyDB), the Shared Lock prevents the dropping of the database, or that backups are restored over that database. And underneath the database level, you have locks on the table, on the pages, and the records when you are performing an operation.

In SELECT statement, you have an Intent Shared Lock (IS) on the table and page level, and a Shared Lock (S) on the record itself. In modification statement (INSERT, UPDATE, DELETE), you have an Intent Exclusive or Update Lock (IX or IU) on the table and page level, and a Exclusive or Update Lock (X or U) on the changed records. SQL Server always acquires locks from top to bottom to prevent so-called Race Conditions, when multiple threads trying to acquire locks concurrently within the locking hierarchy. Imagine now how the lock hierarchy would look like, when you perform a DELETE operation on a table against 20.000 rows. Let’s assume that a row is 400 bytes long, means that 20 records fit onto one page of 8kb

We have now 1 IS lock on database, 1 IS on table, 1000 X on page and 20k on rows. Every lock needs 96 bytes of memory, so we look at 1.9 MB of locks just for 1 simple query. This will not scale indefinitely when you run multiple queries in parallel. For that reason SQL Server implements now the Lock Escalation.

As soon as you have more than 5k locks on one level in your locking hierarchy, SQL Server escalates these many fine-granularity locks into a simple coarse-granularity lock. By default SQL Server always escalates to the table level skipping key page level locks.

Now the above delete operation will have 1 lock on table and 1 on database. But this may impact on concurrency of data, as X lock on table would deny any other read/write operation on that table.

Also In repeatable read isolation level, select statement keeps the lock on till transaction completes so as soon as select statement read more than 5000 rows it will place shared lock on table stopping any further modification.

We can manage lock escalation using sql server to make is it disable or working:

Using trace flag we can disable lock escalation completely on table:

·         1211 – Disables Lock Escalation completely – allows to use 60% of the allocated memory – if 60% of memory is used and more locking is needed you will get an out-of-memory error.

·         1224 – Disables Lock Escalation until the memory threshold of 40% allocated memory is reached – after that Lock Escalation is enabled.

But that was in most cases not a good choice and caused a lot of performance problems. In SQL-Server 2008 and above there is a new table option (ALTER-TABLE) that can be used to change the default Lock-Escalation

Alter table

Set Lock_Escalation =

Auto means if tables is partitioned – the locks will be escalated to the partition-level if table is not partitioned – the locks will be escalated to the table-level, In Table – It will always be escalated to table, Disable-> lock escalation will be deactivated for table.

Lock Escalation needs to be handled carefully, modification on more than 5.000 rows from a table will run into Lock Escalations? You can disable Lock Escalation temporarily, but you have to be very careful here. Another option is to make your DML statements in a loop as different, separate transactions: DML less than 5.000 rows, so that you can prevent Lock Escalations. But it also has its drawback as it may cause frequent log growth issue.