Lesson on SQL Server Deadlocks and how to solve

By: Viacheslav Maliutin | Comments (9) | Related: > Transactions

Problem

We created a new SQL Server database for testing with empty tables. When we were doing our testing by just adding a few records we started getting deadlock issues that we didn't see on the same database that had a lot of data.

To give some more background, our application uses ORM (Object Relation Mapping) this let's a programmer to be efficient, especially when he/she is not familiar with SQL. The ORM does a very simple thing – after a row is inserted it just reads this row from the database one more time. This is a totally fair operation, that’s how the ORM knows about values materialized in calculated fields. This problem appeared only after we tried to execute a workload test on a completely empty database. The problem we had was deadlocks, while we are not having the deadlocks when the database contained data.

Solution

To demonstrate how the SQL Server deadlocks occurred I am going to create two separate active transactions opened in two connection windows. We will create some tables and then use transactions to insert some new data.

Recreating the SQL Server Deadlock

Let's start with creating two tables.

-- script 1 - Creation of County and City tables 
CREATE TABLE Country
    (
        CountryID INT NOT NULL IDENTITY(1, 1)
            CONSTRAINT PK_Country PRIMARY KEY,
        CountryName VARCHAR(100),
PopulationSize INT NOT NULL
    )
 
GO
 
CREATE TABLE City
    (
        CityID INT NOT NULL IDENTITY(1, 1)
            CONSTRAINT PK_City PRIMARY KEY,
        CountryID INT NOT NULL,
        CityName VARCHAR(100) NOT NULL,
        PopulationSize INT NOT NULL,
        CountOfHospitals INT NOT NULL,
        SomeProperty1 VARCHAR(100) NOT NULL,
        SomeProperty2 VARCHAR(100) NOT NULL,
        SomeProperty3 VARCHAR(100) NOT NULL,
        SomeProperty4 VARCHAR(100) NOT NULL,
        SomeProperty5 VARCHAR(100) NOT NULL,
        SomeProperty6 VARCHAR(100) NOT NULL,
        SomeProperty7 VARCHAR(100) NOT NULL,
        SomeProperty8 VARCHAR(100) NOT NULL,
        SomeProperty9 VARCHAR(100) NOT NULL,
        SomeProperty10 VARCHAR(100) NOT NULL,
        SomeProperty11 VARCHAR(100) NOT NULL,
        SomeProperty12 VARCHAR(100) NOT NULL,
        SomeProperty13 VARCHAR(100) NOT NULL,
        SomeProperty14 VARCHAR(100) NOT NULL,
        SomeProperty15 VARCHAR(100) NOT NULL,
        SomeProperty16 VARCHAR(100) NOT NULL,
        SomeProperty17 VARCHAR(100) NOT NULL,
        SomeProperty18 VARCHAR(100) NOT NULL,
        SomeProperty19 VARCHAR(100) NOT NULL,
        SomeProperty20 VARCHAR(100) NOT NULL,
        SomeProperty21 VARCHAR(100) NOT NULL,
        SomeProperty22 VARCHAR(100) NOT NULL,
        SomeProperty23 VARCHAR(100) NOT NULL,
        SomeProperty24 VARCHAR(100) NOT NULL,
        SomeProperty25 VARCHAR(100) NOT NULL,
        SomeProperty26 VARCHAR(100) NOT NULL,
        SomeProperty27 VARCHAR(100) NOT NULL,
        SomeProperty28 VARCHAR(100) NOT NULL,
        SomeProperty29 VARCHAR(100) NOT NULL,
        SomeProperty30 VARCHAR(100) NOT NULL,
        SomeProperty31 VARCHAR(100) NOT NULL,
        SomeProperty32 VARCHAR(100) NOT NULL,
        SomeProperty33 VARCHAR(100) NOT NULL,
        SomeProperty34 VARCHAR(100) NOT NULL,
        SomeProperty35 VARCHAR(100) NOT NULL,
        SomeProperty36 VARCHAR(100) NOT NULL,
        SomeProperty37 VARCHAR(100) NOT NULL,
        SomeProperty38 VARCHAR(100) NOT NULL,
        SomeProperty39 VARCHAR(100) NOT NULL,
        SomeProperty40 VARCHAR(100) NOT NULL,
        CONSTRAINT FK_Country
            FOREIGN KEY (CountryID)
            REFERENCES dbo.Country (CountryID)
    )
