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?
- Open two connections in parallel, like two instances of
psql
or two query windows in pgAdmin (each has its own session). - Start a transaction in each connection.
BEGIN;
- Run mutually conflicting commands in turns.
- Before you can commit, one of the two will be rolled back with a deadlock exception.
- 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:
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:
T1:
select
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).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
How to Call Oracle Md5 Hash Function
How to Determine If a String Is Numeric in SQL
How to Perform Update Query with Subquery in Access
Rotate/Pivot Table with Aggregation in Oracle
Sqlite Database - Select the Data Between Two Dates
Conversion Failed When Converting the Nvarchar Value ... to Data Type Int
Querying Active Directory from SQL Server 2005
How to Detect If a String Contains Special Characters
SQL Server Variable Scope in a Stored Procedure
Number of Days Between Two Dates - Ansi SQL
Connecting to Oracle Database Using SQL Server Integration Services
Is Activerecord's "Order" Method Vulnerable to SQL Injection
Capturing Multiple Error Messages from a Single Statement Inside Try Catch
How to Join Two Tables But Only Return Rows That Don't Match