How to Simulate Deadlock on SQL Server

How to simulate DEADLOCK on SQL Server?

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

How to simulate a deadlock in SQL Server in a single process?

As many have pointed out, the answer is no, a single process cannot reliably deadlock itself. I came up with the following solution to simulate a deadlock on a development or test system..

Run the script below in a SQL Server Management Studio window. (Tested on 2008 R2 only.) You can leave it running as long as necessary.

In the place you want to simulate a deadlock, insert a call to sp_simulatedeadlock. Run your process, and the deadlock should occur.

When done testing, stop the SSMS query and run the cleanup code at the bottom.

/*
This script helps simulate deadlocks. Run the entire script in a SQL query window. It will continue running until stopped.
In the target script, insert a call to sp_simulatedeadlock where you want the deadlock to occur.
This stored procedure, also created below, causes the deadlock.
When you are done, stop the execution of this window and run the code in the cleanup section at the bottom.
*/
set nocount on

if object_id('DeadlockTest') is not null
drop table DeadlockTest

create table DeadlockTest
(
Deadlock_Key int primary key clustered,
Deadlock_Count int
)
go

if exists (select * from sysobjects where id = object_id(N'sp_simulatedeadlock')
AND objectproperty(id, N'IsProcedure') = 1)
drop procedure sp_simulatedeadlock
GO

create procedure sp_simulatedeadlock
(
@MaxDeadlocks int = -1 -- specify the number of deadlocks you want; -1 = constant deadlocking
)
as begin

set nocount on

if object_id('DeadlockTest') is null
return

-- Volunteer to be a deadlock victim.
set deadlock_priority low

declare @DeadlockCount int

select @DeadlockCount = Deadlock_Count -- this starts at 0
from DeadlockTest
where Deadlock_Key = 2

-- Trace the start of each deadlock event.
-- To listen to the trace event, setup a SQL Server Profiler trace with event class "UserConfigurable:0".
-- Note that the user running this proc must have ALTER TRACE permission.
-- Also note that there are only 128 characters allowed in the trace text.
declare @trace nvarchar(128)

if @MaxDeadlocks > 0 AND @DeadlockCount > @MaxDeadlocks
begin

set @trace = N'Deadlock Test @MaxDeadlocks: ' + cast(@MaxDeadlocks as nvarchar) + N' @DeadlockCount: ' + cast(@DeadlockCount as nvarchar) + N' Resetting deadlock count. Will not cause deadlock.'
exec sp_trace_generateevent
@eventid = 82, -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
@userinfo = @trace

-- Reset the number of deadlocks.
-- Hopefully if there is an outer transaction, it will complete and persist this change.
update DeadlockTest
set Deadlock_Count = 0
where Deadlock_Key = 2
return
end

set @trace = N'Deadlock Test @MaxDeadlocks: ' + cast(@MaxDeadlocks as nvarchar) + N' @DeadlockCount: ' + cast(@DeadlockCount as nvarchar) + N' Simulating deadlock.'
exec sp_trace_generateevent
@eventid = 82, -- 82 = UserConfigurable:0 through 91 = UserConfigurable:9
@userinfo = @trace

declare @StartedTransaction bit
set @StartedTransaction = 0
if @@trancount = 0
begin
set @StartedTransaction = 1
begin transaction
end

-- lock 2nd record
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 2

-- lock 1st record to cause deadlock
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 1

if @StartedTransaction = 1
rollback
end
go

insert into DeadlockTest(Deadlock_Key, Deadlock_Count)
select 1, 0
union select 2, 0

-- Force other processes to be the deadlock victim.
set deadlock_priority high

begin transaction

while 1 = 1
begin

begin try

begin transaction

-- lock 1st record
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 1

waitfor delay '00:00:10'

-- lock 2nd record (which will be locked when the target proc calls sp_simulatedeadlock)
update DeadlockTest
set Deadlock_Count = Deadlock_Count
from DeadlockTest
where Deadlock_Key = 2

rollback

end try
begin catch
print 'Error ' + convert(varchar(20), ERROR_NUMBER()) + ': ' + ERROR_MESSAGE()
goto cleanup
end catch

end

cleanup:

if @@trancount > 0
rollback

drop procedure sp_simulatedeadlock
drop table DeadlockTest

How to deliberately cause a deadlock?

Here's some T-SQL to deliberately cause a deadlock.

Object creation:

CREATE TABLE dbo.DeadLockTest (col1 INT)
INSERT dbo.DeadLockTest SELECT 1

CREATE TABLE dbo.DeadLockTest2 (col1 INT)
INSERT dbo.DeadLockTest2 SELECT 1

Open up a new query window and paste this code and execute it:

BEGIN TRAN
UPDATE dbo.DeadLockTest SET col1 = 1

Open up another new query window and paste and execute this code:

BEGIN TRAN
UPDATE dbo.DeadLockTest2 SET col1 = 1
UPDATE dbo.DeadLockTest SET col1 = 1

Go back to your first query window (with the first BEGIN TRAN statement) and execute this code:

UPDATE dbo.DeadLockTest2 SET col1 = 1

Voila! That's a deadlock.

Simulate a dead lock on SQL server using single client and single session

This is currently possible.

The following code deadlocks itself

BEGIN TRAN
CREATE TYPE dbo.OptionIDs AS TABLE( OptionID INT PRIMARY KEY )
EXEC ('DECLARE @OptionIDs dbo.OptionIDs;')
ROLLBACK