GO
 
CREATE INDEX IX_City_Not_Covering_Index
    ON dbo.City (CountryID)
    INCLUDE (CityName)
GO

You might have noticed that I created an index on the “CountryID” column and included the “CityName” column. By creating this index I wanted to show that our system had many useful indexes and even with useful indexes we still had a problem.

As usual, to demonstrate a deadlock we need two connections to be opened. In each window I will open a transaction. In the scope of each transaction I will run the same T-SQL command with different data to show how the deadlock will be reproduced.

Okay, let’s start.

In query window #1 run the following code. Note that we are starting a transaction, but not committing or rolling back the transaction. This will create a record for USA with an ID = 1.

-- script 2 - create USA record
-- Connection #1
-- #1 Step 
BEGIN TRAN
 
INSERT INTO dbo.Country
    (
        CountryName,
        PopulationSize
    )
VALUES
    ('USA', 323100000)

In query window #2 run the following code. Note that we are also starting a transaction, but not committing or rolling back the transaction. This will create a record for India with an ID = 2.

-- script 3 - create India record
-- Connection #2
-- #2 Step 
BEGIN TRAN
 
INSERT INTO dbo.Country
    (
        CountryName,
        PopulationSize
    )
VALUES
    ('India', 1324000000 /*Yes, I am impressed either*/)

Now in query window #1 we are going to select data.

-- script 4 - select USA data
-- Connection #1
-- #3 Step
SELECT *
FROM   dbo.Country AS c
WHERE  c.CountryID = 1

Now in query window #2 we are going to select data.

-- script 5 - select India data
-- Connection #2
-- #4 Step
SELECT *
FROM   dbo.Country AS c
WHERE  c.CountryID = 2

Despite the fact that I closed neither transaction #1 nor transaction #2 my selects did what was expected and no deadlock was created. That’s good, but let’s go further.

In query window #1 let's add a city record.

-- script 6 - add New York City record
-- Connection #1
-- #5 Step
INSERT INTO dbo.City
VALUES
    (1,          -- CountryID - int
     'New York', -- CityName - varchar(100)
     8538000,    --As Google just said and that's cool!
     1000,       --From the ceiling
     'SomeProperty1 ',
     'SomeProperty2 ',
     'SomeProperty3 ',
     'SomeProperty4 ',
     'SomeProperty5 ',
     'SomeProperty6 ',
     'SomeProperty7 ',
     'SomeProperty8 ',
     'SomeProperty9 ',
     'SomeProperty10',
     'SomeProperty11',
     'SomeProperty12',
     'SomeProperty13',
     'SomeProperty14',
     'SomeProperty15',
     'SomeProperty16',
     'SomeProperty17',
     'SomeProperty18',
     'SomeProperty19',
     'SomeProperty20',
     'SomeProperty21',
     'SomeProperty22',
     'SomeProperty23',
     'SomeProperty24',
     'SomeProperty25',
     'SomeProperty26',
     'SomeProperty27',
     'SomeProperty28',
     'SomeProperty29',
     'SomeProperty30',
     'SomeProperty31',
     'SomeProperty32',
     'SomeProperty33',
     'SomeProperty34',
     'SomeProperty35',
     'SomeProperty36',
     'SomeProperty37',
     'SomeProperty38',
     'SomeProperty39',
     'SomeProperty40'
    )

In query window #2 let's add a city record.

