Lessons to Solve SQL Server Deadlocks with Foreign Keys

By: Viacheslav Maliutin | Updated: 2017-12-12 | Comments (3) | Related: > Transactions

Problem

This is the second part of my tip series about how to solve deadlocks in a SQL Server database where there is no data and this database is under workload testing. I recommend reading the first part of this series to understand the details.

The problem we are going to solve is the same as in part 1; there are deadlocks in a SQL Server database which has no data yet for testing purposes, but we are not experiencing deadlocks in the production version of the database.

To give some additional background information, our application uses ORM (Object Relation Mapping) letting the programmer be efficient, especially when he/she is not familiar with SQL Server. 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 the values materialized in the calculated fields.

In part 1, I showed how SQL Server produces an execution plan with a seek operation when the ORM selects a row by a foreign key. What I suggested is to create a clustered index on the foreign key column and it solved the issue. But here is a question – what if the table has more foreign keys than one? How do we resolve this issue?

Solution

Recreating the SQL Server Deadlock

To demonstrate how the deadlock occurs I am going to have two separate active transactions opened in two connection windows.

We start with creating of three tables and three indexes. The script below does it.

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 Person
    (
        PersonID INT NOT NULL IDENTITY(1, 1)
            CONSTRAINT PK_Person PRIMARY KEY
       ,FirstName VARCHAR(100)
       ,LastName VARCHAR(100)
    )
GO
CREATE TABLE City
    (
        CityID INT NOT NULL IDENTITY(1, 1)
            CONSTRAINT PK_City PRIMARY KEY NONCLUSTERED
       ,CountryID INT NOT NULL
       ,MayorOfTheCityID 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)
       ,CONSTRAINT FK_Person
            FOREIGN KEY (MayorOfTheCityID)
            REFERENCES dbo.Person (PersonID)
    )
GO

CREATE INDEX IX_City_Not_Covering_Index 
    ON dbo.City (CountryID) 
    INCLUDE (CityName) 
GO 
CREATE CLUSTERED INDEX CIX_CountryID
    ON dbo.City (CountryID);
GO
CREATE INDEX IX_Person_Not_Covering_Index
   
    ON dbo.City (MayorOfTheCityID) 
    INCLUDE (CityName); 
GO

Please, don’t be surprised that the “PK_City” is a non-clustered index, and the “CIX_CountryID” is a clustered one. That’s why I recommended reading the first tip in the series. To be short this design allows us three things:

Avoid a deadlock scenario when the table “City” is accessed over the “FK_Country” foreign key (please see part 1)
It makes a query obtaining cities by a certain country very fast
Eliminates the “Lookup” operator when you want more columns than the “IX_City_Not_Covering_Index” can give you.

As you can see in script #1 our second foreign key “FK_Person” has been indexed. But the problem is this: it’s used only when statistics tells SQL Server the index is selective enough. The “Yes” answer comes only when there is good amount of data. Otherwise the index is not in use. We will see it later.

As usual, to demonstrate a deadlock we need two connections to be opened. In each SQL Server Management Studio 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.

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 and a record for John Parker both with an ID = 1.

-- Connection #1
-- #1 Step 
BEGIN TRAN

INSERT INTO dbo.Country
    (
        CountryName,
        PopulationSize
    )
VALUES
    ('USA', 323100000)

GO

INSERT INTO dbo.Person
    (
        FirstName
       ,LastName
    )
VALUES
    ('John'   -- FirstName - varchar(100)
    ,'Parker' -- LastName - varchar(100)
    )

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 and a record for Vikram Gandhi both with an ID = 2.

-- Connection #2
-- #2 Step 
BEGIN TRAN

INSERT INTO dbo.Country
    (
        CountryName,
        PopulationSize
    )
VALUES
    ('India', 1324000000 /*Yes, I am impressed either*/)

GO

INSERT INTO dbo.Person
    (
        FirstName
       ,LastName
    )
VALUES
    ('Virkam' -- FirstName - varchar(100)
    ,'Gandhi' -- LastName - varchar(100)
    )

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

-- Connection #1
-- #3 Step
SELECT *
FROM   dbo.Country AS c
WHERE  c.CountryID = 1 
GO 
  
SELECT * 
FROM   dbo.Person AS p 
WHERE  p.PersonID = 1

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

-- Connection #2
-- #4 Step
SELECT *
FROM   dbo.Country AS c
WHERE  c.CountryID = 2 
GO 
   
SELECT * 
FROM   dbo.Person AS p 
WHERE  p.PersonID = 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.

