Poziom izolacji SERIALIZABLE

Transakcje i obsługa błędów w SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Najbardziej restrykcyjny poziom izolacji

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Porównanie poziomów izolacji

	Brudne odczyty	Niepowtarzalne odczyty	Odczyty fantomowe
READ UNCOMMITTED	tak	tak	tak
READ COMMITTED	nie	tak	tak
REPEATABLE READ	nie	nie	tak
SERIALIZABLE	nie	nie	nie

Blokowanie rekordów w poziomie SERIALIZABLE

Zapytanie z klauzulą WHERE opartą na zakresie indeksu -> blokuje tylko te rekordy
Zapytanie nieoparte na zakresie indeksu -> blokuje całą tabelę

SERIALIZABLE – zapytanie oparte na zakresie indeksu

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

Zablokowany rekord

SERIALIZABLE – zapytanie oparte na zakresie indeksu

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

Zablokowany rekord

Transakcja 2

...

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

Musi czekać!

SERIALIZABLE – zapytanie oparte na zakresie indeksu

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

Transakcja 2

...

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

Musi czekać!

SERIALIZABLE – zapytanie oparte na zakresie indeksu

Transakcja 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

Transakcja 2

...

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

W końcu wykonana!

SERIALIZABLE – zapytanie oparte na zakresie indeksu

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

Transakcja 2

...

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

Wstawiono natychmiast!

SERIALIZABLE – zapytanie nieoparte na zakresie indeksu

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

Blokuje całą tabelę

Transakcja 2

...

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

Musi czekać!

SERIALIZABLE – zapytanie nieoparte na zakresie indeksu

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

Transakcja 2

...

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

Musi czekać!

SERIALIZABLE – zapytanie nieoparte na zakresie indeksu

Transakcja 1

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

    SELECT * FROM customers;

COMMIT TRAN

Transakcja 2

...

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

W końcu wykonana!

SERIALIZABLE – podsumowanie

Zalety:

Dobra spójność danych: zapobiega odczytom brudnym, niepowtarzalnym i fantomowym

Wady:

Transakcja może być blokowana przez inną transakcję SERIALIZABLE

Kiedy stosować?:

Gdy spójność danych jest wymagana

Czas na ćwiczenia!

Transakcje i obsługa błędów w SQL Server