Skip to content

Instantly share code, notes, and snippets.

@schotime
Last active October 26, 2017 08:43
Show Gist options
  • Save schotime/5086513 to your computer and use it in GitHub Desktop.
Save schotime/5086513 to your computer and use it in GitHub Desktop.
Reproduction of Deadlock on Select in Sql Server
create database deadlocktest
go
SELECT snapshot_isolation_state_desc from sys.databases where name='deadlocktest'
go
--ensure this is off: If Not do the following alters
ALTER DATABASE deadlocktest
SET ALLOW_SNAPSHOT_ISOLATION OFF
go
ALTER DATABASE deadlocktest
SET READ_COMMITTED_SNAPSHOT OFF
go
--drop table a
--drop table b
CREATE TABLE A (
id INT NOT NULL,
[value] INT
)
CREATE TABLE B (
id INT NOT NULL,
[value] INT
)
INSERT INTO A VALUES(1, 1)
INSERT INTO B VALUES(1, 1)
INSERT INTO A VALUES(2, 2)
INSERT INTO B VALUES(2, 2)
--Open 2 Query Analysers and 1 is tran1 and 2 is tran2
--1. tran1:
begin tran
update B set value = 444 where id=2
--2. tran 2:
begin tran
select value, * from A (updlock) where value = 1 or id = 1
--3. tran 1:
update A set value = 555 where id = 1
--4. tran 2:
select value, * from B where value = 1 or id = 1
--=> DEADLOCK
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment