Tag Archives: handle sql deadlock

From a DBA point of view, Deadlocks occurred in Production environment are probably the trickiest and time consuming problems to fix. Every time I came across this, it took me a while to understand where it is coming from, why, and how to avoid a particular Deadlock. This blog will go through some types of deadlocks and how to deal with them.

Here I will analyze a Deadlock caused by an After Insert trigger that acts like a constraint.

Usually people see the Deadlock error after the Deadlock already happened – thrown by client applications or DB Monitoring tools (ex: SQL Diagnostic Manager – Idera).

1. Provoke Deadlock:

a) Create a table with a Primary Key and a Trigger on it that check that same Security Number is not inserted for a new client.

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

2. Start Debugging:

Look at the error you caught. If you have a DB monitoring tool which captures deadlocks (I have worked with Idera and I recommend it), than you will probably have already some informations about the deadlocked process.

process1816da508 has X (exclusive) lock on PK_Clientsprocess1816da088 waits for S (Shared) lock on PK_Clients
From these lines I can get the name of the first resource demanded by both processes: primary key PK_Clients from Clients table.

The second resource demanded by both processes seems to be also the primary key PK_Clients of Clients table. It can be extracted from the other 2 types of locks held:

How can this be possible? In all documentation the deadlock is described as a conflict between at least 2 different resources.

Now, I have the same resource on which each one of the concurrent processes has X locks and claims S lock. The single logical explanation refers to the fact that SQL would put Row Locks (default Locking level) if the Clustered Index didn’t exist. In Row Lock mechanism transactions are not blocked because each one is modifying a different row from a table. The presence of PK makes a little different but not completely.

After each Insert SQL will have to make a Clustered Index Insert with a new value inserted on ClientID column. So it will request Exclusive Key Lock on PK_Clients pointing a different row from the Clustered Index. This allows other concurrent transactions to update the Index. So, first conclusion is that the Deadlock problem happens inside the trigger.

After each Insert, the trigger is fired – the select from trigger will do an Index Scan on PK_Clients, in this way claiming Shared locks. How do I know all this? Looking at the execution Plan for Trigger code – I see a Clustered Index Scan to extract the SecurityNR value.

Shared locks read only commited data. It means that the second Transaction will wait after the first row has been commited from the First Transaction. But the first Transaction has one more Insert coming which will have the exact behavior – only now it will claim Shared Lock on PK_Clients that is already locked by second Transaction – inserted row from second Transaction.

This is how the Locking occured to my example:

4. Fix the Problem

There are more ways to fix this problem:

Avoid Index Scan on PK_Clients when trigger is fired –> Create Index on SecurityNR column which will avoid the PK_Clients index. Instead the trigger will perform an Index Seek on new NonClustered Index.

Putting No_Lock hints at the Select from trigger will fix the deadlock as well, but this is very risky because you can have dirty data reads and corrupt data.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE – option set per session/transaction. This is the highest Isolation Level and will prevent concurrent Inserts, causing a lot of blockages. More on this here.

About me…

I am from Bucharest, Romania and I am working with SQL Servers since 2006. I have been working closely with all SQL versions starting with SQL Server 2000. I am a senior DBA at OpenSky company and spend my free time with my husband and my son.
See more about me and why I started this blog.