Eight guidelines for avoiding blocking when designing an application

Blocking occurs when the first connection from the application controls the lock and the second connection requires a conflicting lock type. The result is to force the second connection to wait while blocking on the first connection. A connection can block another connection, whether it is from the same application or a separate application on another client computer.

Describes some operations that require lock protection, such as locks on system catalog tables and indexes.

Most blocking problems occur because a process controls the lock for too long, causing the blocked process chain to wait for the lock on another process.

Common blocking scenarios include the following:

Submit a query that has a long execution time.

long-running queries block other queries. For example, delete or update operations that affect many rows can acquire many locks that block other queries whether or not they are upgraded to table locks. Therefore, it is not common to mix long running decision support queries with online transaction processing (OLTP) queries. The solution is to try to optimize the query, such as changing the index, splitting large complex queries into simple queries, or running queries on idle time or on a separate computer.

One reason queries run long and cause congestion is that these queries do not use cursors inappropriately. Cursors may be a convenient way to browse through the result set, but using cursors may be slower than using a query for a collection.

Cancels a query that has not been committed or rolled back.

This can happen if the application cancels the query (such as using the Open Database Connectivity (ODBC) SqlCancel function) but does not emit the required number of rollback and commit statements at the same time. Canceling a query does not automatically rollback or commit a transaction. When you cancel a query, all locks acquired within the transaction are preserved. The application must commit or roll back canceled transactions to properly manage transaction nesting levels.

The application has not finished processing all the results.

After the query is sent to the server, all the application must finish extracting all the result rows immediately. If the application does not extract all the result rows, the lock may stay on the table and block other users. If you use an application that transparently submits a Transact-SQL statement to the server, the application must extract all the result rows. If the application does not do this (if it cannot be configured to do so), the blocking problem may not be resolved. To avoid this problem, you can limit these applications to the report or decision support database.