SERIALIZABLE-isolatieniveau

Transacties en foutafhandeling in SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Meest restrictieve isolatieniveau

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Vergelijking isolatieniveaus

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes
SERIALIZABLE	no	no	no

Records vergrendelen met SERIALIZABLE

Query met WHERE op basis van een indexbereik -> vergrendelt alleen die records
Query niet op basis van een indexbereik -> vergrendelt de hele tabel

SERIALIZABLE - query op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Record vergrendeld

SERIALIZABLE - query op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Record vergrendeld

Transactie 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Moet wachten!

SERIALIZABLE - query op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Transactie 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Moet wachten!

SERIALIZABLE - query op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

COMMIT TRAN

Transactie 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Eindelijk uitgevoerd!

SERIALIZABLE - query op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Transactie 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (200, 'Phantom', 'Ph', '[email protected]', 555666222);

Meteen ingevoegd!

SERIALIZABLE - query niet op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

Vergrendelt de hele tabel

Transactie 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Moet wachten!

SERIALIZABLE - query niet op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

Transactie 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Moet wachten!

SERIALIZABLE - query niet op basis van een indexbereik

Transactie 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

COMMIT TRAN

Transactie 2

...

INSERT INTO customers 
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Eindelijk uitgevoerd!

SERIALIZABLE - samenvatting

Voordelen:

Goede dataconsistentie: voorkomt dirty, non-repeatable en phantom reads

Nadelen:

Je kunt geblokkeerd worden door een SERIALIZABLE-transactie

Wanneer gebruiken?:

Als dataconsistentie cruciaal is

Laten we oefenen!

Transacties en foutafhandeling in SQL Server