SQL Server blocking issues detailed

Blocking definition

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.

Note: Some operations that require lock protection may not be obvious, 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

1. Submit a query for 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.

2. 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.

3. Cancel a query that has not been submitted 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.

4. 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.

5. Distributed client/server deadlock.

Unlike a regular deadlock, a distributed deadlock cannot be automatically detected by Microsoft SQL Server 2000. If the application opens multiple connections to SQL Server and submits the query asynchronously, a distributed client/server deadlock may occur.

For example, a client application thread has two open connections. The thread initiates the transaction asynchronously and issues a query on the first connection. The application then initiates another transaction, issues a query on another connection, and waits for the result. When SQL Server returns the results of one of the connections, the application begins to process the results. The application processes the result until the query that generated the result is blocked by a query executed on another connection, causing no further results to be available. At this point the first connection is blocked, waiting indefinitely for more results to be processed. The second connection is not blocked on the lock but still attempts to return the result to the application. However, as the application blocks and waits for the result on the first connection, the result of the second connection is not processed.

Ways to avoid blocking

1. Use a query timeout for each query.

2. Use a lock timeout for each query. For more information, see Customizing the Lock timeout.

3. Use a bound connection. For more information, see Using bound connections.

4. SQL Server is essentially a puppet that is manipulated by client applications. The client application has almost complete control over the locks acquired on the server (and is responsible for the locks). Although the SQL Server lock Manager automatically uses locks to protect transactions, this is directly instigated by the type of query sent by the client application and by how the results are handled. As a result, most blocking problem solutions involve checking client applications.

5. Blocking issues often require checking the SQL statements submitted by the application itself, as well as examining the application behavior itself related to connection management, processing of all result rows, and so on. If the development tool does not allow explicit control over connection management, query timeouts, result processing, and so on, the blocking problem may not be resolved.

Guidelines for designing applications to avoid blocking

1. Do not use or design an application that allows the user to fill in the edit box, and the edit box generates a long-running query. For example, do not use or design applications that prompt user input, allow some fields to remain blank, or allow you to enter wildcard characters. This may cause the application to submit queries that run too long, causing a blocking problem.

2. Do not use or design applications that enable users to enter content within a transaction.

3. Allow cancellation of query.

4. Use query or lock timeout to prevent runaway queries and avoid distributed deadlocks.

5. Immediately complete the extraction of all results rows.

6. Make matters as short as possible.

7. Explicit control of connection management.

8. Stress testing of the application under the full load of the expected concurrent users.