Lock Escalation in SQL2005
Lock Escalation:
If you consider the hierarchy of the objects in a SQL Server instance, at the top level you have the database, followed by schema, tables, table partitions, pages and then finally the individual rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock a table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction. Fortunately, for most users don’t need not concern themselves with nuances of locking strategy as deployed by SQL Server. Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to coarser level of granularity depending on the number of locks acquired and the availability of memory at run time. Currently, SQL Server only supports escalating the locks to the table level. The locks can only be escalated from rows to the table or pages to the table level. Locks are never escalated from rows to the parent page or from pages to the owning partition.
Triggering Lock Escalation:
A lock escalation is triggered when any of the following conditions is true
· The number of locks held (different from acquired) by a statement on an index or a heap within a statement exceeds the threshold (currently set to 5000 (approx)). These locks include the intent locks as well. Note the lock escalation will not trigger if
o The transaction acquires 2,500 locks each on two index/heap(s) in a single statement.
o The transaction acquires 2,500 locks on the non-clustered index and 2,500 locks on the corresponding base table in a single statement.
o The same heap/index is referenced more than one time in a statement; the locks on each instance of those are counted separately. So for example, in the case of a self-join on a table t1, if each instance has 3000 locks within the statement, it will not trigger lock escalation
· The memory taken by lock resources > 40% of the non-AWE (32-bit) or regular (64-bit) enabled memory when the locks configuration option is set to 0, the default value. In this case, the lock memory is allocated dynamically as needed.
· The memory taken by lock resources is > 40% of the configured memory of locks (i.e. when a non-zero value for the locks configuration option). When locks configuration option is used, the locks memory is statically allocated when SQL Server starts.
When the lock escalation is triggered, the SQL Server attempts to escalate the lock to table level but the attempt may fail if there are conflicting locks. So for example, if the SH locks need to be escalated to the table level and there are concurrent X locks on one or more rows/pages of the target table, the lock escalation attempt will fail. However, SQL Server periodically, for every 1250 (approx) new locks acquired by the lock owner (e.g. transaction), attempts to escalate the lock. If the lock escalation succeeds, the SQL Server releases the lower granularity locks, and the associated lock memory, on the index or the heap. A successful lock escalation can potentially lead to blocking (because at the time of lock escalation, there cannot be any conflicting access) of future concurrent access to the index or the heap by transactions in conflicting lock mode. So the lock escalation is not always a good idea for all applications.
Disabling Lock Escalation:
SQL2005 provides supports disabling lock escalation using two trace flags as follows:
· TraceFlag-1211: It disables lock escalation at the current threshold (5000) on a per index/heap per statement basis. When this trace flag is in effect, the locks are never escalated. It also instructs SQL Sever to ignore the memory acquired by the lock manager up to a maximum statically allocated lock memory or 60% of non-AWE(32-bit)/regular(64-bit) of the dynamically allocated memory. At this time an out of lock memory error is generated. This can potentially be damaging as a misbehaving application can exhaust SQL Server memory by acquiring large number of locks. This, in the worst case, can stall the Server or degrade its performance to an unacceptable level. For these reasons, a caution must be exercised when using this trace flag
· TraceFlag-1224: This trace flag is similar to trace flag 1211 with one key difference. It enables lock escalation when lock manager acquires 40% of the statically allocated memory or (40%) non-AWE(32-bit)/regular(64-bit) dynamically allocated memory. Additionally, if this memory cannot be allocated due to other components taking up more memory, the lock escalation can be triggered earlier. SQL Server will generate an out of memory error when memory allocated to lock manager exceeds the statically allocated memory or 60% of non-AWE(32-bit)/regular memory for dynamic allocation.
If both trace flags (1211 and 1224) are set at the same time, the trace flag 1211 takes precedence. You can use dbcc tracestatus (-1) command to find the status of all trace flags enabled in SQL Server.
Please also refer to the https://support.microsoft.com/kb/323630/en-us.
Limitations of Lock Escalation:
There are some limitations in the current lock escalation mechanism in SQL Server. We will consider removing one or more of these limitations in future.
· Trace flags can only be used to disable lock escalation at an instance level. More often than not, you want to disable lock escalation at an object level. You can get around this issue by starting a dummy transaction and locking a resource (e.g. a row) to prevent lock escalation.
· The lock escalation triggering is hard coded to approx 5000 locks which may be too many locks for a small table and too few for a large table.
Locks are not escalated to individual table partitions, but to the table instead. So two users accessing distinct partitions of a table in conflicting mode may get blocked immediately after lock escalation.
Comments
Anonymous
May 31, 2006
Thanks to Euan for pointing out that Sunil from the Storage Engine team has started a blog. This continues...Anonymous
May 31, 2006
Thanks to Euan for pointing out that Sunil from the Storage Engine team has started a blog. This continues...Anonymous
June 21, 2007
The comment has been removedAnonymous
June 21, 2007
By default, we have both ROW and PAGE locks enabled...SQL Server chooses ROW lock granularity for most cases but may choose PAGE lock where appropriate. So for the case you specified, ROW lock is likely. There is no way to turn off PAGE locking at database or instance level. Are you encountering blocking due to PAGE locks? Here is the doc from BOL AllowRowLocks When TRUE, row locks are allowed when accessing the index. The Database Engine determines when row locks are used. When FALSE, row locks are not used. The default is TRUE. AllowPageLocks When TRUE, page locks are allowed when accessing the index. The Database Engine determines when page locks are used. When FALSE, page locks are not used. The default is TRUE.Anonymous
June 22, 2007
Yes, we are encountering blocking. It's not frequent, but frequent enough to cause some 'pain' to the end-user. Even though our environment is OLTP and most queries are point queries, meaning they always join on a key, some queries for a child table will touch/update a few thousand rows at a time. This is not the norm but an important part of our environment nonetheless. Under 3-6K tps for a table/database that's 2TB in size this can become an issue. I've been using trace flag 1224 hoping that it will prevent lock escalation to page locks. On the same note, we noticed in lab testing that SQL2005 Std. Ed. escalates to page locks sooner than SQL2005 and SQL2000 Ent. Ed. Documentation doesn't seem to support or verify this claim, but we can reproduce it with regularity in a test environment. For our test we had 3 identical systems (hw identical) with the only difference being the version of SQL (2k ent, 2005 ent, 2005 std). Running a begin tran then selecting the rows (3-4K) produces page locks in the sys.dm_tran_locks view on SQL2005 Std. Running the same query in SQL2k or SQL2005 Ent. produces only row locks. All systems are idle so the results from sys.dm_tran_locks or syslocks are exclusive to the test. Sort of a tangent, when we upgraded our SQL2k Ent. system to SQL2005 Std it we noticed a drop in performance despite adding newer generation hardware (migration was side-by-side, next-gen host nodes and SAN). Application timeouts were more than we could handle so we failed back. After the test above we tried SQL2005 Ent. on the same new system and our app was cruising. We don't know if the performance difference is b/c of the locking escalation difference or b/c the lock pages in memory option is not available in Std. Ed (available, but support verified it doesn't work in Std. Ed). In the end we couldn't get any calirification on the technical performance differences between Std. and Ent. Editions of SQL2005. Sorry for the long rant....was hoping since I had your ear I could lay everything out there for you. LCAnonymous
April 08, 2008
Interesting observation with table > 1TB We have a project using SQL 2005 with a table that is severalAnonymous
April 08, 2008
I have seen index is locked in SQL 2005. We have allow_row_lock and allow_page_lock off. Is there any setting to switch off index lock in SQL 2005?Anonymous
April 09, 2008
if you have disabled ROW/PAGE locks on the index, SQL Server will take lock on the index. You can't avoind the lock unless you are querying under read-uncommitted. You need to evaluate why you disabled lower granularity locks. I recommend to enable them. Also, you should look into RCSI and/or Snapshot Isolation features in SQL2005Anonymous
July 18, 2008
Hi, We're experiencing a slightly different, but related issue as part of testing an application upgrade to SQL 2005. In our case, a large select statement is seeing lock escalation to shared table lock and then blocking other queries in SQL 2005 Enterprise (x64). But the running same statement against the same database on a SQL 2000 Enterprise server does not experience the lock escalation+blocking. In both cases there's just a single user running the query and a single user attempting a second query against the same object. Is there any info on differences between 2000 and 2005 lock escalation rules? We don't want to enable T1211 unless we really have to... we'd rather understand why this is occuring now and not before.Anonymous
January 12, 2009
Hi All - Wonderful article. Our system was experiencing slowdowns since we migrated from SQL 2k to SQL 2K5 64 bit. The slowdown was due to blocking/locking issues, we tried and tested everything but nothing worked. The slowdown will always happen during weekdays (mon- fri) in morning and will go away after we restart the SQL Service. Ever since i enabled TF 1211 we have not experienced the slowdown. So how do i proof that enabling this falg might have fixed the problem? any ideas or comment.Anonymous
January 12, 2009
you can look at the profiler and there is an event for lock escalation. I assume that before using TF-1211, you were getting blocking due to Table locks. Also, please note, in SQL2008, we have a new table level option to disable lock escalation.Anonymous
January 12, 2009
Thanks for your feedback. I think I have isolated the problem to be one of the scheduled jobs running on mainDB. The problem started happening around 3:50 pm and once we recycled the SQL Agent the slowdown went away instantaneously. Digging my way to find which job is causing the problem… My question : What is different on SQL Agent 2005 than SQL 2000 which would cause slow downs or blocking issues? Can this be an engine related issue?Anonymous
January 12, 2009
Please send me the issue in details to sunila@microsoft.com. I will see if I can do. I don't know much about the agentAnonymous
January 13, 2009
Hi Sunil - i have send you the details of the case.Anonymous
February 23, 2009
Though I knew littel bit abt Lock escalation, yet this article made me to feel as if I am reading this first time.Anonymous
August 26, 2010
What is the difference between SQL server 2005 and 2008 with reference to locks? I am running an application on sql 2008 and it failes with following error. But it never failed in sql 2005. Error 1204. The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.Anonymous
August 27, 2010
I think the issue you may be running into is the memory issue. The memory footprint for SQL2008 will be different compared to SQL2005. I suggest looking into two things (1) See if you are getting memory pressure in SQL2008. In that case, you should adjust memory allocated to SQL Server (2) Look at query plans changes compared to SQL2005. There is no locking related change in SQL2008. We have added an option to disable lock escalaton at table level. thanks SunilAnonymous
December 23, 2013
In SQL Server 2000, how can I disable the lock scalation whit an update with rowlock?