Types of Waits
According to SQL Server Books Online
, SQL Server threads can wait on
- Parallel query resources
- Other threads
- Memory resource
- Application events
Deadlocking can occur with locks, parallelism, threads, and application events. (Memory waits are resolved by query time-out.) The most frequent source of SQL Server deadlocking is resource locking where the resources are table or index objects.
Deadlocks Involving Locks
Lock-based deadlocks involve two or more threads, at least one transaction, and one or more resources. It's useful to view deadlocks as occurring in two stages. The first is a grant stage, where each thread is granted a lock on its resource. They could be the same resource, but it's much more common that they are different resources.
The second stage is a blocked request where each thread requests an incompatible lock on the other thread's resource. Each thread waits on the other to release its locks before it can complete. SQL Server detects the deadlocked state and rolls back one of the transactions.
|Deadlocking is more than blocking. Blocking occurs when one thread is waiting on another, and some brief blocking is normal.|
Deadlocking is more than blocking. Blocking occurs when one thread is waiting on another, and some brief blocking is normal. Blocking is expected in any database system that uses locking to maintain transaction isolation. Only blocks with long durations should be considered a problem. In an active system, short periods of blocking may be happening quite often. Lock-based deadlocking is a special type of blocking where two or more threads mutually block each other, and that's what you need to avoid.
How SQL Server handles a Deadlock
In SQL Server 2000, the Lock Monitor thread detects the deadlock. It uses a periodic detection system, inspecting processes about every 5 seconds to determine if there are any deadlock cycles. When it finds one, it automatically chooses one thread as the deadlock victim. It then rolls back the victim thread's transaction, cancels its query, and returns error 1205 to its client.
The Lock Monitor generally chooses the least expensive transaction to roll back. You can override this somewhat using SET DEADLOCK_PRIORITY to LOW for a session. But whenever both threads have the same DEADLOCK_PRIORITY setting, the Lock Monitor will have to choose one of them as the victim.
The message delivered by error 1205 is mysterious or entertaining, depending on your point of view:
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 54) was deadlocked
on lock resources with another process and
has been chosen as the deadlock victim.
Rerun the transaction.
Error 1205 is not fatal to the connection; your connection is not terminated. SQL Server aborts the batch, rolls back the transaction, and cancels the query. As a result, error 1205 cannot be detected from within Transact-SQL by @@ERROR within the batch or from calling a stored procedure, because the entire calling batch is cancelled. It has a severity level of 13, correctable by the user, so the client can resubmit the query.
Unfortunately, error 1205 does not tell you the identity of the other participating spid or spids that your process deadlocked with. It also does not reveal statements at the grant or blocked request stage, so you are not sure what commands set up the deadlock conditions in the first place.
SQL Server Deadlocking Factors
You can view deadlocks as containing four factors:
- Lock modes and incompatibility
- Transaction timing
- The order of lock requests
- Isolation level of the transactions