Introduction To Deadlock
Deadlock in SQL server is a condition in which two or more system server processes IDS (SPIDs) are waiting for a resource. No process can get the resource as the other processes are preventing it from getting the resource. It is a very common issue in SQL Server, whenever this situation occurs in the system, the server will choose one of the process as a victim and rollback that process. After it rollbacks the victim process, it allows other processes to execute as there is no other way to resolve the conflict.
Lock Manager
The thread of lock manager checks the deadlock condition. When the lock manager algorithm finds a deadlock, it will select one process as a victim and send error message 1205 to the client. Lock manager then kills that selected SPID. Killing the victim SPID will free up the resource and will allow other processes to continue with the resource.
How To Identify Deadlock in SQL Server
First, to identify the deadlock, one should gather information about suspected processes and resources. To get this information one needs to add the -T1204 and -T3605 startup parameters or we can call Trace Flags to SQL server.
Steps for adding startup parameters:
- Start SQL Server Enterprise Manager.
- Select and then right-click the server.
- Click on Properties.
- Go to the Startup Parameters.
- In the Startup Parameters, enter-T1204 in the Parameters text box.
- Then click on Add.
- Again in Parameters text box, type -T3605.
- Then click on Add.
- Click on OK.
The startup parameter -T1204 will gather information about processes and the resource when the lock manager encounters a deadlock. Whereas -T3605 startup parameter write this collected information on a log file.
Three Events to capture Deadlock in SQL Server
User can also go for the profiler to capture the deadlock, there are three events that can capture the deadlocks. These events are present in Lock event class.
- Deadlock graph – Deadlock graph provides XML description of the deadlock.
- Lock: Deadlock – It indicates the two processes that has deadlocked each other in order to acquire the resource.
- Lock: Deadlock Chain – It generate all the events that causes the deadlock condition.
How to Handle Deadlock in SQL Server
To handle deadlock in SQL Server, user can go for following techniques given below:
The Lock Manager
The SQL server detects the deadlock victim by two ways:
- Lock Manager will assign the priority to the processes competing for the resources using Deadlock_Priority. The process with lowest priority will always be considered as deadlock victim.
- If two or more processes have same priority then, the process that has lowest cost will be chosen as victim.
Trace Flag 1204
It captures the details of deadlock in SQL Server and provides information about the node involved in deadlock condition. User can use DBCC TRACEON(1204, -1) format to capture the details of involved processes and resource.
Trace Flag 1222
It returns the deadlock information using XML format, the Flag 1222 captures the graph, which is in more comprehensive format as compared to Output information of Flag 1204. It captures the deadlock events in the form of graph.
WMI Provider
The WMI (Windows Management Instrumentation) Event Provider is used for creating SQL Server Agent alerts that respond to specific events. For later analysis, it generates an alert that saves XML deadlock graph events in a table. SQL Server Agent submits a WQL (WMI query language) request, receives the WMI events, and runs a job in response to the event.
By Capturing The Graph
In this, user will get the information about processes and resource involved in deadlock using graph. It offers the XML deadlock graph event in profiler, which captures more information about deadlock events than trace flag 1204.
Conclusion:
In this article, we have captured the different ways of handling deadlock conditions in SQL Server and the reasons that causes the deadlock in the server. By using techniques mentioned above, one can easily find the events or different processes and resource that are involved in deadlock condition.