Resolving Deadlocks in SQL Server 2000
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. In this article, you'll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.
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. In this article, you'll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.
When
your application must handle complex transactions and multiple users,
SQL Server deadlocking can be an annoying and difficult problem.
Deadlocking
is not a problem that is unique to SQL Server. Any database system that
relies on locking to ensure that user transactions do not interfere
with each other is subject to deadlock conditions. In order to
understand and resolve SQL Server deadlocks, it's important to
understand the basic concepts underlying deadlocking in SQL Server.
Deadlocking Concepts
The
key concept behind deadlocking is the transaction. To give your users a
consistent view of the database, where either all changes in a
transactional unit of work succeed or all fail, the database system must
lock some resources while the work is being done. A SQL Server deadlock
occurs when two or more processes have acquired locks on their
respective resource, and they need to get an incompatible lock on the
other's resource in order to finish a transaction. The result is mutual
blocking: each waits on each other to acquire some resource that the
other process already has.
| " | A SQL Server deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other's resource in order to finish a transaction. The result is mutual blocking: each waits on the other to acquire some resource that the other process already has. | " |
The
result is a situation where neither process can finish. SQL Server's
lock manager will detect a deadlock cycle and end one of the
transactions. Table 1 shows how, in general, a deadlock occurs.
Read
the table from top to bottom, imagining time to progress from instance
T1 through T7. By time T3, Transaction1 and Transaction2 have both been
granted locks on some resource. At time T4, Transaction1 requests an
incompatible lock on the resource already locked by Transaction2, and is
blocked. At that point, Transaction1 goes into a wait state, waiting
for the lock to be released.
At
time T5, Transaction2 requests an incompatible lock on the resource
that Transaction1 already has locked. At this point, Transaction2 also
goes into a wait state, and each process is blocking the other. This is a
deadlock cycle, and here is where SQL Server will detect the deadlock
cycle and end one of the transactions.
Types of waits
According to SQL Server Books Online, SQL Server threads can wait on
- Locks
- 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 1Transaction (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.
