Deadlocks are a side effect of blocking and not a SQL Server Bug. Poorly written queries in SQL Server can trigger deadlock in the system. We all knows cyclic dependency causes dead lock.
A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, P1 holds a lock on table A and requests a lock on table B; P2 holds a lock on table B and requests a lock on table A.
The lock manager(Lock Monitor)/ thread checks for deadlocks. When a lock manager/ deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID.
When SQL Server find a deadlock, it kill one process (deadlock victim) rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Database engine choose deadlock victim according to the least cost to rollback.
what can cause deadlocks?
• Worker threads
• Parallel query execution-related resources
• Multiple Active Result Sets (MARS) resources.
They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur.
Trace Flags can be used to output the details of deadlocks as well, and generally provide you the maximum amount of information to help facilitate identifying the root of the problem. In SQL 2000, flags 1204 (basic) and 1205 (detailed) provide information regarding deadlocks.
An Example statement to enable tracing to the ErrorLog for all connections:
DBCC TRACEON (1204, 3605, -1)
DBCC TRACEON (1205, 3605, -1)
DBCC TRACEON (1222, 3605, -1)
The SQL Profiler can also be used to help in identifying deadlocking. In order to identify the deadlocking statements in SQL Profiler, you need to be tracing the following events.
Locks - Lock:Deadlock
Locks - Lock:Deadlock Chain
Stored Procedure - SP:StmtStarting
TSQL - SQL:StmtStarting
How to create profiler trace to find deadlock?
1.Start SQL profiler
2.On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace
3.Click “Show all events”
4.Click the Events tab, above specified events
(i) Add the Deadlock graph event to the trace from the Locks category. You will get an additional tab appears on the Trace Properties window, called Event Extraction Settings.
(ii) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file.
When a deadlock is encountered, the Deadlock chain event will output the SPID's associated with the deadlock. From there, you can look at the StmtStarting event for the SPID's immediately following the Deadlock Event to isolate the problem statements.
This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention.
To translate the database id and object id into names, you can do
You can also run following query to check which process is being blocked.
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
Get the SPID from blocked column
DBCC inputbuffer (SPID)