-
Notifications
You must be signed in to change notification settings - Fork 0
/
Deadlocks.txt
79 lines (65 loc) · 4.71 KB
/
Deadlocks.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
Deadlocks
What is a dead lock?
Process 1 has locked A and needs to move to B next.
Process 2 has blocked B and needs to move to A next.
Process 1 can’t unlock A until it has access to B.
Process 2 can’t unlock B until it has access to A.
Both are waiting for the lock to be released.
-----locks------>TableA<----waits-----
| |
| |
| |
Process A Process B
| |
| |
| |
----waits------->TableB<----locks-----
When a deadlock occurs SQL Server will choose one of the processes as the deadlock victim and rollback the process, so that the other process can move forward.
How does SQL Server detect deadlocks?
A lock monitor thread runs every five seconds to detect if there are any deadlocks. If the lock monitor thread finds a deadlock, the deadlock detection interval will drop from five seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches back to five seconds.
What happens when a deadlock is detected?
When a deadlock is detected, the Database Engine ends the deadlock by choosing one of the processes as the Deadlock Victim.
The deadlock victim's transaction is ended and rolled back and all locks are released for that transaction.
The system returns a 1205 error to the application with a message. The other transactions may now move forward.
How does Sql Server determine the priority of transactions?
By default SQL Server chooses a transaction as the deadlock victim that is least expensive to rollback.
However, a user can specify the priority of the sessions in a deadlock by using the SET DEADLOCK_PRIORITY statement.
The session with the lowest deadlock priority is chosen as the deadlock victim.
DEADLOCK_PRIORITY can have the values LOW, NORMAL and HIGH. NORMAL is the default.
DEADLOCK_PRIORITY can also be set using an integer value ranging from -10 to 10. NORMAL would be 0.
LOW would be a value less than 0 and HIGH would be a value greater than 0.
Example-
SET DEADLOCK_PRIORITY NORMAL
Begin Tran
.....
If the transactions causing the deadlock have different deadlock priorities then the one having the lowest priority will be chosen as the deadlock victim.
If the transactions causing the deadlock have the same priority then the transaction having the least expensive rollback is selected as the victim.
This would mean the process that is used the least log space i.e. 'Log Used' in the transaction log.
If the transactions causing the deadlock have the same priority and same cost then the deadlock victim is chosen randomly.
Deadlock information can be written to a log.
There are several ways to track down the queries that are causing deadlocks.
One option is to use SQL Server trace flag 1222 to output deadlock information to the SQL Server error log.
Use the following commands to initiate, check the status of and terminate the trace flag
DBCC Traceon(1222,-1)
DBCC TraceStatus(1222,-1)
DBCC Traceoff(1222,-1)
The parameter -1 indicates that the trace flag must be set at the global level. If the -1 is omitted then the trace flag will be set at the session level.
To access and read the error log:
execute sp_readerrorlog
What does the error log contain and how can we use the information?
The error log contains the following information regarding deadlocks
Deadlock victim - contains the ID of the process that was selected as the deadlock victim and killed by SQL Server.
Process List - contains the list of processes that participated in the deadlock.
Resource List - Contains the list of resources(database objects) owned by the processes involved in the deadlock
The Process List contains the following Nodes and their descriptions -
loginname - the login name associated with the process
isolationlevel - what isolation level was used
procname - the stored procedure name
inputbuf - the code the process is executing when the deadlock occured
owner-list - contains the owner id of the process and the lock mode it has on the resource.
lock mode determines how the resource can be acccessed by concurrent transactions. S for shared lock, U for Update lock, X for Exclusive lock
waiter-list - contains waiter id of the process that is waiting on the lock and the lock mode it is requesting.
Using Sql profiler to capture deadlock graph
To capture a graph of a deadlock event add Deadlock graph event to the trace in SQL Profiler
The deadlock graph data is captured in XML format which can be extracted to a physical file.