INF: Deadlocks and Two-Phase Commit

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

Two-phase commit adds no extensions to lock services. Each server knows only about its local locks, which makes it impossible to detect deadlocks that span multiple servers. Until SQL Server provides full distributed update support, you should practice standard deadlock avoidance techniques.

MORE INFORMATION

Two applications can deadlock only if they request multiple locks at different times and in different orders. If an application locks file A and later locks file B, it can deadlock with another application only if the second attempts to lock file B before file A. If both applications lock in the same order, one may wait, but no deadlock occurs.

If your application locks items in the local database and then in the remote database, two copies of it deadlock because what is local to one is remote to the other and each application locks in the opposite order.

To avoid this problem, have each application lock items in database A before attempting to lock items in database B, regardless of whether database A is local or remote.

To confirm what is occurring, produce the deadlock and run sp_lock on each server. This procedure displays the locks being held by each process. The table ID can be decoded to a table name by looking it up in SYSOBJECTS.