Tip

Avoid blocking in Microsoft SQL Server administration

Barrie Sosinsky, Contributor

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

    Requires Free Membership to View

support.microsoft.com. Look for the following articles: Q162361, Q169960, and Q43199.

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.