Blocking and Deadlocks

3 4

Blocking and deadlocks are two additional problems that can appear with concurrent transactions. They can cause major problems for a system and can slow and even halt performance. These problems can be handled in the application, or SQL Server will deal with them as best it can; they will be described here only so that you will be aware of them and understand the concepts. Avoiding and resolving blocking and deadlock issues is the responsibility of the programmer.

Blocking occurs when one transaction is holding a lock on a resource and a second transaction requires a conflicting lock type on that resource. The second transaction must wait for the first transaction to release its lock—in other words, it is blocked by the first transaction. Blocking usually occurs when a transaction holds a lock for an extended period, causing a chain of blocked transactions that are waiting for other transactions to finish so that they can obtain their required locks—a condition referred to as chain blocking. Figure 19-1 shows an example of chain blocking.

click to view at full size.

Figure 19-1. Chain blocking.

A deadlock differs from a blocked transaction in that a deadlock involves two blocked transactions waiting for each other. For example, assume that one transaction is holding an exclusive lock on Table_1 and a second transaction is holding an exclusive lock on Table_2. Before either exclusive lock is released, the first transaction requires a lock on Table_2 and the second transaction requires a lock on Table_1. Now each transaction is waiting for the other to release its exclusive lock, yet neither transaction will release its exclusive lock until a commit or rollback occurs to complete the transaction. Neither transaction can be completed because it requires a lock held by the other transaction in order to continue—deadlock! Figure 19-2 illustrates this scenario. When a deadlock occurs, SQL Server will terminate one of the transactions, and that transaction will have to be run again.

click to view at full size.

Figure 19-2. Deadlock.

MORE INFO


For basic information about how to avoid blocking and deadlocks, look up "Blocks" in the Books Online index and select "Understanding and Avoiding Blocking" in the Topics Found dialog box. Also, look up "Deadlocks" in the Books Online index and select "Avoiding Deadlocks and Handling Deadlocks."



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net