Úroveň izolace SERIALIZABLE

Transakce a zpracování chyb v SQL Serveru

Miriam Antona

Software Engineer

SERIALIZABLE

Nejrestriktivnější úroveň izolace

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Porovnání úrovní izolace

	Špinavé čtení	Neopakovatelné čtení	Fantomové čtení
READ UNCOMMITTED	ano	ano	ano
READ COMMITTED	ne	ano	ano
REPEATABLE READ	ne	ne	ano
SERIALIZABLE	ne	ne	ne

Uzamykání záznamů se SERIALIZABLE

Dotaz s klauzulí WHERE na rozsah indexu -> Uzamkne pouze dané záznamy
Dotaz mimo rozsah indexu -> Uzamkne celou tabulku

SERIALIZABLE – dotaz na rozsah indexu

Transakce 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 |

Uzamčený záznam

SERIALIZABLE – dotaz na rozsah indexu

Transakce 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 |

Uzamčený záznam

Transakce 2

...

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

Musí čekat!

SERIALIZABLE – dotaz na rozsah indexu

Transakce 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 |

Transakce 2

...

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

Musí čekat!

SERIALIZABLE – dotaz na rozsah indexu

Transakce 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

Transakce 2

...

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

Konečně provedeno!

SERIALIZABLE – dotaz na rozsah indexu

Transakce 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 |

Transakce 2

...

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

Okamžitě vloženo!

SERIALIZABLE – dotaz mimo rozsah indexu

Transakce 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 |

Uzamkne celou tabulku

Transakce 2

...

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

Musí čekat!

SERIALIZABLE – dotaz mimo rozsah indexu

Transakce 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 |

Transakce 2

...

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

Musí čekat!

SERIALIZABLE – dotaz mimo rozsah indexu

Transakce 1

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

    SELECT * FROM customers;

COMMIT TRAN

Transakce 2

...

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

Konečně provedeno!

SERIALIZABLE – shrnutí

Výhody:

Dobrá konzistence dat: Zabraňuje špinavému, neopakovatelnému i fantomovému čtení

Nevýhody:

Transakce může být blokována transakcí SERIALIZABLE

Kdy použít?:

Pokud je konzistence dat nezbytná

Pojďme si procvičit!

Transakce a zpracování chyb v SQL Serveru