This is a long standing issue due to the use of internal system transactions when creating the instance of the TVP that can't access the lock taken by the user transaction.

At some point it may be fixed though

How is a deadlock possible on a SELECT

It is possible for a SELECT to cause a deadlock if someone else is using the table.

This example is ripped almost 100% from Brent Ozar's video on deadlocks, but changed one command to a SELECT.

To start with, create two tables

CREATE TABLE Lefty (ID int PRIMARY KEY)
CREATE TABLE Righty (ID int PRIMARY KEY)
INSERT INTO Lefty (ID) VALUES (1)
INSERT INTO Righty (ID) VALUES (2)

Then open two windows in SSMS. In the first put this code (don't run it yet)

BEGIN TRAN
UPDATE Lefty SET ID = 5

SELECT * FROM Righty
COMMIT TRAN

In the second window put in this code (also don't run it yet).

BEGIN TRAN
UPDATE Righty SET ID = 5
UPDATE Lefty SET ID = 5
COMMIT TRAN

Now, in the first window, run the first two commands (BEGIN TRAN AND UPDATE LEFTY).
That starts.

In the second window, run the whole transaction. It sits there waiting for your first window, and will wait forever.

In the first window, go back and run the SELECT * FROM Righty and COMMIT TRAN. 5, 4, 3, 2, 1 Boom deadlock - because the second window already had a lock on the table and therefore the SELECT in the first window couldn't run (and the second window couldn't run because the first had a lock on a table it needed).

(I'd like to reiterate - this is Brent Ozar's demo not mine! I'm just passing it on. Indeed, I recommend them).

How to simulate deadlock in PostgreSQL?

  1. Open two connections in parallel, like two instances of psql or two query windows in pgAdmin (each has its own session).
  2. Start a transaction in each connection. BEGIN;
  3. Run mutually conflicting commands in turns.
  4. Before you can commit, one of the two will be rolled back with a deadlock exception.
  5. You may want to roll back the other. ROLLBACK;

Explicitly locking tables is as simple as:

LOCK tbl;

Locking rows can be done with:

SELECT * FROM tbl WHERE boo = 3 FOR UPDATE;

Or FOR SHARE etc. Details in the manual.

(Or implicitly with UPDATE or DELETE.)

Example

Your added example cannot deadlock. Both try to take the same lock on the same row of the same table first. The second will wait for the first to finish.

Example to actually produce a deadlock (rows must exist or no lock will be taken):

Transaction 1                    Transaction 2
BEGIN;
BEGIN;
SELECT salary1
FROM deadlock_demonstration
WHERE worker_id = 1
FOR UPDATE;
SELECT salary1
FROM deadlock_demonstration
WHERE worker_id = 2
FOR UPDATE;
UPDATE deadlock_demonstration
SET salary1 = 100
WHERE worker_id = 2;

UPDATE deadlock_demonstration
SET salary1 = 100
WHERE worker_id = 1;

--> ... deadlock!

Result

The OP user3388473 contributed this screenshot after verifying the solution:

Screenshot reproducing this in psql

How to simulate a deadlock on a row in mysql?

First of all, refering to your last edit, the example in the manual should work. If it doesn't, there is either a fundamental problem, or you are missing some detail, so I would start there and make sure that you get it working.

The deadlock example has 3 steps, and I suspect you may have missed the last one:

  1. T1: select

  2. T2: delete. T2 has to wait for T1 now. Waiting means, that MySQL currently still sees a possible way that both T1 and T2 can finish successfully! For example, T1 can just commit now. Noone knows, so T2 waits for what happens. If you wait too long in this step, you will get a timeout (which is what I suspect happened).

  3. T1: delete. This will result in a deadlock in T2. You need this last step to create a non-resolvable conflict.

You should try that example first, and carefully, as the devil is in the details. Leading to a detail in your own example:

You are using SELECT ... FOR UPDATE. FOR UPDATE is actually a way to reduce the number of deadlocks (which is the opposite of what you want), at the price of locking more restrictively. E.g. you have more situation where MySQL waits just to be safe, instead of going on and hoping it will work out eventually (or not, hence deadlock). Note that the example in the manual uses LOCK IN SHARE MODE for that reason.

So to modify and expand your own example to get a deadlock, you can do

 T1: START TRANSACTION;
SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;

T2: START TRANSACTION;
UPDATE table SET id=2 WHERE id=1
-- wait

T1: UPDATE table SET id=2 WHERE id=1
-- deadlock in T2

For completeness (and to exclude a potential misunderstanding): the row has to exists, if your table is e.g. empty, you won't get a deadlock.

If you use FOR UPDATE instead, you don't get a deadlock, but T2 keeps waiting until you commit/rollback T1. It has to do with the way locking works, but you can maybe get an idea of that if you add a select to T2:

 T1: START TRANSACTION;
SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;

T2: START TRANSACTION;
SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE;
-- fine in shared mode. Waits here if you use `for update`!

T1: UPDATE table SET id=2 WHERE id=1
-- wait

T2: UPDATE table SET id=2 WHERE id=1
-- deadlock

If you replace both LOCK IN SHARE MODE with FOR UPDATE, T2 will wait at/before the select, until T1 commits, without a deadlock.



Related Topics



Leave a reply



Submit