Blocking is a classic database problem in which a transaction locks a first record and then a second record. If a subsequent transaction then runs and attempts to lock those same records, and performs the lock in the opposite order just after the first transaction, you have a deadlock and both transactions cannot get the resources they need to complete their transaction.

SQL Server when run as a single server can often detect deadlocks and terminate one of the transactions. However, when a single client application is updating multiple SQL servers, then deadlock can occur because the status of locks is not communicated. Using a two-phase commit won't help you here.

To avoid deadlocks you can design your application so that the two transactions both request their locks in the same absolute order. This will eliminate many problems, but not all, as in the instance where a transaction reads and updates a record with HOLDLOCK. HOLDLOCK prevents other transactions from updating what was read. So in this latter example, both transactions can deadlock because both can get the READLOCK, but neither can update since each is waiting for the other to release its READLOCK. While SQL Server also can detect this kind of deadlock on a single server, it cannot correct the problem when multiple servers are involved. Using the BROWSE mode or timestamps rather than HOLDLOCK can provide a solution.

Other Microsoft Knowledge Base articles on this topic may be found at support.microsoft.com. Look for the following articles: Q162361, Q169960, and Q43199.