INF: Deadlock Scenarios and Avoidance in SQLLast reviewed: March 12, 1997Article ID: Q47162 |
The information in this article applies to:
- Microsoft SQL Server versions 4.2x
SUMMARYThis 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 INFORMATIONConsider the following distributed update example:
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:
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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |