MSSQL - Prevent SQL Locking

http://support.microsoft.com/kb/323630

How to Determine Whether Lock Escalation Is Causing Blocking

Lock escalation does not cause most blocking problems. To determine whether lock escalation is occurring around the time when you experience blocking issues, start a SQL Profiler trace that includes the Lock:Escalation event. If you do not see any Lock:Escalation events, lock escalation is not occurring on your server and the information in this article does not apply to your situation.

If lock escalation is occurring, verify that the escalated table lock is blocking other users.

For more information about how to identify the head blocker and how to identify the lock resource held by the head blocker that is blocking other server process IDs (SPIDs), click the following article number to view the article in the Microsoft Knowledge Base:

224453  (http://support.microsoft.com/kb/224453/ ) Understanding and resolving SQL Server 7.0 or 2000 blocking problems

If the lock that is blocking other users is anything other than a TAB (table-level) lock with a lock mode of S (shared), or X (exclusive), lock escalation is not the issue. In particular, if the TAB lock is an intent lock (such as a lock mode of IS, IU, or IX), this is not the result of lock escalation. If your blocking problems are not being caused by lock escalation, see the article Q224453 for troubleshooting steps.

Back to the top

How to Prevent Lock Escalation

The simplest and safest way to prevent lock escalation is to keep transactions short and to reduce the lock footprint of expensive queries so that the lock escalation thresholds are not exceeded. There are several ways to obtain this goal, many of which are listed:

• Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:

DELETE FROM LogMessages WHERE LogDate < '2/1/2002'                                            

By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:

SET ROWCOUNT 500

delete_more:

     DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

IF @@ROWCOUNT > 0 GOTO delete_more

SET ROWCOUNT 0

• Reduce the query's lock footprint by making the query as efficient as possible. Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance. After you find the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks. Consider pasting the query into a Query Analyzer query window to perform an automatic index analysis on it. To do so, on the Query menu, click Index Tuning Wizard in SQL Server 2000, or click Perform Index Analysis in SQL Server 7.0.
  
  One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of Bookmark Lookups (maximize the selectivity of the index for the particular query). If SQL Server estimates that a Bookmark Lookup logical operator may return many rows, it may use a PREFETCH to perform the bookmark lookup. If SQL Server does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to repeatable read for a portion of the query. This means that what may look similar to a SELECT statement at a read-committed isolation level may acquire many thousands of key locks (on both the clustered index and one nonclustered index), which can cause such a query to exceed the lock escalation thresholds. This is especially important if you find that the escalated lock is a shared table lock, which, however, is not commonly seen at the default read-committed isolation level. If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding additional columns to the nonclustered index that appears in the Index Seek or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. It may be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if including everything in the select column list is impractical.
  
  A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.
  
  For more information, click the following article number to view the article in the Microsoft Knowledge Base:

260652  (http://support.microsoft.com/kb/260652/ ) Nested loop join that uses a "BOOKMARK LOOKUP ...WITH PREFETCH" may hold locks longer

• Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies a large number of rows in the mytable table and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:

·         BEGIN TRAN

·         SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0

·         WAITFOR DELAY '1:00:00'

COMMIT TRAN                                  

This query acquires and holds an IX lock on mytable for one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using an sp_indexoption stored procedure).

Additionally, you can disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server. Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

Note When a "1204" error occurs, it stops the processing of the current statement and causes a rollback of the active transaction. The rollback itself may block users or lead to a long database recovery time if you restart the SQL Server service.

Using a lock hint such as ROWLOCK only alters the initial lock plan. Lock hints do not prevent lock escalation.

The other methods of preventing lock escalation that are discussed earlier in this article are better options than enabling the trace flag. Additionally, the other methods generally result in better performance for the query than disabling lock escalation for the whole instance. Microsoft recommends enabling this trace flag only to mitigate severe blocking that is caused by lock escalation while other options, such as those discussed earlier in this article, are being investigated. To enable a trace flag so that it is turned on whenever SQL Server is started, add it as a server startup parameter.

To add a server startup parameter, right-click the server in SQL Enterprise Manager, click Properties, and then on the General tab, click Startup Parameters, and then add the following parameter (exactly as shown):

-T1211

You must cycle the SQL Server service for a new startup parameter to take effect. If you run the following query in Query Analyzer the trace flag takes effect immediately:

DBCC TRACEON (1211, -1)                             

However, if you do not add the -T1211 startup parameter, the effect of a traceon command is lost when the SQL Server service is cycled. Turning on the trace flag prevents any future lock escalations, but it does not reverse any lock escalations that have already occurred in an active transaction.