1061
Chapter 47: Managing Transactions, Locking, and Blocking
47
and■ Transaction 2 has a lock on data B and needs to lock data A to complete its
transaction.
Each transaction is stuck waiting for the other to release its lock, and neither can complete
until the other does, and each process will not release the resource it already has for the
other process to use. This stalemate continues until the database engine chooses a victor.Deadlocks do not always only include two transactions. It is completely possible that
Process A can be waiting on a resource held by Process B, which is, in turn, waiting on a
resource held by Process C. If C is waiting on a resource that Process A or Process B has
locked, a deadlock is created.Creating a Deadlock
It’s easy to create a deadlock situation in SQL Server using two connections in Management
Studio’s Query Editor, as illustrated in Figure 47-5. Transaction 1 and transaction 2 simply
try to update the same rows but in the opposite order. Use a third window to watch the
locks using Activity Monitor or one of the DMV queries.FIGURE 47-5
Creating a deadlock situation in Management Studio using two connections tiled vertically.c47.indd 1061c47.indd 1061 7/31/2012 10:23:29 AM7/31/2012 10:23:29 AM
http://www.it-ebooks.info