INF: Deadlock Scenarios and Avoidance in SQL

Last reviewed: March 12, 1997
Article ID: Q47162

The information in this article applies to:

  - Microsoft SQL Server versions 4.2x

SUMMARY

This article describes common deadlock scenarios and avoidance techniques.

In the classic deadlock, a transaction locks record 1 and later locks record 2. If a second transaction attempts to lock the same two records, but in the opposite order, and happens to run just after the first transaction acquired its first lock, a deadlock occurs.

In a single-server environment, SQL Server detects this kind of deadlock and abnormally terminates one of the transactions. If a single client application is performing updates on multiple SQL servers, permanent deadlocks are possible because the participating SQL Servers do not communicate with each other regarding lock status and requests. This is true even if 2 phase commit service is being used.

MORE INFORMATION

Consider the following distributed update example:

  1. An application performs updates on its local server and also on a remote server.

  2. When another copy of that application runs on the other server, a deadlock occurs because "local" and "remote" are relative to each application.

In absolute terms, the two applications are requesting their locks in opposite order.

To correct this problem, request locks in the same absolute order.

It is also possible for two applications to deadlock even if they request locks in the same order, as in the following example:

  1. An application reads a record with holdlock (to prevent others from updating what it has read) and later updates the record.

  2. Two copies of this application can deadlock because both can get the readlock, but neither can update (each is waiting for the other to release the readlock).

SQL Server detects this kind of deadlock on a single server; however, it cannot do so if the lock dependencies span multiple servers.

To correct this problem, use SQL Server BROWSE mode or timestamps rather than read with holdlock. For more information, refer to the "Maximizing Database Consistency and Concurrency" manual that ships with SQL Server version 4.2.


Additional query words: 4.20 wait
Keywords : kbother ssrvlock
Version : 4.20
Issue type : kbtshoot


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 12, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.