Example of Deadlock | SQL Server Deadlock Script

Leave a comment (0) Go to comments

IS there a way I can stimulate a deadlock in SQL Server, so that I can use that as an example of deadlock

Let’s take an example, where we will result / cause a dead lock to SQL Serve?

To stimulate the Deadlock, let’s create two tables which we are going to use as an example of Deadlock

STEP 1 – Create Sample Tables which we will use Deadlock Example

-- Sample Table, which will track individual Orders entered by 
-- Various Sales Persons
create table Orders
(
OrderID int,
OrderDate datetime,
Amount money,
SalesPersonID int
)
go
-- Sample Table, which will track all individual Sales Persons details
create table SalesPerson
(
SalesPersonID int,
Name varchar(100),
Region varchar(100)
);
-- Insert Date in SalesPerson
insert SalesPerson Values (1,'Dave Lawlor', 'California');
insert SalesPerson Values (2,'Mark F', 'Atlanta');
insert SalesPerson Values (3,'Vivek Pundit', 'India');
insert SalesPerson Values (4,'Chi Li Hu', 'China');
insert SalesPerson Values (5,'Mat Ross', 'London');

-- Insert Date in Orders Table
insert Orders values (101,'2011-01-01', 343,1)
insert Orders values (102,'2011-02-01', 35,2)
insert Orders values (103,'2011-03-01', 33,3)
insert Orders values (104,'2011-04-01', 445,1)
insert Orders values (105,'2011-05-01', 865,3)
insert Orders values (106,'2011-06-01', 23,5)
insert Orders values (107,'2011-07-01', 673,1)
insert Orders values (108,'2011-01-01', 343,1)
insert Orders values (109,'2011-02-01', 135,2)
insert Orders values (110,'2011-03-01', 373,3)
-- select Table Data
select * from Orders;
select * from SalesPerson;

STEP 2 – Open TWO new Query Windows where we will run 4 update statements


1 – Execute the following Query on Query Windows 1

-- Step 1 update a Order Table and update the Sales Person Id for Order 108
-- Session 1 (Query Window 1) -- Statement 1

BEGIN TRAN
update Orders set SalesPersonID=4 where OrderID = 108;

2 – Execute the following Query on Query Windows 2

-- Step 2 update Sales Person Table and update the Sales Person Name for SalesPersonID 4
-- Session 2 (Query Window 2) -- Statement 2

BEGIN TRAN
update SalesPerson set Name='Li Hu Chu' where SalesPersonID = 4;

3 – Execute the following Query on Query Windows 1

-- Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4
-- Session 1 (Query Window 1) -- Statement 3

BEGIN TRAN
update SalesPerson set Region='Beijing' where SalesPersonID = 4;

4 – Execute the following Query on Query Windows 2

-- Step 4 update Orders Table and update the Sales OrderDate for Order 108
-- Session 2 (Query Window 2) -- Statement 4

BEGIN TRAN
update Orders set OrderDate='2011-11-11 11:11:11.000' where OrderID = 108;

OUTPUT at STEP 4

Once you execute the 4th statement, you will get the following error message

Msg 1205, Level 13, State 45, Line 5

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

Example of Deadlock | SQL Server Deadlock Script sql server deadlock trace flag sql deadlock trace deadlock trace sql 2012 deadlock trace sql 2005 deadlock trace deadlock sql 2008 deadlock sql 2005

NOTE : Currently this DEAD LOCK Error is displayed at session level, I mean to say either at query window or at user front end. But this information in not get logged either in SQL Server Error Logs or Event Viewer by default.

As a preventive. we should keep an active eye on deadlocks and take necessary actions to avoid DEADLOCK issues.

But if these events are not getting logged anywhere, there how we are going to check these?, So in next topic I am going to discuss, how to track DEADLOCK information, so that these can be analyzed.

Please check My previous article, "How to Keep track of DEADLOCK in SQL Server", where we learn how to trace DEADLOCK in SQL Server

EOF - Example of Deadlock | SQL Server Deadlock Script, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.