Microsoft KB Archive/47295

= INF: Deadlocks and Two-Phase Commit =

Article ID: 47295

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q47295



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.

Additional query words: 2phase

Keywords: kbusage KB47295

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.