Microsoft engineers explain SQL Server Blocking

Microsoft engineers explain SQL Server Blocking

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, and block the first connection. A connection can block another connection, whether from the same application or from another client.

Note: Some operations that require lock protection may not be obvious, such as the system directory table and index locks.
Most blocking problems occur because a process controls the lock for a long time, causing the blocked process chain to wait for the lock on other processes.

Common blocking situations

1. Submit a query that has been executed for a long time.
Long-running queries block other queries. For example, the deletion or update of many rows is affected.
The operation can obtain many locks. Whether or not these locks are upgraded to the table lock, other queries are blocked. Therefore, it is generally not necessary to support queries and online transaction processing (OLTP) for long-running decisions)

Query is mixed together. The solution is to optimize the query, such as changing the index, dividing large and complex queries into simple queries, or running queries on idle time or on a separate computer.

2. The cursor is not properly used for queries. A cursor may be a convenient way to browse in a result set, but using a cursor may be slower than using a set-oriented query.

3. Cancel the query that is not submitted or rolled back.
This happens if the application cancels the query (for example, using the open database connection (ODBC) sqlcancel function) but does not issue the required number of rollback and commit statements at the same time. Canceling a query does not automatically roll back or commit transactions. After the query is canceled, all locks obtained within the transaction are retained. The application must commit or roll back canceled transactions to correctly manage the transaction nesting level.

4. The application has not processed all the results.
After a query is sent to the server, all applications must extract all result rows immediately. If the application does not extract all result rows, the lock may stay on the table and block other users. If the used application transparently submits the 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 perform this operation), the blocking problem may not be solved. To avoid this problem, you can restrict these applications to the report or decision support database.

5. Distributed Client/Server deadlock.
Unlike conventional deadlocks, distributed deadlocks cannot be implemented by Microsoft SQL server? 2000 is automatically detected. If the application opens multiple connections with SQL Server and submits queries asynchronously, a distributed client/server deadlock may occur.

For example, a client application thread has two open connections. This thread starts the transaction asynchronously and sends a query on the first connection. The application then starts other transactions, issue queries on another connection, and wait for the results. When SQL Server Returns a connection result, the application starts to process the result. The application processes the results in this way until the query of the generated results is blocked by the query executed on another connection, resulting in no available results. At this time, the first connection is blocked and will wait for more results to be processed indefinitely. The second connection is not blocked by the lock, but still tries to return the result to the application. However, the result of the second connection will not be processed because the application is blocked and the result is waiting for the first connection.

Avoid Blocking

1. query timeout for each query.

2. lock timeout for each query. For more information, see custom lock timeout.

3. Bind the connection. For more information, see bind a connection.

4. SQL Server is essentially manipulated by client applications. The client application has almost full control over the locks obtained on the server (and is responsible for the locks ). Although SQL Server

The lock manager automatically uses the lock to protect transactions, but this is directly encouraged by the query type issued by the client application and the way in which the results are processed. Therefore, most blocking solutions involve checking client applications.

5. Blocking problems often require you to check the SQL statements submitted by the application and the application behavior related to connection management, processing of all result rows, and so on. If the development tool does not allow explicit control of connection management, query timeout, and result processing, the blocking problem may not be solved.

Guidelines for designing applications to avoid blocking

1. Do not use or design applications that allow users to enter the editing box. The editing box will generate a query that runs during the growth time. For example, do not use or design an application that prompts the user to enter, and allow some fields to be left blank or enter wildcards. This may cause the application to submit a query that runs for a long time and cause blocking.

2. Do not use or design applications that allow users to enter content in transactions.

3. Cancel the query.

4. Use query or lock timeout to prevent out-of-control queries and avoid distributed deadlocks.

5. Extract all result rows immediately.

6. Make the transaction as short as possible.

7. explicitly control connection management.

8. Perform stress testing on applications under The estimated full load of concurrent users.