Daily Archives: April 1, 2011

Handling SQL Deadlocks – One Resource Conflict

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.

CREATE TABLE [dbo].[Clients]
([clientid] [int] NOT NULL,[name] [nvarchar](50) NULL,[SecurityNR] [int] NULL,
 CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED 
([clientid] ASC)) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[SecurityNR] ON [dbo].[Clients]
AFTER INSERT
AS
DECLARE @SecNR Int
SET @SecNR  = (select SecurityNR from Inserted)
IF EXISTS (SELECT count (1)
           FROM Clients c
           where c.SecurityNR = @SecNR having count (1) >1)
BEGIN
RAISERROR ('This client''s SecurityNR is assigned to another Client', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

Let’s assume that there are many concurrent transactions running on the Server that insert 1..to..many new clients inside one transaction.

b) Start a Transaction and Insert one client and do not commit it.

BEGIN TRANSACTION
INSERT INTO [test2].[dbo].[clients]       
 ([clientid],[name],[SecurityNR]) 
VALUES (3,'Chris',12345680)
GO

c) Start a second Transaction and Insert one client and do not commit it.

BEGIN TRANSACTION
INSERT INTO [test2].[dbo].[clients]       
 ([clientid],[name],[SecurityNR]) 
VALUES (4,'Lya',12345682)
GO

At this point the second Transaction will be blocked by the first one.

d) Return to first Transaction and insert another client and commit transaction

BEGIN TRANSACTION
INSERT INTO [test2].[dbo].[clients]       
 ([clientid],[name],[SecurityNR]) 
VALUES (5,'Mary',12345681)
GO

Now, the Deadlock will occur.

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.

Alert displayed in IDERA:

A deadlock occurred on server [SERVER NAME]. 

The deadlock victim was spid 58 with application name '[APPLICATION NAME]' 
by user '[USER NAME]' on host '[MACHINE NAME]'.
Last command issued:
Proc [Database Id = 13 Object Id = 1157579162]

Whether you have or not a monitoring tool, next step is to activate the Deadlock traceflag and analyse its output from SQL Server Log after you encounter another deadlock:

-- SQL 2005 - 2008: 
DBCC TRACEON (1222, -1)

-- SQL 2000 :
DBCC TRACEON (1204, -1)

3. Collect Deadlock Trace Output

Here is the important info that must be extracted from the Log file.

A: deadlock-list

  1. deadlock victim=process1816da088

B: process-list

    1. process id = process1816da088 waitresource = KEY: 7:72057594040483840 (03000d8f0ecc) lockMode = S spid = 53 clientapp = Microsoft SQL Server Management Studio – Query loginname = irina-VAIOirina

 

    1. frame procname = test2.dbo.SecurityNR

 

IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR
having count (1) >1)
    1. frame procname = adhoc

 

INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR])
values(4,'Lya',12345682)
    1. process id = process1816da508 waitresource = KEY: 7:72057594040483840 (0400b4b7d951) lockMode = S spid = 54 clientapp = Microsoft SQL Server Management Studio – Query loginname = irina-VAIOirina

 

    1. frame procname = test2.dbo.SecurityNR

 

IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR
having count (1) >1)
    1. frame procname = adhoc

 

INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR])
values((5,'Mary',12345681))

C: resource-list

  1. keylock dbid = 7 objectname = test2.dbo.Clients indexname = PK_Clients mode = X
  2. owner id = process1816da508 mode = X
  3. waiter id= process1816da088 mode = S requestType = wait
  4. keylock dbid = 7objectname = test2.dbo.Clients indexname = PK_Clients mode = X
  5. owner id = process1816da088 mode = X
  6. waiter id = process1816da508 mode = S requestType = wait

3. Analyze Deadlock Trace Output

At A: deadlock-list I see the deadlocked process number – process1816da088.
Lower at B: process-list, I can see the SQL statements of the 2 processes caught in the deadlock:

    • process1816da088 – spid 53 – executed:

 

INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR]) values(4,'Lya',12345682)
IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR having count (1) >1)
    • process1816da508 – spid 54 – executed:

 

INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR]) values(5,'Mary',12345681)
IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR having count (1) >1)

Lower at C:resource-list:

process1816da508  has X (exclusive) lock on PK_Clients
process1816da088 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:

process1816da088 has X (exclusive) lock on PK_Clients
process1816da508 waits for S (Shared) lock on PK_Clients

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:

Deadlock schema

 

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.