How to Minimize SQL Server Blocking

Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. Blocking is not the same thing as a deadlock.

A certain amount of blocking is normal and unavoidable. Too much blocking can cause connections (representing applications and users) to wait extensive periods of time, hurting overall SQL Server performance. In the worst cases, blocking can escalate as more and more connections are waiting for locks to be released, creating extreme slowdowns. The goal should be to reduce blocking as much as possible.

Locks held by SELECT statements are only held as long as it takes to read the data, not the entire length of the transaction. On the other hand, locks held by INSERT, UPDATE, and DELETE statements are held until the entire transaction is complete. This is done in order to allow easy rollback of a transaction, if necessary.

Some causes of excessive blocking, and ways to help avoid blocking, include:

• Long-running queries. Anytime a query of any type, whether it is a SELECT, INSERT, UPDATE, or DELETE, takes more than a few seconds to complete, blocking is likely. The obvious solution to this is to keep transactions as short as possible. There are many tips on this web site on how to help reduce transaction time, but some of them include optimize Transact-SQL code, optimize indexes, break long transactions into multiple, smaller transactions, avoiding cursors, etc.
• Canceling queries, but not rolling them back. If your application’s code allows a running query to be cancelled, it is important that the code also roll back the transaction. If this does not happen, locks held by the query will not be released, which means blocking can occur.
• Distributed client/server deadlock. No, this is not your typical deadlock that is handled automatically by SQL Server, but a very special situation that is not automatically resolved by SQL Server.
  
  Here is what can happen. Let's say that an application opens two connections to SQL Server. The application then asynchronously starts a transaction and sends a query through the first connection to SQL Server, waiting for results. The application then starts a second transaction and sends a query through the second connection to SQL Server, waiting for results. At some point, one of the queries from one of the connections will begin to return results, and the application will then begin to process them.
  
  As the application processes the results, at some point what could happen is that the remainder of the results become blocked by the query running from the other connection. In other words, the first query can not complete because it is being blocked by the second query. So in essence, this connection is blocked and cannot continue until the second query completes. But what happens is that the second query tries to return its results, but because the application is blocked (from the first query), its results cannot be processed. So this means that this query cannot complete, which means the block on the first query can never end, and a deadlock situation occurs. Neither connection will give up, so neither connection ever ends, and the deadlock situation never ends.
  
  SQL Server is unable to resolve this type of deadlock, so unless you want to write applications that hang forever, you can take these steps to prevent this unusual situation: 1) Add a query time-out for each of the queries, or 2) Add a lock time-out for each of the queries, or 3) Use a bound connection for the application.

In many ways, the best way to avoid blocking is to write well-tuned applications that follow the tuning advice found on this website. [7.0, 2000, 2005] Updated 4-17-2006

*****

By default, blocking locks do not time out. The waiting connection waits until the lock is released, and the block is over. If you like, you can set a lock time-out so that a connection does not wait indefinitely for the blocking lock to be released. This is accomplished using the LOCK_TIMEOUT setting.

When the LOCK_TIMEOUT setting is used to set a maximum amount of time that a connection can wait for a blocking lock to go away, the connection that has the lock and is causing the blocking problem is not affected, but the connection waiting for the block is halted, and receives an error message. When this happens, then error message 1222, "Lock request time-out period exceeded" is sent to the application.

This means that the application needs to include the appropriate error-handling code to deal with this situation and take the appropriate action, which includes rolling back the transaction. If the application does not know how to deal with this error message, and the transaction is not rolled back, it is possible that the application can continue as if the transaction was not automatically cancelled. Because of this, you should not use the LOCK-TIMEOUT setting unless your application(s) that will be affected by it know what to do when they receive this message from SQL Server.

The syntax for the SET LOCK_TIMEOUT is:

SET LOCK_TIMEOUT timeout_period

Where timeout_period is the number of milliseconds that a connection waits for a blocking lock to go away before an error is returned from SQL Server to the application. A value of -1 is the default, which means to wait indefinitely. A value of 0 tells SQL Server not to wait at all, and to return the error immediately.

This command is based on a per connection basis, and stays with the connection until the connection is broken, or a new SET LOCK_TIMEOUT command is issued. [7.0, 2000] Updated 4-17-2006

*****

Avoid INSERTing, UPDATEing, or DELETEing large numbers of records in a single transaction. If you do, all the records affected by your action will be locked until the transaction is done. If you find that you need to perform mass data changes, it is better to batch them into smaller, shorter transactions to prevent unnecessary locking.

In addition, changing the backup method from full to simple will also reduce the overhead incurred by long running transactions. Once you are done with the long running activity, you can switch back to the full backup method. [6.5, 7.0, 2000] Updated 4-17-2006