SQL Server - How to generate a deadlock history for failure analysis in routines

Reading Time: 9 minutes

Hello people,
All right with you ? Hope so!

In this post I would like to share with you a situation where a deadlock occurred at dawn, disrupting a critical routine, and I needed to identify which session caused the deadlock to plan actions to avoid this situation. To help identify these situations and have a history of deadlocks, I will present some solutions to achieve this goal.

What is a deadlock?

A deadlock is generated when two or more processes try to access the same object by locking it. Therefore, these processes try to perform the same action at the same time on the same object, and one process waits for the other to remove the lock to continue the operation.

For example, process A locks the client table to perform an update. Process B also locks the client table to delete at the same time as process A. So process A waits for process B to finish deleting and removes the lock to continue and process B is waiting for process B Finish updating and remove lock to continue.

Deadlock Illustrated

If SQL Server does not take action, they would wait for each other infinitely, and this is called a deadlock.

How to generate / simulate a deadlock

To understand in practice what a deadlock is and how it works, I will show you how it happens in everyday life and how SQL Server behaves when the deadlock is identified.

Now let's generate the deadlocks:

Deadlock Generated:

Msg 1205, Level 13, State 45, Line 10
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

As soon as the Deadlock Monitor Thread identified that the instance was deadlocking, it tried to eliminate the most recent session (55), allowing session 58 to be processed, thus "resolving" the generated deadlock.

How Deadlock Monitor Thread Works

In SQL Server there is a feature called Deadlock Monitor Thread, which runs in the background to identify and “help” resolve deadlocks in the instance, thus preventing the sessions from endlessly waiting for each other.

If you query the sys.dm_os_waiting_tasks DMV, you will notice that there is always a system task with the REQUEST_FOR_DEADLOCK_SEARCH event.

This thread fires every 5 seconds to check for deadlocks on the instance. If it encounters any deadlock, it will kill one of the deadlocked sessions to free the locked resources for the other waiting session.

How does SQL Server decide which session it will drop? It is very simple, it will always eliminate the session that has the lowest cost (generally, the one that was “locked” last), thus facilitating the rollback of transactions performed by the session that was chosen to be disconnected (deadlock victim) As long as they have the same priority. If the sessions have the same priority and the same cost, the victim of the deadlock will be chosen at random.

When the Deadlock Monitor Thread drops a session due to deadlock, it runs again immediately to verify that the deadlock has been resolved. If there are still deadlocks in the instance, it will eliminate more sessions and shorten the next execution cycles in 100ms (each cycle) until no deadlock is detected.

How to set a session priority

When the Deadlock Monitor Thread identifies that a deadlock has occurred and begins to evaluate which session it will eliminate in order to "resolve" this, the first criterion to be considered is the session priority. By default, all sessions on SQL Server have the priority value = 0 (NORMAL). To change the priority of a session, we have the command SET DEADLOCK_PRIORITY.

Parameters:
- LOW: Priority below NORMAL, whose value is -5.
- NORMAL: Default priority, whose value is 0.
- HIGH: Priority above NORMAL, whose value is 5.
- : It is an integer value range (-10 to 10) to provide 21 deadlock priority levels instead of just 3 if you use the predefined ones.

Examples:

Do you remember the example I created above to simulate deadlock? What if we ran again, but changing the priority of the session that was dropped as a deadlock victim? What would happen?

Here is the excerpt that I was able to finish processing - Now he became the victim of Deadlock

This stretch was the deadlock victim in the previous example, as both sessions had the same priority, but the second session was chosen as the deadlock victim because it had been locked for less time.

Note that by increasing its priority, we have been able to change the behavior of the Deadlock Monitor Thread so that this command, which I consider very important, is not a victim of deadlocks because of other commands that are not so relevant to my business in this example.

How to identify deadlocks that occurred in the instance

There are several methods you can use to identify locks that occurred in the instance so that you can identify and evaluate locks after they occur, as the DBA will not be manually monitoring all deadlocks in all instances of the instance. environment all the time.

How to identify deadlocks using Trace

An easy and practical way to identify instance locks is to enable a trace using the 1222 trace flag, as shown below:

If you want to check if this traceflag is active, just run the command below:

Since this trace is active, whenever an instance deadlock occurs, this event is written to the SQL Server log, which you can query using the sp_readerrorlog:

How to identify deadlocks using System Health DMV's

System Health is a monitoring using Extended Events (XE), like a Default Trace, that runs in the background and collects instance information. It is available from SQL Server 2008 and its information can only be accessed using query.

It is only important to note that System Health does not save deadlock records for long. For this reason, you can create a routine that does it for you or use another method to collect your deadlock logs, such as Extended Events.

Result:

XML Example:

To create a deadlock history manually, simply create a Job that runs this command:

How to identify deadlocks using Extended Events (XE)

The Extended Events feature is very useful for many DBA day-to-day activities, and identifying deadlocks is an excellent justification for getting started. Since the new session configuration interface is only available from SSN 2012 version, I will demonstrate how to create the session using the GUI and also by command line.

Start a new XE session through the SSMS interface

Name the XE session you are creating

Check not to use template

Select the “xml_deadlock_report” event

Select the global fields you want to include in the session (feel free)

Apply the filters you want (or leave blank not to use filters)

Select where you want to save your session logs, maximum size and other settings

Session Settings Summary

After you finish creating the session, be sure to enable it.

Generated script:

To query the event data generated by the XE session, simply execute the query below:

Or if you already want to write the data extracted from XML to the table, follow my query suggestion:

Result:

That's it folks!
I hope you enjoyed the post and see you next time.