-- script 6 - add Delhi record
-- Connection #2
-- #6 Step
INSERT INTO dbo.City
VALUES
    (2,        -- CountryID - int
     'Delhi',  -- CityName - varchar(100)
     19980000, --As Google just said and that's cool!
     2000,     --From the ceiling
     'SomeProperty1 ',
     'SomeProperty2 ',
     'SomeProperty3 ',
     'SomeProperty4 ',
     'SomeProperty5 ',
     'SomeProperty6 ',
     'SomeProperty7 ',
     'SomeProperty8 ',
     'SomeProperty9 ',
     'SomeProperty10',
     'SomeProperty11',
     'SomeProperty12',
     'SomeProperty13',
     'SomeProperty14',
     'SomeProperty15',
     'SomeProperty16',
     'SomeProperty17',
     'SomeProperty18',
     'SomeProperty19',
     'SomeProperty20',
     'SomeProperty21',
     'SomeProperty22',
     'SomeProperty23',
     'SomeProperty24',
     'SomeProperty25',
     'SomeProperty26',
     'SomeProperty27',
     'SomeProperty28',
     'SomeProperty29',
     'SomeProperty30',
     'SomeProperty31',
     'SomeProperty32',
     'SomeProperty33',
     'SomeProperty34',
     'SomeProperty35',
     'SomeProperty36',
     'SomeProperty37',
     'SomeProperty38',
     'SomeProperty39',
     'SomeProperty40'
    )

Everything is still fine.

In query window #1, select data from the City table for the USA records.

-- script 7 - select USA data
-- Connection #1
-- #7 Step
SELECT *
FROM   dbo.City AS c
WHERE  c.CountryID = 1

In query window #2, select data from the City table with India records.

-- script 8 - select India data
-- Connection #2
-- #8 Step
SELECT *
FROM   dbo.City AS c
WHERE  c.CountryID = 2

In the picture below we can see what happens after the step #8 is executed. We get a deadlock error.

You might say: “Hey, you created an index “IX_City_Not_Covering_Index” on the “CountryID” column and included the “CityName” column. So, to not have a deadlock just replace the “*” symbol in the SELECT queries with the “CityName” column in scripts 8 and 9. Yes, this will work in a perfect world, but when using an ORM it writes the SQL like “SELECT * FROM MyTable”. Actually, it never writes the “*”, but it picks up every column and that’s why my scripts 8 and 9 are written that way and that’s why we have the problem I am talking about. Of course, I said something interesting to tell our developers, but unfortunately they can’t change the way the SQL is created. It is a long term task they said. Long story…

Now let me solve the problem with a good problem explanation and a good solution.

Understanding and Solving the SQL Server Deadlock Issue

To solve the deadlock you just need to cluster the foreign key “FK_Country”. I will explain this in detail and how this solved this problem.

Why is the SQL Server deadlock created?

In order to understand the reason of the deadlock we need to take a look at what was locked and who was blocked.

Script 10 below will show us all the locks have been acquired in the database.

-- script 10 - show current locks
SELECT OBJECT_NAME(p.object_id) AS TableName,
       dtl.resource_type,
       dtl.resource_description,
       c.CityName AS Locked_Row,
       c.CityID AS Locked_Row_ID,
       dtl.request_mode,
       dtl.request_type,
       dtl.request_status,
       dtl.request_session_id,
       dtl.request_owner_type,
       dtl.request_owner_id
FROM   sys.dm_tran_locks AS dtl
LEFT JOIN sys.partitions AS p
    ON p.hobt_id = dtl.resource_associated_entity_id
LEFT JOIN dbo.City AS c WITH (NOLOCK)
ON %%lockres%% = RTRIM(dtl.resource_description)
WHERE  OBJECT_NAME(p.object_id) = 'City'
       AND c.CityName IS NOT NULL

Here is what step #5 above did.

In the picture above you can see that transaction #3138538 acquired an exclusive (‘X’) lock on a key in the “City” table. As you can also see we are talking about the city of New York. So, now we know that at least one row has been locked. The “X” lock will be held until the end of the transaction.

Now let’s have a look what happened in the database after step #6.

The picture above depicts a new exclusive lock that holds the city of Delhi.

Okay, both of our cities have been locked with an exclusive key lock and nobody can read them except a reader under the “READ UNCOMMITED” isolation level. By the way, our two transactions are acting under the “READ COMMITED” isolation level.

