In order to understand deadlocks, let us first create tables and have some sample data.
/* Tables and data - Start */
IF OBJECT_ID('Order') IS NOT NULL
BEGIN
DROP TABLE [Order]
END
IF OBJECT_ID('Customer') IS NOT NULL
BEGIN
DROP TABLE Customer
END
CREATE TABLE Customer
(
ID int IDENTITY(1,1) CONSTRAINT PK_cid PRIMARY KEY ,
Name varchar(20)
)
CREATE TABLE [Order]
(
ID int IDENTITY(1,1) CONSTRAINT PK_oid PRIMARY KEY ,
CustomerId int,
OrderDate datetime
)
ALTER TABLE [Order]
ADD FOREIGN KEY (CustomerID)
REFERENCES Customer(ID)
INSERT INTO Customer (Name)
SELECT 'xxx'
UNION ALL
SELECT 'yyy'
UNION ALL
SELECT 'zzz'
INSERT INTO [Order] (CustomerID, OrderDate)
SELECT 1, GetDate()
UNION ALL
SELECT 2, GetDate()
UNION ALL
SELECT 3, GetDate()
/* Tables and data - End*/
Open Sql Profiler and use the “TSQL_Locks” template.
In the “Event selection” tab, make sure you have “Deadlock Graph” selected and the click “Run”.
In SSMS, open two new query windows and execute the below batches in those two separate windows at the same time (Hit F5 in one window, switch to the another and hit F5).
Batch 1
/*Transaction A updates record A in table A - Waits and then updates record B in table B*/ BEGIN TRANSACTION UPDATE Customer SET Name = 'aaa' WHERE ID=1 WAITFOR DELAY '00:00:10' -- Waiting for 10 secs UPDATE [Order] SET CustomerId = 1 WHERE ID = 2 COMMIT TRANSACTION
Batch 2
/*Transaction B updates record B in table B - Waits and then updates the updates record A in table A */ -- This causes deadlock BEGIN TRANSACTION UPDATE [Order] SET OrderDate = GetDate() WHERE Id = 2 WAITFOR DELAY '00:00:10' -- Waiting for 10 secs UPDATE Customer SET Name = 'bbb' WHERE Id=1 COMMIT TRANSACTION
You will notice that the batch 1 transaction took affect but because batch 2 couldn’t as that was identified as deadlock victim.
Look at the profiler now.
Notice that the Batch 2 didn’t get committed (See the big blue cross on the left circle and the tool tip on it in the above screenshot from the profiler?). However the Batch 1 did go through and took effect (see below).
Note that If the above batches (separate transactions) were updating two different records at the same time, there wont be any deadlocks.
/* Transaction A updates record A in table A waits and the updates record B in table B*/ BEGIN TRANSACTION UPDATE Customer SET Name = 'aaa' WHERE ID=2 PRINT 'Customer updated...' WAITFOR DELAY '00:00:10' -- Waiting for 10 secs UPDATE [Order] SET CustomerId = 1 WHERE ID = 2 PRINT 'Order updated...' COMMIT TRANSACTION
/* Should be executed on a separate query window Transaction B updates record C in table A waits and the updates record C in table B */ BEGIN TRANSACTION UPDATE [Order] SET OrderDate = GetDate() WHERE Id = 3 PRINT 'Order updated' WAITFOR DELAY '00:00:10' -- Waiting for 5 secs UPDATE Customer SET Name = 'bbb' WHERE Id=3 PRINT 'Customer updated' COMMIT TRANSACTION
