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 5

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
Types of Lock Deadlocks
Deadlocks can involve more than two threads, often quite a number of threads. When many threads are involved in a deadlock, SQL Server will choose one of the threads as a victim, return to the deadlock, choose another thread as a victim, and then eventually narrow the entire deadlock down to a core of two threads.

You can view deadlocks as falling into two categories, depending on whether the transactions initially lock the same or different resources. When the transactions each lock a single resource, let's call that a "single-resource" deadlock, and when there is more than one resource, we'll call that a "multiple-resource" deadlock.

Single-Resource Deadlocks
The single-resource deadlock is sometimes called 'conversion' deadlock, because it occurs when each transaction simultaneously attempts to convert its compatible lock to an incompatible lock.

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.
The initial grant stage requires shared locks, because both threads must obtain a lock on a single resource. The single-resource deadlock also requires an isolation level higher than READ COMMITTED so that the share locks will continue to be held through the end of the transaction. The blocked request stage requires exclusive or update locks, locks that are incompatible with shared locks.

Table 5 shows how a single-resource deadlock can occur. The HOLDLOCK hint causes each SELECT to enter into the SERIALIZABLE isolation level, so that the shared locks are held for the duration of the transaction. This is a comparatively rare type of deadlock because the SERIALIZABLE isolation level and HOLDLOCK hint are rarely used in practice.

Table 5. A single-resource deadlock transaction history.

Time

Tran 1



Tran 2

T1

Begin Tran

Begin Tran

T2

GRANT

Select *

From Authors With (HOLDLOCK)

Where au_id = '172-32-1176'

 

T3

GRANT

 

Select *

From Authors With (HOLDLOCK)

Where au_id = '172-32-1176'

T4

Blocked

Update Authors

Set contract = 0

Where au_id = '172-32-1176'

 

T5

Blocked

 

Update Authors

Set contract = 1

Where au_id = '172-32-1176'

T6

Deadlock Victim

(blocking removed)

T7

 

Commit





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