-- Connection #1
-- #5 Step
INSERT INTO dbo.City
VALUES
    (1,          -- CountryID - int
     1,          --MayorOfTheCity - 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.

-- Connection #2
-- #6 Step
INSERT INTO dbo.City
VALUES
    (2,        -- CountryID - int
     2,          --MayorOfTheCity - 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.

-- Connection #1
-- #7 Step
SELECT *
FROM   dbo.City AS c 
WHERE  c.MayorOfTheCityID = 1

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

-- Connection #2
-- #8 Step
   
SELECT * 
FROM   dbo.City AS c 
WHERE  c.MayorOfTheCityID = 2

In the image 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_Person_Not_Covering_Index” on the “MayorOfTheCityID” 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 to our developers, but unfortunately they can’t change the way the SQL is created. It is a long term task they said.

Now let me solve the problem with a good explanation, but with a controversial solution.

Understanding and Solving the SQL Server Deadlock Issue

To solve the deadlock you need to force SQL Server use an index by writing a hint. I will explain how to do it without changing application code. One note, I have to say is that the ORM, we use, is not able to add hints to the SQL it writes.

Why Is the SQL Server Deadlock Created

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

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

-- script 10

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 #374244 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 the key 2 – city of Delhi. Step #7 is in scope of transaction #374244 and now you can see that transaction #374244 tries to read data inserted by an uncommitted transaction #374345. So, if we try to execute step #8 it leads to an attempt of transaction #374345 to read the data inserted by the uncommitted transaction #374244 that is how the deadlock occurs.

Here is the picture of 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_Person_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.

Solving the SQL Server Deadlock with Controversial Trick

With the script below I rename the actual table “City”, to hide it from its consumers, and create a view with name “City” to let consumers work with a fake table “City”. As you can see, now “City” forces SQL Server to choose the “Index Seek” always because of the hint “FORCESEEK”.

EXEC sys.sp_rename @objname = N'dbo.City'       -- nvarchar(1035)
                  ,@newname = 'City$Hidden' -- sysname
                  ,@objtype = 'object'          -- varchar(13)
GO
 
CREATE VIEW dbo.City
AS
SELECT *
FROM   dbo.City$Hidden AS dch WITH (FORCESEEK)
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.MayorOfTheCityID = 1
-- Connection #2
-- #8 Step
SELECT *
FROM   dbo.City AS c WITH(HOLDLOCK)
WHERE  c.MayorOfTheCityID = 2

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

As I renamed the “City” table I had to fix the script returning locking information as shown below:

SELECT OBJECT_NAME(p.object_id) AS TableName,
   i.name AS IndexName,
       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 sys.indexes AS i ON i.object_id = p.object_id AND p.index_id = i.index_id
LEFT JOIN dbo.[City$Hidden] AS c WITH (NOLOCK)
ON %%lockres%% = RTRIM(dtl.resource_description)
WHERE  OBJECT_NAME(p.object_id) = 'City$Hidden'
AND resource_type = 'KEY'
ORDER BY dtl.request_owner_id

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 (please see key “4fab0ff3e3c7” in “IX_Person_Not_Covering_Index”). It is my understanding that the first object to be used is “IX_Person_Not_Covering_Index”, and that’s why the “X” lock was converted to “RangeX-X” in that object. 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 the code 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 the step #8 execution plan as shown below:

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

Now let’s analyze this.

After both cities are inserted, transaction #729615 is awaiting for key “29a347ff72ce”, which has been locked by transaction #729627. In the meantime, because of the seek operation transaction #729627 reads only key 2 (that is key “29a347ff72ce”) which this transaction is already holding. Also transaction #729627 does not need key 1 (that is key “4fab0ff3e3c7”) as it happened with the scan.

No Deadlock after Some Data Is Inserted

Do you remember I promised that index “IX_Person_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 T-SQL code to make the index selective, so the execution plan uses a seek operation. One thing to note is that there is no hidden table, no renaming, and no view as in the last experiment.

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

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 = IDENT_CURRENT('dbo.Country')

    INSERT INTO dbo.Person
        (
            FirstName
           ,LastName
        )
    VALUES
        ('Someone'  -- FirstName - varchar(100)
        ,'Verywise' -- LastName - varchar(100)
        )
    DECLARE @Someone INT = IDENT_CURRENT('dbo.Person')

    INSERT INTO dbo.City
    SELECT TOP 100
         @NewCountryID -- CountryID - int
        ,@Someone      --MayorOfTheCity - 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 operation 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.

My Opinion

To be honest, I recommend not living with the solution I provided in this tip and finding a better coding approach. Also, please take into consideration the following when we use hints:

This error about the Query Processor happens because of the “FORCESEEK” hint and it is a totally fair exception. How can SQL Server produce a seek operation plan when there is nothing to seek? It was a rhetorical question, of course.

Next Steps

Check out these additional resources:
- 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 thing “%%lockres%%” – https://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/
- SQL Server Locking and Blocking Tips

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

Article Last Updated: 2017-12-12

Lessons to Solve SQL Server Deadlocks with Foreign Keys - Part 2