This repository contains examples to cause transactions deadlock in Microsoft SQL Server.
Go to file
Maksymilian Lúmenn Stachowiak 4ed642350c Added DDL Script
2023-12-12 17:50:19 +01:00
read commited Created examples for deadlock on each isolation level 2023-12-11 23:43:31 +01:00
read uncomitted Created examples for deadlock on each isolation level 2023-12-11 23:43:31 +01:00
repeatable read Created examples for deadlock on each isolation level 2023-12-11 23:43:31 +01:00
serializable Created examples for deadlock on each isolation level 2023-12-11 23:43:31 +01:00
ddl-script.sql Added DDL Script 2023-12-12 17:50:19 +01:00
LICENSE Initial commit 2023-12-11 23:53:04 +01:00
README.MD Added DDL Script 2023-12-12 17:50:19 +01:00

Purpose

This repostiory contains examples which might be used to show/explain why and when deadlocks happen in MSSQL.

How to

  1. Create example tables using ddl-script.sql
  2. Open two different SQL sessions.
  3. In each session open one file.
  4. Run SQL commands according to comments.

For example - running Read Commited should look like:

Session 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION
    UPDATE Wycieczki
        SET cena = cena*2
    WHERE
        kraj = 'Bangkok'
-- STEP 1 RUN ABOVE

Session 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION
    UPDATE Bilety
        SET cena = cena * 2
    WHERE
        kraj = 'Ateny'
-- STEP 2 - RUN ABOVE

Session 1

    SELECT
        *
    FROM
        Bilety
    WHERE
        kraj = 'Ateny'
-- STEP 3 RUN BETWEEN 1 AND 3
COMMIT TRANSACTION -- COMMIT AFTER DEADLOCK HAPPENS

Session 2

    SELECT
        *
    FROM
        Wycieczki 
    WHERE
        kraj = 'Bangkok'
-- STEP 4 RUN BETWEEN 2 AND 4
COMMIT TRANSACTION -- COMMIT AFTER DEADLOCK HAPPENS