Deadlocking

Deadlock is a condition that can occur on any system with multiple users, not only on a relational database management system. A deadlock occurs when two users (or sessions) have locks on separate objects and each user wants a lock on the other’s object. Each user waits for the other to release their lock.

For example, transaction 1 has an exclusive lock on the Supplier table. Transaction 2 obtains an exclusive lock on the Part table, and then wants a lock on the Supplier table. Transaction 2 cannot obtain the lock because transaction 1 has it. Transaction 2 is blocked, waiting on transaction 1. Transaction 1 then wants a lock on the Part table, but cannot obtain it because transaction 2 has it locked. Neither transaction can release the locks they hold until they commit or roll back, but neither can commit or roll back because they require a lock held by the other transaction to continue.


Note Deadlocking is often confused with normal blocking. When one transaction has a lock on a resource another transaction wants, the second transaction waits for the lock to be released. By default, SQL Server transactions do not time out (unless LOCK_TIMEOUT is set). The second transaction is blocked, not deadlocked. For more information, see Customizing the Lock Time-out.


  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.