Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on different client computers.
Note Some of the actions needing locking protection may not be obvious, for example, locks on system catalog tables and indexes.
Most blocking problems happen because a single process holds locks for an extended period of time, causing a chain of blocked processes, all waiting on other processes for locks.
Common blocking scenarios include:
A long-running query can block other queries. For example, a DELETE or UPDATE operation that affects many rows can acquire many locks that, whether or not they escalate to a table lock, block other queries. For this reason, you generally do not want to intermix long-running decision support queries and online transaction processing (OLTP) queries on the same database. The solution is to look for ways to optimize the query, by changing indexes, breaking a large, complex query into simpler queries, or running the query during off hours or on a separate computer.
One reason queries can be long-running and hence cause blocking is if they inappropriately use cursors. Cursors can be a convenient method for navigating through a result set, but using them may be slower than set-oriented queries.
This can happen if the application cancels a query, for example, using the Open Database Connectivity (ODBC) sqlcancel function without also issuing the required number of ROLLBACK and COMMIT statements. Canceling the query does not automatically roll back or commit the transaction. All locks acquired within the transaction are retained after the query is canceled. Applications must properly manage transaction nesting levels by committing or rolling back canceled transactions.
After sending a query to the server, all applications must immediately fetch all result rows to completion. If an application does not fetch all result rows, locks may be left on the tables, blocking other users. If you are using an application that transparently submits Transact-SQL statements to the server, the application must fetch all result rows. If it does not (and if it cannot be configured to do so), you may be unable to resolve the blocking problem. To avoid the problem, you can restrict these applications to a reporting or decision-support database.
Unlike a conventional deadlock, a distributed deadlock cannot be automatically detected by Microsoft® SQL Server™. A distributed client/server deadlock may occur if the application opens more than one connection to SQL Server and submits a query asynchronously.
For example, a single client application thread has two open connections. It asynchronously starts a transaction and issues a query on the first connection. The application then starts another transaction, issues a query on another connection, and waits for the results. When results are returned by SQL Server for one of the connections, the application starts to process them. The application processes the results until no more results are available because the query generating the results is blocked by the query executed on the other connection. At this point, the first connection is blocked, waiting indefinitely for more results to process. The second connection is not blocked on a lock, but tries to return results to the application. However, because the application is blocked, waiting for results on the first connection, the results for the second connection are not processed.
Two avoid this problem, use either:
SQL Server is essentially a puppet of the client application. The client application has almost total control over (and responsibility for) the locks acquired on the server. While the SQL Server lock manager automatically uses locks to protect transactions, this is directly instigated by the query type sent from the client application and the way the results are processed. Therefore, resolution of most blocking problems involves inspecting the client application.
A blocking problem frequently requires both the inspection of the exact SQL statements submitted by the application and the application’s exact behavior regarding connection management, processing of all result rows, and so on. If the development tool does not allow explicit control over connection management, query time-out, processing of results, and so on, blocking problems may not be resolvable.
Guidelines for designing applications to avoid blocking include:
| Deadlocking | Locking |