Deadlocks
The Deadlocks in Teratrax Performance Viewer reports detailed information about
SQL Server deadlocks. By
default, all deadlocks are detected on SQL Server instances monitored by
Teratrax Performance Agent.
It is useful to correlate deadlocks with blocks since deadlocks are a
result of two conflicting blocks. Since deadlocks are not naturally resolved with time
like blocks, SQL Server
automatically kills one of the processes (Deadlock victim) involved in a
deadlock once a deadlock is detected.
This allows the
other process to continue with its transaction. Although deadlocks can be caused by two short blocks (In
a fraction of a
second), it is the long blocks that usually increase the chances of a
deadlock.
Deadlock Details
You can view the details of a deadlock by right-clicking the item in
the list and selecting "Deadlock Details". The Deadlock Details window provides
the following
information about the deadlock:
- Deadlock Time: Indicates the date and time when the
deadlock occurred.
- Victim Process: SQL Server process ID (spid) of the process
that was killed.
- Victim Program: Application that was running the victim
process.
- Victim Duration: Number of microseconds between the time
the lock request was issued and the time the deadlock occurred.
- Victim Computer: Client computer from
which the victim process was running.
- Victim Login: Login name of the user that
initiated the victim process.
- Victim Database: Database in which victim process was
running.
- Victim SQL: SQL code that caused the deadlock.
- Survivor Process: SQL Server process ID (spid) of the
process that survived.
- Survivor Program: Application that was running the survivor
process.
- Survivor Duration: Number of microseconds between the time
the lock request was issued and the time the deadlock occurred.
- Survivor Computer: Client computer from
which the survivor process started.
- Survivor Login: Login name of the user that
initiated the survivor process.
- Survivor Database: Database for the survivor process.
- Survivor SQL: SQL code that caused the deadlock.
Blocks Escalating to Deadlocks
The following diagram shows the sequence of events leading to a
deadlock. Consider two applications (A1, A2) accessing two different
table (T1, T2):
Event 1: A1 places a lock on T1 inside its transaction and
continues to execute other statements
Event 2: A2 places a lock on T2 inside its transaction and
continues to execute other statements
Event 3: A1 attempts to place a lock on T2 (Needs to access T2
before it can finish the transaction) but has to wait for A2 to release
its lock
At this point, a block is created since A2 is
blocking A1.
Event 4: While A1 is waiting, A2 attempts to place a lock on
T1 (Needs to access T1 before it can finish its own transaction)
SQL Server detects a deadlock when the second
block conflicts with the first block (The two processes
have blocked one another). SQL Server automatically resolves the deadlock by choosing one of
the processes as a deadlock victim and killing it.
|
