Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Resolving Deadlocks in SQL Server 2000 : Page 6

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place.


advertisement
Multiple-resource Deadlocks
The multiple-resource deadlock is sometimes called a 'cycle' deadlock, but that is a misnomer. All deadlocks involve a cycle of some kind. This type of deadlock occurs when each thread requests an incompatible lock on the other's resource.

The grant stage normally requires exclusive locks, because shared locks will not be held to the end of the transaction under the default isolation level of READ COMMITTED. There are many variations of the multiple-resource deadlock, but they can generally be classified into two sets.

Some multiple-resource deadlocks involve exclusive locks only; let's call them X-only. In this group, threads make just exclusive lock requests. Others involve mixed exclusive and shared (X-S). First, threads are granted exclusive locks. Then the threads request shared locks and then they are blocked. The mixed X-S deadlocks can be more subtle and challenging.

Table 6 shows how an X-only deadlock can occur. Note that the X-only type of deadlock works at any isolation level, because exclusive locks are always held to the end of a transaction. This is a fairly easy deadlock to detect and prevent. All you have to do is enforce the rule that transactions in stored procedures or other code have to access common sets of tables in the same order.

Table 6. How a deadlock with exclusive-only locks can occur.

Time

Tran 1



Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Update Authors

Set contract = 0

Where au_id = '172-32-1176'

 

T3

GRANT

 

Update Titles

Set ytd_sales = 0

Where title_id = 'BU1032'

T4

Blocked

Update Titles

Set ytd_sales = 0

Where title_id = 'BU1032'

 

T5

Blocked

 

Update Authors

Set contract = 0

Where au_id = '172-32-1176'

T6

Deadlock Victim

(blocking removed)

T7

 

Commit


The X-S, exclusive with shared, type of deadlock is a more complex and subtle type of deadlock. There are many variations: X-X:S-S, X-X-X:S-S-S, X-IS:IS-X, S-IX:IX-S, X-X:U-U, etc. All of these can occur with the READ COMMITTED isolation level, so they are actually the type that you are more likely to see in subtle and complex deadlocks.

Table 7 shows one example of an X-S type of deadlock. Notice in this case that each transaction attempts to read another row in the same table. The rows are the resources that are locked by the transactions.

Table 7. A simple X-S deadlock.

Time

Tran 1

Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Update Authors

Set contract = 0

Where au_id = '172-32-1176'

 

T3

GRANT

 

Update Titles

Set ytd_sales = 0

Where title_id = 'BU1032'

T4

Blocked

Select *

From Titles

Where title_id = 'BU1032'

 

T5

Blocked

 

Select *

From Authors

Where au_id = '172-32-1176'

T6

Deadlock Victim

(blocking removed)

T7

 

Commit


It might be rather rare that a transaction would want to read another transaction's row just after an update, so Table 8 shows a much more subtle type of X-S deadlock. In this example, each transaction inserts a row into the same table, and then reads the table for more information, perhaps to do another insert. This is a much more common pattern of deadlock, something that transactions in stored procedures are legitimately required to do.

Table 8. A more subtle X-S deadlock.

Time

Tran 1

Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Insert Authors Values

('111-11-1111', 'test1', '', '', '', '', '', '11111', 0)

 

T3

GRANT

 

Insert Authors Values

('111-11-1112', 'test2', '', '', '', '', '', '11111', 0)

T4

Blocked

Select * From Authors

Where contract = 0

 

T5

Blocked

 

Select * From Authors

Where contract = 0

T6

Deadlock Victim

(blocking removed)

T7

 

Commit


Mixed X-S deadlocks can be much more difficult to diagnose. They can take place with the default isolation level of READ COMMITTED or higher and involve SELECT statements.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date