This is what we have after step #7.

In the picture above we can see that there is a request to read key 2 – city of Delhi. Step #7 is in scope of transaction #3138538 and now you can see that transaction #3138538 tries to read data inserted by an uncommitted transaction #3139303. So, if we try to execute step #8 it leads to an attempt of transaction #3139303 to read the data inserted by the uncommitted transaction #3138538 that is how the deadlock occurs.

Here is the picture how it was after step #8 was executed and before SQL Server detected the deadlock and chose a victim.

Here is the deadlock graph.

So, why were my transactions trying to read these other resources? The answer is very simple – because of a “bad” execution plan that has a “Clustered Index Scan” operator. Here is what the plan looks like.

To be precise, the scan tries to read all of the table’s data and when some data is locked with an exclusive lock the reader is blocked until the locking transaction commits or rollback. The same thing happens to any concurrent transaction with the same logic. It creates an infinite wait - and then a deadlock.

Why does SQL Server chose a clustered index scan just for this particular SQL (steps #7 and #8), this is because with this small amount of data the index “IX_City_Not_Covering_Index” is not selective enough and that’s why SQL Server chose to do the scan. Actually, it is a very good choice. Later in the tip I’ll make this index selective enough to show you that SQL Server will produce another good execution plan when there is more data.

Solve the SQL Server Deadlock with a Clustered Foreign Key

With the script below I change the foreign key to a clustered index and we will see how it solves the issue.

-- script 11 - create clustered foreign key
ALTER TABLE dbo.City DROP CONSTRAINT PK_City;
GO
 
CREATE CLUSTERED INDEX CIX_CountryID ON dbo.City(CountryID);
GO
 
ALTER TABLE dbo.City ADD CONSTRAINT PK_City PRIMARY KEY NONCLUSTERED(CityID) ;
GO

Also, as I am going to solve the issue it means there will be no blocking and shared locks will be released after a reader gets its data. To see the locks I will use the cool hint “HOLDLOCK”. So, let’s re-write scripts #7 and #8 and start as follows:

-- Connection #1
-- #7 Step
SELECT *
FROM   dbo.City AS c WITH(HOLDLOCK)
WHERE  c.CountryID = 1

-- Connection #2
-- #8 Step
SELECT *
FROM   dbo.City AS c WITH(HOLDLOCK)
WHERE  c.CountryID = 2

The most interesting thing what we are going to have at the end of our scenario is where we saw the deadlock.

Thus after #5 (inserting city of New York)

After step #6 we have this.

After step #7 we are blocked and the locks are shown in the picture below. It's worth noting that we have been blocked because of the hint “HOLDLOCK”. This hint is an equivalent of “SERIALIZABLE”. In turn, “SERIALIZABLE” forces SQL Server to acquire a Range-key lock from key 1 to 2. Without the “HOLDLOCK” there is no blocking, but this is the only way to demonstrate what happens. Please, keep in mind that without the “HOLDLOCK” hint step #7 will not try to acquire the Range-key lock, but will just use the lock it has acquired already – the “X” lock.

After running step #8 it returns the city of Delhi immediately, and the locks are shown below.

So, the deadlock has gone, but why? Let’s first take a look at step #8 execution plan as shown below.

Now we see a Clustered Index Seek operation and it seems this has saved us from the deadlock.

Now let’s analyze this.

After both cities are inserted, transaction #3154411 is awaiting for key 2, which has been locked by transaction #3154709. In the meantime, because of the seek operation transaction #3154709 reads only key 2 (it does not need key 1 as it happened with the scan), which this transaction is already holding.

Deadlock Does Not Occur When There is More Data

Do you remember I promised that index “IX_City_Not_Covering_Index” also solves the deadlock issue, once it is selective enough? So, let’s make this index selective and see if SQL Server will chose the index seek operation. If this happened we can avoid the deadlock. I will give you SQL to make the index selective, so the execution plan uses a seek. One thing to note is that there is no clustered index on the “CountryID” field as in the last experiment.

The following script adds 500 rows to the “City” table.

-- script 12 - add Dummy Cities 
DECLARE @i INT = 0
DECLARE @NewCountryID INT
WHILE @i < 5
BEGIN
    DECLARE @CountryName VARCHAR(100)
    SET @CountryName = 'SomeCountry' + CAST(@i AS VARCHAR)
    INSERT INTO dbo.Country
        (
            CountryName,
            PopulationSize
        )
    VALUES
        (@CountryName, -- CountryName - varchar(100)
         1000000
        )
    SET @NewCountryID = SCOPE_IDENTITY()
 
    INSERT INTO dbo.City
    SELECT TOP 100
         @NewCountryID, -- CountryID - int
         o.name,        -- CityName - varchar(100)
         1000000,       -- PopulationSize - int
         1000,          -- CountOfHospitals - int
         o2.name,       -- SomeProperty1 - varchar(100)
         o2.name,       -- SomeProperty2 - varchar(100)
         o2.name,       -- SomeProperty3 - varchar(100)
         o2.name,       -- SomeProperty4 - varchar(100)
         o2.name,       -- SomeProperty5 - varchar(100)
         o2.name,       -- SomeProperty6 - varchar(100)
         o2.name,       -- SomeProperty7 - varchar(100)
         o2.name,       -- SomeProperty8 - varchar(100)
         o2.name,       -- SomeProperty9 - varchar(100)
         o2.name,       -- SomeProperty10 - varchar(100)
         o2.name,       -- SomeProperty11 - varchar(100)
         o2.name,       -- SomeProperty12 - varchar(100)
         o2.name,       -- SomeProperty13 - varchar(100)
         o2.name,       -- SomeProperty14 - varchar(100)
         o2.name,       -- SomeProperty15 - varchar(100)
         o2.name,       -- SomeProperty16 - varchar(100)
         o2.name,       -- SomeProperty17 - varchar(100)
         o2.name,       -- SomeProperty18 - varchar(100)
         o2.name,       -- SomeProperty19 - varchar(100)
         o2.name,       -- SomeProperty20 - varchar(100)
         o2.name,       -- SomeProperty21 - varchar(100)
         o2.name,       -- SomeProperty22 - varchar(100)
         o2.name,       -- SomeProperty23 - varchar(100)
         o2.name,       -- SomeProperty24 - varchar(100)
         o2.name,       -- SomeProperty25 - varchar(100)
         o2.name,       -- SomeProperty26 - varchar(100)
         o2.name,       -- SomeProperty27 - varchar(100)
         o2.name,       -- SomeProperty28 - varchar(100)
         o2.name,       -- SomeProperty29 - varchar(100)
         o2.name,       -- SomeProperty30 - varchar(100)
         o2.name,       -- SomeProperty31 - varchar(100)
         o2.name,       -- SomeProperty32 - varchar(100)
         o2.name,       -- SomeProperty33 - varchar(100)
         o2.name,       -- SomeProperty34 - varchar(100)
         o2.name,       -- SomeProperty35 - varchar(100)
         o2.name,       -- SomeProperty36 - varchar(100)
         o2.name,       -- SomeProperty37 - varchar(100)
         o2.name,       -- SomeProperty38 - varchar(100)
         o2.name,       -- SomeProperty39 - varchar(100)
         o2.name        -- SomeProperty40 - varchar(100)
    FROM sys.objects AS o
    CROSS JOIN sys.objects AS o2
 
    SET @i += 1
END

These dummy cities create statistics that tells SQL Server that if a client wants to get only one city it is better to use a seek in the index for the row id and then make a lookup for the rest of data.

The execution plan for both steps 7 and 8 will now look like this.

What If a Table Has More Than One Foreign Key?

Yes, this is another issue we met which we solved. Please, let me know if you are interested in how we did it.

Next Steps

Read these related articles:
- Current locks – https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql
- Undocumented but very useful “%%lockres%%” – https://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/

About the author

Viacheslav Maliutin is a SQL Server Professional with 16+ years of experience in reporting, integration and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips