Blocking definition =============== When the first connection control lock from the application is a second connection requires a collision-lock type, it will occurs. The result is forced the second connection waiting, and blocks the first connection. Whether it is from the same app or another client separate application, one connection can block another connection.
Note Some operations that need to be locked protection may not be obvious, such as locks on the system catalog table and index. Most blocking problems occurs because a process controlled lock time is too long, causing blocking process chains waiting to be locked on other processes.
Common blocking situations include ================1. Submit a query for execution time. Long-running queries will block other queries. For example, DELETE or UPDATE operations affecting a lot of rows can get a lot of locks, whether or not to upgrade to the table lock, other queries. Therefore, do not operate decision support query and online transaction processing (OLTP) for a long time
The query mixed together. The solution is to find a way to optimize the query, such as changing the index, dividing large complex queries into a simple query or running a query on an idle time or a separate computer. The query is inappropriately using the cursor. The cursor may be a convenient way to browse in the results, but the use of the cursor may be slower than the use of a collection of queries. Cancel the query without submission or rollback. This happens if the application cancels query (such as using an open database connection (ODBC) SQLCANCEL function) but does not simultaneously issue the required number of ROLLBACK and COMMIT statements. Cancel the query does not automatically return or submit a transaction. After canceling the query, all the locks acquired in the transaction will be retained. The application must submit or roll back the canceled transaction to properly manage the transaction nested level.
4. The application did not process all the results. After sending the query to the server, all applications must immediately complete the extract all results. If the application does not extract all result lines, the lock may leave other users on the table. If used by the application
The Transact-SQL statement is transparently submitted to the server, and the application must extract all results rows. If the application does not do this (if this is not configured to do this), the blocking problem may not be resolved. To avoid this problem, these applications can be restricted on the report or decision support database.
5. Distributed client / server deadlock. Unlike conventional deadlocks, distributed deadlocks cannot be automatically detected by Microsoft SQL Server® 2000. If the application opens multiple connections to SQL Server and submitting queries asynchronous, a distributed client / server deadlock may occur. For example, a client application thread has two open connections. This thread is asynchronously starting transactions and queries on the first connection. The application then launches other transactions and queries and waits for the results on another connection. When SQL Server returns a result of one of the connections, the application starts processing these results. The application processes the result until the query of the resulting result is blocked by the query blocked by another connection, there is no result. At this time, the first connection is blocked, and the result is waiting for more results indefinitely. The second connection is not blocked on the lock, but still trying to return the result to the application. However, since the application is blocked and waits for the result on the first connection, the result of the second connection will not be processed.
Avoid blocked methods =============== 1. Use queries for each query to time out.
2. Use locking timeout for each query. For more information, see Custom Lock Timeout. 3. Use the binding connection. For more information, see Connecting using the binding. 4. SQL Server is essentially a shackle that is manipulated by the client application. The client application has almost complete control over the server (and responsible for the lock). Although SQL Server
The lock manager automatically uses lock protection transactions, but this is directly encouraged by the query type emitted by the client application and the processing of the results. Therefore, most of the solutions to the problem involve checking the client application.
5. Blocking issues often require the application behavior itself related to the application submitted by the application, and check the application behavior itself of the processing and the processing of all results rows. If the development tool does not allow explicit control connection management, query timeout, result processing, etc., blocking problems may not be resolved.
Criteria for designing applications to avoid blocking includes =============== 1. Do not use or design to enable users to fill in the application of the edit box, the edit box generates a query for a long time. For example, do not use or design a prompt user input application, allowing some fields to keep blank or allow input of wildcards. This can cause the application to submit an excessive query, resulting in blocking problems.
2. Do not use or design to enable users to enter content in transactions. 3. Allows the cancellation. 4. Use queries or lock timeout to prevent out-of-control query and avoid distributed deadlocks. 5. Immediately complete the extract all the results. 6. Make the transaction as short as possible. 7. Explicit control connection management. 8. The application is tested against the application under full load of the prescribed concurrent user.
Here are some related technical documents. Understanding and resolving sql server 7.0 or 2000 blocking problems
How to: Troubleshoot Application Performance with SQL Server
