Deadlock in SQL with Example

Deadlock in SQL with Example

A Deadlock occur when processes blocks each other .for Eg 1st process is blocking 2nd  process 2nd process is blocking 3rd process and the last 3rd  process is blocking the 1st Process.
In this case the sql terminates one of the Session(Process with least work) and terminate the current session with below Error.

Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In order to Demonstrate Deadlock in sql i will use two tables

--First Table 

CREATE TABLE [dbo].[Employee_Deadlock](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)

--Second Table

CREATE TABLE [dbo].[Employee_Deadlock1](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)

Open a New a tab in sql editor -Tab 1

Run the below query

--Updating  first table without committing the Transactions
Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'


Now open a new sql edition New query tab 2 

Run the below query

--Updating second table without committing the Transactions
Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'



Now go to first Tab run the Below query

-- Extracting Data from table 2 which has not been committed in tab 1
select * from [Employee_Deadlock1]
where empname='amit'
commit Tran


After Running above query you will see that it will not show any result as Process 2 is already in Updating Process as we have not committed the Changes
This  arise the Blocking Situation.

Now Run the Below Query in tab 2

-- Extracting Data from Deadlocktable 1 which has not been committed in tab 2
select * from [Employee_Deadlock]
where empname='amit'
commit Tran


This will throw an Error Message

Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This mean SQL server identified the Deadlock and terminated one of the blocking process with the above error.

--commit the Update Transaction in both tab to remove blocking

Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'
commit tran


Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'
commit tran

NOTE:DMV(Dynamic Management views) are there to track down deadlocks and  blocking process.