Уровень изоляции SERIALIZABLE

Транзакции и обработка ошибок в SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Наиболее строгий уровень изоляции

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Сравнение уровней изоляции

	Грязное чтение	Неповторяемое чтение	Фантомное чтение
READ UNCOMMITTED	да	да	да
READ COMMITTED	нет	да	да
REPEATABLE READ	нет	нет	да
SERIALIZABLE	нет	нет	нет

Блокировка записей при SERIALIZABLE

Запрос с WHERE по диапазону индекса -> Блокирует только эти записи
Запрос без диапазона индекса -> Блокирует всю таблицу

SERIALIZABLE — запрос по диапазону индекса

Транзакция 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 |

Заблокированная запись

SERIALIZABLE — запрос по диапазону индекса

Транзакция 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 |

Заблокированная запись

Транзакция 2

...

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

Ожидает!

SERIALIZABLE — запрос по диапазону индекса

Транзакция 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 |

Транзакция 2

...

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

Ожидает!

SERIALIZABLE — запрос по диапазону индекса

Транзакция 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

Транзакция 2

...

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

Наконец выполнено!

SERIALIZABLE — запрос по диапазону индекса

Транзакция 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 |

Транзакция 2

...

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

Вставлено мгновенно!

SERIALIZABLE — запрос без диапазона индекса

Транзакция 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 |

Блокирует всю таблицу

Транзакция 2

...

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

Ожидает!

SERIALIZABLE — запрос без диапазона индекса

Транзакция 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 |

Транзакция 2

...

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

Ожидает!

SERIALIZABLE — запрос без диапазона индекса

Транзакция 1

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

    SELECT * FROM customers;

COMMIT TRAN

Транзакция 2

...

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

Наконец выполнено!

SERIALIZABLE — итоги

Преимущества:

Высокая согласованность данных: предотвращает грязное, неповторяемое и фантомное чтение

Недостатки:

Транзакция может быть заблокирована другой транзакцией SERIALIZABLE

Когда использовать?:

Когда согласованность данных критически важна

Давайте потренируемся!

Транзакции и обработка ошибок в SQL Server