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.