Share via

SQL Server: Understanding Lock Escalation

  • Article

"Books Online states that the threshold for a lock escalation is reached when the statement has acquired in excess of 5,000 row or page level locks on a single instance of an object. Also some blogs (which I came across in the Internet) just take 5000 value as a threshold point and not consider other parameters. When rows exceed 5000 an attempt is made to convert a higher-level lock on the object (either an IS or an IX lock) to an S lock or an X lock on the table. If this fails, perhaps because the table is being used by someone else, then SQL Server will back off and try again if it acquires another 1,250 locks on the same instance of the object. Remember that all locks attained must originate from the same object within the statement to qualify for exceeding the lock threshold, so 4,000 locks from tableA that is self-joined (i.e., another set of 4,000 locks) would not qualify." [taken from book "Professional SQL Server 2008 Internals and Troubleshooting" by Christian Bolton et al. published by Wrox ]

I faced one question from OP where he was curious about whole table getting locked during delete operation.One user replied that if locks exceed 5000 rows it will be escalated to table level lock (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/52e6be98-6165-4b1e-a926-5d5609ab8486/delete-large-number-of-rows-without-growing-the-transaction-log). I showed him that 5000 is not just the only value which is taken into consideration apart from that following would also be used

*"Exceeding either one of the following conditions will cause lock escalation to be triggered:

  1. Forty percent of the memory used by the database engine is used by lock objects when the locks property of sp_configure is set to 0.
  2. Forty percent of the value set for the locks property in sp_configure. The locks property in Sp_configure contains the total number of locks SQL Server is permitted to issue. This therefore affects the amount of memory used by SQL Server for locking. For example, if the locks option had been set to 100 and a single object in a query had exceeded 40 locks then SQL Server would attempt to escalate the lock." [taken from book "Professional SQL Server 2008 Internals and Troubleshooting" by Christian Bolton et al. published by Wrox ]*

"During query runtime SQL Server will initially be taking row locks or page locks as the lowest level of lock granularity prior to any decision about lock escalation. How SQL Server decides which one is a closely guarded secret, but one factor is the number of rows that fit onto each page. Narrow tables with many rows are much more likely to see page locks when range scans are being used to query the data than wide fact or denormalized tables. [taken from book "Professional SQL Server 2008 Internals and Troubleshooting" by Christian Bolton et al. published by Wrox ]

To prove this I did a test on AdventureWorks2008R2 DB. For records more than 5000 no exclusive lock was taken on table. For rec count 6000, even then no exclusive lock is taken on table but for 10000 exclusive lock is taken on table. So just taking 5000 as a threshold value is not correct, as said above, other parameters also needs to be taken into account.

For 5000 records

 

From above we can see that no. of rows involved is 5000 and still Table (object) has Intent exclusive(IX) lock.

For 6000 records

Still you can see lock on object that is table is intent exclusive not exclusive.So just taking 5000 value as a mark for lock escalation is not correct approach.

For 10000 records

Now with 10000 records exclusive lock is there on table. So does 5000 value really matter here, or memory acquired by locks also came into picture with size of column and data it holds. I suppose  whenever lock escalation happens not only just rows but row size, amount of rows, table structure all these come into picture and it solely depends on Database engine to escalate row locks to table (mostly). I would suggest a testing by above method before saying that after how much rows lock will be escalated to table level.

I would be happy if somebody can show me lock escalation happening just for 5001 rows exactly.

For writing this article I took help from the book SQL Server 2008 Internals and Troubleshooting" by Christian Bolton

See Also :

TechNet Guru Contributions - SQL Server Database Engine
List of articles by Shanky

This article participated in Technet Guru competition September 2013 and won Gold Medal