INF: Deadlock Scenarios and Avoidance in SQL

ID Number: Q47162

1.11 4.20

OS/2

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 reference words: Deadlocks holdlock wait BROWSE timestamp