{"id":270,"date":"2013-02-20T11:26:31","date_gmt":"2013-02-20T11:26:31","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2013\/02\/20\/deadlock-internals-with-real-time-scenario-explanation-and-resolution\/"},"modified":"2013-02-20T11:26:31","modified_gmt":"2013-02-20T11:26:31","slug":"deadlock-internals-with-real-time-scenario-explanation-and-resolution","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2013\/02\/20\/deadlock-internals-with-real-time-scenario-explanation-and-resolution\/","title":{"rendered":"Deadlock Internals with Real Time Scenario – Explanation and Resolution"},"content":{"rendered":"

What is deadlock?<\/strong>
\nA deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.
\nRefer: http:\/\/en.wikipedia.org\/wiki\/Deadlock<\/a><\/p>\n

Scenario:<\/strong>
\nData Preparation Script: (Script-a)<\/p>\n

[sql]
\n–Create Sample Database
\nCREATE DATABASE SampleDeadLockDatabase
\nGO<\/p>\n

USE SampleDeadLockDatabase
\nGO<\/p>\n

–Create Sample Tables
\nCREATE TABLE SampleTable1(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)
\nGO<\/p>\n

CREATE TABLE SampleTable2(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)
\nGO<\/p>\n

–Populate Sample tables
\nINSERT INTO SampleTable1(VALUE)
\nSELECT NEWID()
\nGO 10<\/p>\n

INSERT INTO SampleTable2(VALUE)
\nSELECT NEWID()
\nGO 10<\/p>\n

–Retreive data for verification
\nSELECT * FROM SampleTable1
\nSELECT * FROM SampleTable2
\n[\/sql]<\/p>\n

Transaction1 Script: (Script-b)<\/em><\/p>\n

[sql]
\nSelect @@SPID — Note down this value here (68)
\n——————Transaction1——————-
\n–Step1 Begins–
\nUSE SampleDeadLockDatabase
\nGO
\nBEGIN TRAN<\/p>\n

UPDATE SampleTable1
\nSET VALUE = ‘1DF1EBE1-322E-4698-A5BD-2E2C6D85AEB6’
\nWHERE ID=1
\n–Step1 Ends–<\/p>\n

—RUN Step2 in Transaction2<\/p>\n

–Step3 Begins–
\nSELECT * FROM SampleTable2 WHERE ID=4<\/p>\n

ROLLBACK TRAN
\n–Step3 Ends–
\n——————Transaction1——————-<\/p>\n

[\/sql]<\/p>\n

Transaction2 Script: (Script-c)<\/em><\/p>\n

[sql]
\nSelect @@SPID — Note down this value here — 65
\n——————Transaction2——————-
\n–Step2 Begins–
\nUSE SampleDeadLockDatabase
\nGO
\nBEGIN TRAN<\/p>\n

UPDATE SampleTable2
\nSET VALUE = ‘23154CCE-0919-4D3A-92EB-E6B4062F8715’
\nWHERE ID=3
\n–Step2 Ends–<\/p>\n

–Step4 Begins–
\nSELECT * FROM SampleTable1 WHERE ID=5<\/p>\n

ROLLBACK TRAN
\n–Step4 Ends–
\n——————Transaction2——————-
\n[\/sql]<\/p>\n

Data Preparation:<\/strong>
\nIn SQL Server Management Studio, Click on new query
\nPaste the Data Preparation Script, and execute it.<\/p>\n

Dead Lock Scenario Creation:<\/strong>
\nPaste the two code snippets transaction1 and transaction 2 in separate query windows.
\nIn each of them highlight the code (SELECT @@SPID) and run it to note down the SPID
\nNow run the Step1 in transaction1
\nThis step updates SampleTable1 with a new value for id 1
\nNow run the Step2 in transaction2
\nThis step updates SampleTable2 with a new value for id 3
\nNow run the Step3 in transaction1
\nThis step selects the values of SampleTable2 with id 4
\nYou will notice that this step3 is still executing. Why? We will analyze this later in the post.<\/em>
\nNow run the Step4 in transaction2
\nThis step selects the values of SampleTable1 with id 5
\nYou will notice that this step executes for few seconds and completes. Also one of the session (Transaction1 or Transaction2 will be deadlocked.) with the errormessage <\/em><\/p>\n


\nMsg 1205, Level 13, State 45, Line 1
\nTransaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
\n<\/font><\/p>\n

Analysis:<\/strong>
\nWhy Step3 was waiting (Blocked?)
\nSo now let us replicate the scenario again, by running the above steps1 and 2.
\nIn the new query window (again a new Query window \ud83d\ude42 ) Run the below script to understand what locks are placed on the tables pages and rows.<\/p>\n

Script-d<\/em>
\n[sql]
\n–Update this id with the @@spid of transaction1
\nSELECT resource_type,
\nresource_description,
\nresource_associated_entity_id,
\nrequest_mode,
\nrequest_type,
\nrequest_status FROM sys.dm_tran_locks
\nwhere request_session_id = 68<\/p>\n

–Update this id with the @@spid of transaction2
\nSELECT resource_type,
\nresource_description,
\nresource_associated_entity_id,
\nrequest_mode,
\nrequest_type,
\nrequest_status FROM sys.dm_tran_locks
\nwhere request_session_id = 65<\/p>\n

–Fetch the partition_id of the table
\nselect p.partition_id,t.name,t.object_id From sys.partitions p join sys.tables t
\non t.object_id = p.object_id
\nwhere t.name in (‘SampleTable1′,’SampleTable2’)
\n[\/sql]<\/p>\n

Update the values above with the respective @@spud\u2019s that we noted down earlier. (SPID\u2019s of the respective queries)
\nOutput:<\/em><\/p>\n

\"Table<\/a><\/p>\n

When you execute this you can find that each session has created exclusive lock (X) on the row level and intent exclusive (IX) locks on the page and table level.
\nLet\u2019s find what exactly those rows are.
\nCopy the resource_description values for resource_type RID to the below script and run it.<\/p>\n

Script-e:<\/em>
\n[sql]
\nselect * From SampleTable1 (nolock) where %%lockres%% = ‘1:154:0’
\nselect * From SampleTable2 (nolock) where %%lockres%% = ‘1:156:2’
\n[\/sql]<\/p>\n

This would output the rows that we updated in respective transactions.<\/p>\n

\"Lockres<\/a><\/p>\n

Now run the step3 in transaction1, and run the script-d pasted above.
\nOutput:<\/em>
\n\"Shared<\/a><\/p>\n

You will notice that transaction1 now has the new lock for RID 1:156:2 which is in waiting state.
\nIt\u2019s waiting for an exclusive lock to be released on a row on SampleTable2
\nIn order to find out the row for which transaction 1 is waiting copy the resource_description values to the below script and run it.
\nScript-f:<\/em>
\n[sql]
\nselect * From SampleTable2 (nolock) where %%lockres%% = ‘1:156:2’
\n[\/sql]<\/p>\n

\"Shared<\/a><\/p>\n

So transaction1 is waiting for exclusive lock on row ID 3 to be released.
\nBut in no way we are referring to rowid3 of transaction1., we were actually referring to row ID 4.<\/p>\n

Why is SQL Server waiting for this row?<\/strong><\/p>\n

In order to understand this stop the step3 in transaction1 (it\u2019s still getting executed) , select step3 code and click on Display estimated execution plan button ( or Query \u2013 Display estimated execution plan button) (or Ctrl + L )
\nYou will find a table scans in the execution plan.<\/p>\n

\"Waiting<\/a><\/p>\n

So this table scan is forcing SQL Server to actually scan all the rows in the table(for ID=4) , as the ID=3 is blocked by transaction 2 it is waiting for transaction 2 to actually complete in order to take shared lock on the row and compare its value with 4.<\/p>\n

Similarly when step4 is executed it is blocked by transaction 1, as step 4 has to do a table scan on the table SampleTable1 for ID = 5, but the transaction 1 has exclusively locked the row ID = 1.
\nSo,
\nTransaction 1 is blocked by Transaction 2
\nAND
\nTransaction 2 is blocked by Transaction 1
\nThis causes deadlock situation.
\nAs both transactions would stay for infinite time blocking each other.
\nSo, one of the transaction that is easier to rollback is terminated by SQL Server giving deadlock error: 1205<\/p>\n

Resolutions:<\/strong>
\n1. Change to Snapshot Isolation.<\/strong>
\n[sql]
\nALTER DATABASE SampleDeadLockDatabase
\nSET READ_COMMITTED_SNAPSHOT ON
\n[\/sql]<\/p>\n

Run the above query after disconnecting all the queries connected to SampleDeadLockDatabase
\nNow if you run the above Dead Lock Scenario steps you will notice that the deadlock is not occurring now.<\/p>\n

2.Create index on the table so that there will be alternates for Table Scan.<\/strong>
\nBefore testing the below approach remove snapshot isolation<\/p>\n

[sql]
\nALTER DATABASE SampleDeadLockDatabase
\nSET READ_COMMITTED_SNAPSHOT OFF
\n[\/sql]<\/p>\n

Script to Create clustered indexes on tables.
\n[sql]
\nALTER TABLE SampleTable1
\nADD CONSTRAINT SampleTable1_PK PRIMARY KEY (ID)
\nGO<\/p>\n

ALTER TABLE SampleTable2
\nADD CONSTRAINT SampleTable2_PK PRIMARY KEY (ID)
\n[\/sql]<\/p>\n

Now if you run the above Dead Lock Scenario steps you will notice that the deadlock is not occurring now. But you can reproduce dead lock if you force SQL Server to do an index \/table scan instead of index seek. Using WITH (INDEX (0)) hint in Step 3 and Step 4.<\/p>\n

[sql]
\n–Step3 Begins–
\nSELECT * FROM SampleTable2 WITH (INDEX(0)) WHERE ID=4<\/p>\n

ROLLBACK TRAN
\n–Step3 Ends\u2014<\/p>\n

–Step4 Begins–
\nSELECT * FROM SampleTable1 WITH (INDEX(0)) WHERE ID=3<\/p>\n

ROLLBACK TRAN
\n–Step4 Ends–
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"

What is deadlock? A deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does. Refer: http:\/\/en.wikipedia.org\/wiki\/Deadlock Scenario: Data Preparation Script: (Script-a) [sql] –Create Sample Database CREATE DATABASE SampleDeadLockDatabase GO USE SampleDeadLockDatabase GO –Create Sample Tables CREATE TABLE SampleTable1(ID INT IDENTITY(1,1),VALUE UNIQUEIDENTIFIER)…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1946,2166,3],"tags":[],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/270"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=270"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/270\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}