Die sechs Gesichter der Deadlocks

USE TempDB
GO

CREATE TABLE T1
(
   C1 INT
)
GO

INSERT INTO T1 VALUES (1)
GO

CREATE TABLE T2
(
   C1 INT
)

INSERT INTO T2 VALUES (1)
GO

SELECT * FROM T1
SELECT * FROM T2
GO

BEGIN TRANSACTION

UPDATE T1 SET C1 = 2

UPDATE T2 SET C1 = 2

-- COMMIT TRANSACTION

DROP TABLE T1
DROP TABLE T2
GO

USE TempDB
GO

BEGIN TRANSACTION

UPDATE T2 SET C1 = 1

UPDATE T1 SET C1 = 1

COMMIT TRANSACTION
GO

USE master
GO

CREATE DATABASE BookmarkLookupDL
GO

USE BookmarkLookupDL
GO

CREATE TABLE Deadlock (
   Col1 INT NOT NULL PRIMARY KEY CLUSTERED,
   Col2 INT NOT NULL,
   Col3 INT NOT NULL
)
GO

CREATE NONCLUSTERED INDEX idx_Col3 ON Deadlock(Col3)
GO

INSERT INTO Deadlock VALUES (1, 1, 1)
GO

SELECT * FROM Deadlock
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

WHILE (1 = 1)
BEGIN
   UPDATE Deadlock
   SET Col1 = Col1 + 1
   WHERE Col3 = 1
END
GO

USE BookmarkLookupDL
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

WHILE (1 = 1)
BEGIN
   SELECT * FROM Deadlock WITH (INDEX(idx_Col3))
  -– The hint is necessary to overcome the Tipping
  -- Point to produce a Bookmark Lookup
   WHERE Col3 = 1
END
GO

-- Creates a Covering Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_Col3 ON Deadlock(Col3)
INCLUDE (Col1, Col2)
WITH (DROP_EXISTING = ON)
GO

USE master
GO
-- Create a new database
CREATE DATABASE DeadlockingDemo
GO

-- Use it
USE DeadlockingDemo
GO

-- Create a table without any indexes
CREATE TABLE Table1 (
   Column1 INT, Column2 INT )
GO

-- Insert a few records
INSERT INTO Table1 VALUES (1, 1)
INSERT INTO Table1 VALUES (2, 2)
INSERT INTO Table1 VALUES (3, 3)
INSERT INTO Table1 VALUES (4, 4)
GO

-- Create a table without any indexes
CREATE TABLE Table2 (
   Column1 INT, Column2 INT )
GO
-- Insert a few records
INSERT INTO Table2 VALUES (1, 1)
INSERT INTO Table2 VALUES (2, 2)
INSERT INTO Table2 VALUES (3, 3)
INSERT INTO Table2 VALUES (4, 4)
GO

BEGIN TRANSACTION
   -- Acquires an Exclusive Lock on the row
   UPDATE Table1 SET Column1 = 3 WHERE Column2 = 1
   -- Execute the query from Session 2...
   -- ...

   -- This query now requests a Shared Lock, but gets
  -- blocked, because the other session/transaction
  -- has an Exclusive Lock on one row, that is
  -- currently updated
   SELECT Column1 FROM Table2
   --WITH (INDEX = idx_Column2)
  -- The index hint is necessary, because in this
  -- tiny table, SQL Server will just scan the whole
  -- table...
   WHERE Column2 = 3

ROLLBACK TRANSACTION
GO

-- Use the previous created database
USE DeadlockingDemo
GO

BEGIN TRANSACTION
   -- Acquires an Exclusive Lock on the row
   UPDATE Table2 SET Column1 = 5 WHERE Column2 = 2

   SELECT * FROM sys.dm_tran_locks
   WHERE request_session_id = @@SPID

   -- Continue with the query from Session 2...
   -- ...

   -- This query now requests a Shared Lock, but
  -- gets blocked, because the other session/
  -- transaction has an Exclusive Lock on one row,
  -- that is currently updated
   SELECT Column1 FROM Table1
   -- WITH (INDEX = idx_Column2) -- The index hint
  -- is necessary, because in this tiny table SQL 
  -- Server will just scan the whole table...
   WHERE Column2 = 4

ROLLBACK TRANSACTION
GO

-- Create new indexes so that SQL Server has alter
-- native access paths to the data. The previous
-- 2 SELECT statements can be now done through the
-- Nonclustered Index without acquiring a Shared
-- Lock on the table itself (which currently holds
-- an Exclusive Lock from the UPDATE statement).
CREATE NONCLUSTERED INDEX idx_Column2 ON Table1(Column2)

CREATE NONCLUSTERED INDEX idx_Column2 ON Table2(Column2)
GO

USE AdventureWorks2014
GO

-- Causes a deadlock when we access data in the
-- wrong order
BEGIN TRANSACTION
   -- 1st range of data
   SELECT * FROM Person.Person
   WHERE ModifiedDate = '20120208'

   UPDATE Person.Person
   SET FirstName = '...'
   WHERE ModifiedDate = '20120208'
   -- Switch to session 2
   -- ... 
   -- 2nd range of data
   -- This statement will cause a deadlock!
   SELECT * FROM Person.Person
   WHERE ModifiedDate = '20120209'

   UPDATE Person.Person
   SET FirstName = '...'
   WHERE ModifiedDate = '20120209'

ROLLBACK
GO

USE AdventureWorks2014
GO
-- Causes a deadlock when we access data in the
-- wrong order
BEGIN TRANSACTION
   -- 2nd range of data
   SELECT * FROM Person.Person
   WHERE ModifiedDate = '20120209'

   UPDATE Person.Person
   SET FirstName = '...'
   WHERE ModifiedDate = '20120209'
  -- Switch to session 1
   -- ...
   -- 1st range of data
   -- This statement will cause a deadlock!
   SELECT * FROM Person.Person
   WHERE ModifiedDate = '20120208'

   UPDATE Person.Person
   SET FirstName = '...'
   WHERE ModifiedDate = '20120208'

ROLLBACK
GO

USE AdventureWorks2014
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRANSACTION
   SELECT * FROM Person.Person
   WHERE ModifiedDate = '20120208'
   -- Switch to session 2
   -- ...

   -- This statement will cause a deadlock!
   UPDATE Person.Person
   SET FirstName = '...'
   WHERE ModifiedDate = '20120208'

ROLLBACK
GO

USE AdventureWorks2014
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

BEGIN TRANSACTION
   SELECT * FROM Person.Person
   WHERE ModifiedDate = '20120208' WITH (UPDLOCK)
   -- Switch to session 1
   -- ...

   -- This statement will cause a deadlock!
   UPDATE Person.Person
   SET FirstName = '...'
   WHERE ModifiedDate = '20120208'

ROLLBACK
GO