How to simulate DEADLOCK on SQL Server?

Question

I am trying to simulate a deadlock on SQL Server.

_|worker_id|salary|
1|1        |100   |
2|2        |300   |

Transaction 1 completed in 5 seconds.

/* TRANSACTION 1*/
Use dbmcw;

DECLARE @sal1 INT, @sal2 int;

BEGIN TRAN;

SELECT @sal1 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK) 
WHERE worker_id = 1;

WAITFOR DELAY '00:00:05.000';

SELECT @sal2 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 2;

COMMIT TRAN;

Transaction 2 finished in 3 seconds.

/* TRANSACTION 2*/
Use dbmcw;

DECLARE @sal1 INT, @sal2 int;

BEGIN TRAN;

SELECT @sal2 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 2;

SELECT @sal1 = salary
FROM dbo.deadlock_demonstration WITH(UPDLOCK)
WHERE worker_id = 1;

COMMIT TRAN;

SQL Server is not giving any error. Deadlock did not occur. What should I change in order to simulate a deadlock?

My coursework on database management requires to demonstrate deadlock on SQL SERVER. So I need for deadlock — user3388473, Apr 02 '14 at 23:48
@user3388473 - which text book are you using for this course ? thanks. — Erran Morad, Apr 02 '14 at 23:49
Connolly T., C. Begg Database Systems A Practical Approach to Design, Implementation and Management — user3388473, Apr 02 '14 at 23:49
http://technet.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx this is a pretty good resource.. maybe use an exclusive lock — clancer, Apr 02 '14 at 23:49
@clancer, thanks for link. But there is no code there. I have alse seen this link: http://technet.microsoft.com/en-us/library/aa213040(v=sql.80).aspx. Could you suggest what I am doing wrong to get DEADLOCK in above code? — user3388473, Apr 02 '14 at 23:52

score 76 · Accepted Answer · edited Dec 28 '17 at 06:19

You can create a deadlock by using the steps shown below. First, create the global temp tables with sample data.

--Two global temp tables with sample data for demo purposes.
CREATE TABLE ##Employees (
    EmpId INT IDENTITY,
    EmpName VARCHAR(16),
    Phone VARCHAR(16)
)
GO

INSERT INTO ##Employees (EmpName, Phone)
VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')
GO

CREATE TABLE ##Suppliers(
    SupplierId INT IDENTITY,
    SupplierName VARCHAR(64),
    Fax VARCHAR(16)
)
GO

INSERT INTO ##Suppliers (SupplierName, Fax)
VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')
GO

Now open two empty query windows in SSMS. Place the code for session 1 in one query window and the code for session 2 in the other query window. Then execute each of the two sessions step by step, going back and forth between the two query windows as required. Note that each transaction has a lock on a resource that the other transaction is also requesting a lock on.

Session 1                   | Session 2
===========================================================
BEGIN TRAN;                 | BEGIN TRAN;
===========================================================
UPDATE ##Employees
SET EmpName = 'Mary'
WHERE EmpId = 1
===========================================================
                             | UPDATE ##Suppliers
                             | SET Fax = N'555-1212'
                             | WHERE SupplierId = 1
===========================================================
UPDATE ##Suppliers
SET Fax = N'555-1212'
WHERE SupplierId = 1
===========================================================
<blocked>                    | UPDATE ##Employees
                             | SET Phone = N'555-9999'
                             | WHERE EmpId = 1
===========================================================
                             | <blocked>
===========================================================

A deadlock results; one transaction finishes and the other transaction is aborted and error message 1205 is sent to client.

Close the SSMS query windows for "Session 1" and "Session 2" to commit (or rollback) any open transactions. Lastly, cleanup the temp tables:

DROP TABLE ##Employees
GO
DROP TABLE ##Suppliers
GO

If they edit different records, then a deadlock won't happen. Am I right? — Saeed Neamati, Oct 31 '15 at 04:06
@SaeedNeamati No, the deadlock can still happen, for instance all the attempted editing records (for all the different `@SPID`s ) are on the same page or pages that have been locked. For your saying to be true, lock escalation should be switched off and page_locks disabled for the table(s) in question. — Marcello Miorelli, Aug 26 '16 at 14:06
Note: to efficiently run this, what I did was find/replace all "==" to "--", paste into two query windows, and then use Alt+Drag in SSMS to select the various portions of the script that I wanted to run. In SSMS 2012 and later you can hold alt and drag to create a rectangular text selection — Mark Sowul, Dec 15 '17 at 16:21

How to simulate DEADLOCK on SQL Server?

1 Answers1

Linked