WEBINAR:
On-Demand
Building the Right Environment to Support AI, Machine Learning and Deep Learning
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
|
Previous Page
