ID Number: Q47295
1.11 4.20
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.
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.
Additional reference words: 1.11 4.20 2phase