SQL Server: how to simulate and catch deadlock

SQL Server Profiler has the template "locks" that is intended especially to trace deadlock. Let's try to simulate and catch a deadlock situation.

Suppose the test database is named test. Create two tables and feed them some data.

USE test;

CREATE TABLE dbo.invoices (
   id int NOT NULL,
   num nvarchar(20) NOT NULL,
   customer nvarchar(100) NOT NULL,
   created_at datetime NOT NULL,
   updated_at datetime NOT NULL,
   CONSTRAINT PK_invoices PRIMARY KEY (id)
);

CREATE TABLE dbo.invoice_items (
   invoice_id int NOT NULL,
   item_index int NOT NULL,
   product nvarchar(100) NOT NULL,
   qty int NOT NULL,
   price money NOT NULL,
   CONSTRAINT PK_invoice_items PRIMARY KEY (invoice_id, item_index)
);

INSERT INTO dbo.invoices (id, num, customer, created_at, updated_at) VALUES
(1, 'INV180125', 'NASA', '2018-01-25', '2018-01-25'),
(2, 'INV180128', 'SpaceX', '2018-01-28', '2018-01-28');
INSERT INTO invoice_items (invoice_id, item_index, product, qty, price) VALUES
(1, 1, 'NK-33 engine', 7, 145000),
(1, 2, 'Rocketdyne RS-25', 2, 560000),
(2, 1, 'Merlin 1D engine', 7, 125000),
(2, 2, 'J58 engine', 3, 225000);

In SSMS open the query in new session and copy the following code (don't run yet!)

USE test;
BEGIN TRANSACTION
   UPDATE invoices SET updated_at = GETDATE() WHERE id = 1;
   WAITFOR DELAY '00:00:05';
   UPDATE invoice_items SET qty = qty + 1 WHERE invoice_id = 1 AND item_index = 2;
ROLLBACK TRANSACTION

Open the second query and copy the following code

USE test;
BEGIN TRANSACTION
   UPDATE invoice_items SET qty = qty + 1 WHERE invoice_id = 1 AND item_index = 2;
   WAITFOR DELAY '00:00:05';
   UPDATE invoices SET updated_at = GETDATE() WHERE id = 1;
ROLLBACK TRANSACTION

One can see than the updating order is reverse that should produce a deadlock.

Start a new trace in SQL Server Profiler using the "TSQL_Locks" template

In SSMS run two queries and wait about 5 seconds. One query will be selected as the deadlock victim with the corresponding error message.

Msg 1205, Level 13, State 51, Line 5Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Returning to Profiler, you can see the deadlock events including the graph.