Today we are going to explore when two resources hold locks that the other need which causes no one to execute = deadlock It could be two sessions or one session that has parallel executing threads. both need resources that the other one has locked

• then we will have two transactions A is trying to update 1 and 2, and B is trying to update 2 and 1so the first one will definitely acquire X lock on 1, and 2 will definitely acquire X lock 2
• , but A will try to acquire X on 2 but he will fail and B will try to acquire X lock on 1and it will fail
• this is what you call a deadlock when one statement needs the other’s resource and the second statement needs the first one’s resource
• SQL Server’s Deadlock Monitor will recognize that and will kill one of them ( the easier one to rollback in logspace ) and the other one will be executed

let's look at an example we have a table with an ID, Col1, and Col2 let’s populate it

-- Drop the table if it exists
IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL
    DROP TABLE "dbo"."MyTable";
GO

-- Create the table with ID, Col1, and Col2
CREATE TABLE "dbo"."MyTable"
(
    "ID" INT NOT NULL IDENTITY(1, 1),
    "Col1" NVARCHAR(50) NOT NULL,
    "Col2" NVARCHAR(50) NULL,
    CONSTRAINT "PK_MyTable" PRIMARY KEY CLUSTERED ("ID")
);
GO

-- Insert sample data into the table
DECLARE @i INT = 1;
DECLARE @Col1 NVARCHAR(50);
DECLARE @Col2 NVARCHAR(50);

WHILE @i <= 20000
BEGIN
    SET @Col1 = 'Co' + CAST(@i AS NVARCHAR(50)) + '1';
    SET @Col2 = 'SampleData' + CAST(@i AS NVARCHAR(50));

    INSERT INTO "dbo"."MyTable" ("Col1", "Col2")
    VALUES (@Col1, @Col2);

    SET @i = @i + 1;
END;

first

BEGIN TRANSACTION;
-- Session A updates the row with ID 1
UPDATE "dbo"."MyTable"
SET "Col1" = 'SessionA1'
WHERE "ID" = 1;
--- don't COMMIT

then in another session

BEGIN TRANSACTION;
-- Session B updates the row with ID 2
UPDATE "dbo"."MyTable"
SET "Col1" = 'SessionB2'
WHERE "ID" = 2;
--don't COMMIT

then in the first session

-- Session A attempts to update the row with ID 2
UPDATE "dbo"."MyTable"
SET "Col1" = 'SessionA2'
WHERE "ID" = 2;

then in the second session

UPDATE "dbo"."MyTable"
SET "Col1" = 'SessionB1'
WHERE "ID" = 1;

and one of them successfully executes and the other one shows up with this  

Lack Of An Index As A Cause Of Deadlock

• our isolation level is READ COMMITTED( this stuff is not happening in RCSI, IN SI, or IN NOLOCK)
• we have a clustered index we created on ID
• we have two sessions with two statements
• the A session updates based on ID it sets Col1 to ‘WHO’ where ID = ‘1’
• the B session updates based on ID it set Col1 to ‘ME’ where ID =’2’
• both don’t COMMIT
• Then the first one tries to read Col2 where Col1 = ‘Col155’
• and the second one tries to read Col2 where Col1=’Col166’
• both deadlock
• one is a victim
• why?
• Clustered index scan to see where the Col1 matches these values and in order to do that they had to acquire an S lock on a lot of rows one of these rows is the one that is being held by X locks in this case ID 1 AND 2
• non of them can get rid of the X lock
• They deadlock
• Deadlock Monitor Kills one of them

let's show the code session A first

BEGIN TRANSACTION;

-- Session A updates the row with ID 1
UPDATE "dbo"."MyTable"
SET "Col1" = 'WHO'
WHERE "ID" = 1;

Then in Session B

BEGIN TRANSACTION;

-- Session B updates the row with ID 2
UPDATE "dbo"."MyTable"
SET "Col1" = 'ME'
WHERE "ID" = 2;
-- don't COMMIT 

then back to A

SELECT "Col2"
FROM "dbo"."MyTable"
WHERE "Col1" = 'Col155';

then again to B

SELECT "Col2"
FROM "dbo"."MyTable"
WHERE "Col1" = 'Col166';

and this will pop up in one of them now commit the one that executed then let’s create an index on Col1

CREATE NONCLUSTERED INDEX IX_MyTable_Col1 ON dbo.MyTable (Col1);

then do the same steps and no deadlock would happen because the rows where reached immediately through the index. so here if the index is created no deadlocking occurs

Multiple Indexes Clashing With Updates=Updating The Same Row Using Different Indexes

So what we have here is a row that is included in two indexes the clustered (which has everything) and a non-clustered index that has Col1 but not Col2 so first we have 3 transactions :

• in the first session that updates Col2 through the clustered
• only X locks on the clustered
• then we will view the same row through the non-clustered, since the non-clustered has no locks on it we will have no problem acquiring it on the non-clustered but not on clustered
• then we will update the same non-clustered through the clustered in the first session
• since our session has the X locks on the clustered we will have no problem acquiring them
• but when want to update the non-clustered we will be blocked by the S locks in the second session
• and the first session blocked the second by the X locks on the clustered
• so now we are deadlocking

let’s prove it in code let’s show the code and the execution plans before running them concurrently then we have 3 trans one that updates Col2 through the Clustered index the code and the execution plan

BEGIN TRAN 
UPDATE MyTable
SET Col2 = 'new_stuff'
WHERE ID = 7;
waitfor delay'00:00:03';

and the execution plan only on Clustered indexes this will acquire X locks on Clustered index rows not the non-clustered, this is the first session then in another session one that selects through Col2 through the non-clustered index and the code is

USE ContosoRetailDW
BEGIN TRAN 
SELECT Col2
from MyTable
WHERE Col1='Co71';

this acquires S locks on the non-clustered index and the clustered on Col2 so and one that updates through the Clustered index the Col2

update MyTable
set Col1 ='new_Col1'
where ID = 7

this acquires locks on the clustered and the non-clustered this will run in the first session too so you need to acquire locks on both now if you run each separately you will have no problem but if you do it in the order we mentioned you will have deadlocking through updating the same row from different indexes. so you run the first update then you run the select in another session and then you run the second update through the first session and one of the processes will be automatically rolled back, hence deadlock. and we are done for today.    

---

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners by Dmitri Korotkevitch Pro SQL Server Internals by Dmitri Korotkevitch SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Technique by Dmitri Korotkevitch SQL Server Concurrency by Kalen Delaney Microsoft SQL Server 2012 Internals by Kalen Delaney