Avoid blocking in Microsoft SQL Server administration

Learn why you should avoid blocking in SQL Server, a classic database problem in which a transaction locks a first record and then a second record.

Blocking is a classic database problem in which a transaction locks a first record and then a second record. If

a subsequent transaction then runs and attempts to lock those same records, and performs the lock in the opposite order just after the first transaction, you have a deadlock and both transactions cannot get the resources they need to complete their transaction.

SQL Server when run as a single server can often detect deadlocks and terminate one of the transactions. However, when a single client application is updating multiple SQL servers, then deadlock can occur because the status of locks is not communicated. Using a two-phase commit won't help you here.

To avoid deadlocks you can design your application so that the two transactions both request their locks in the same absolute order. This will eliminate many problems, but not all, as in the instance where a transaction reads and updates a record with HOLDLOCK. HOLDLOCK prevents other transactions from updating what was read. So in this latter example, both transactions can deadlock because both can get the READLOCK, but neither can update since each is waiting for the other to release its READLOCK. While SQL Server also can detect this kind of deadlock on a single server, it cannot correct the problem when multiple servers are involved. Using the BROWSE mode or timestamps rather than HOLDLOCK can provide a solution.

Other Microsoft Knowledge Base articles on this topic may be found at support.microsoft.com. Look for the following articles: Q162361, Q169960, and Q43199.

This was first published in February 2005

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close