SET TRANSACTION ISOLATION LEVEL: примеры (SQL)

Краткие примеры использования

Пример установки уровня READ UNCOMMITTED:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Orders WHERE OrderID = 1;
-- Здесь возможно чтение незафиксированных данных
COMMIT TRANSACTION;

Команда выполнена успешно. Уровень изоляции изменен.

Пример использования уровня SNAPSHOT:

-- Включение для базы данных
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Установка уровня для сеанса
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Products WHERE CategoryID = 5;
-- Видит данные на момент начала транзакции
COMMIT TRANSACTION;

Команда выполнена успешно.

Похожие средства в MS SQL

Контроль изоляции возможен не только на уровне сеанса. Часто используют табличные указания (table hints) в рамках отдельного запроса.

• Табличные указания с WITH: Например, WITH (NOLOCK) аналогичен READ UNCOMMITTED для конкретной таблицы в запросе: SELECT * FROM Orders WITH (NOLOCK).
• Табличные указания с изоляцией: WITH (READCOMMITTEDLOCK), WITH (REPEATABLEREAD), WITH (SERIALIZABLE) или WITH (SNAPSHOT) позволяют задать уровень для конкретной таблицы, игнорируя уровень сеанса.
• SET LOCK_TIMEOUT: Управляет временем ожидания блокировки, что дополняет настройки изоляции.

Предпочтительнее использовать SET TRANSACTION ISOLATION LEVEL для глобальных настроек сеанса. Табличные указания применяют для точечной оптимизации или решения конкретных проблем блокировок в сложных сценариях.

Аналоги в других СУБД

Концепция уровней изоляции транзакций есть во всех реляционных СУБД, но реализация и синтаксис отличаются.

Oracle: Уровни READ COMMITTED (по умолчанию), SERIALIZABLE и READ ONLY. Установка: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;. Также поддерживает SET TRANSACTION READ ONLY.

PostgreSQL: Поддерживает READ UNCOMMITTED, READ COMMITTED (по умолчанию), REPEATABLE READ и SERIALIZABLE. Синтаксис аналогичен: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;. Также можно задать при начале транзакции: BEGIN ISOLATION LEVEL REPEATABLE READ;.

MySQL (InnoDB): Уровни READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (по умолчанию) и SERIALIZABLE. Установка для сеанса: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;.

-- Пример для PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts;
COMMIT;

BEGIN
SET
    account_id | balance
-------------+---------
           1 | 1000.00
           2 | 2500.00
COMMIT

Типичные ошибки

Ошибки часто связаны с непониманием области действия и конфликтами блокировок.

1. Установка уровня после начала транзакции. Инструкция должна выполняться до BEGIN TRANSACTION.

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Ошибка непоследовательности
SELECT * FROM Table1;

Сообщение 574, уровень 16, состояние 0: Инструкция SET TRANSACTION ISOLATION LEVEL не может быть выполнена внутри явной или неявной транзакции.

2. Попытка использования уровня SNAPSHOT без его включения на уровне базы данных.

-- Если ALLOW_SNAPSHOT_ISOLATION OFF
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

Сообщение 3952, уровень 16, состояние 1: Для базы данных 'MyDatabase' параметр изоляции моментального снимка не включен.

3. Чрезмерные блокировки при высоких уровнях изоляции, приводящие к взаимоблокировкам (deadlocks) или снижению производительности.

Изменения в последних версиях

В SQL Server 2019 и более новых версиях существенных изменений в синтаксис SET TRANSACTION ISOLATION LEVEL не вносилось. Однако, улучшения касаются оптимизатора запросов и механизма управления версиями строк (RCSI - Read Committed Snapshot Isolation), который может использоваться с уровнем READ COMMITTED при включенной опции базы данных READ_COMMITTED_SNAPSHOT ON.

Рекомендуется использовать RCSI для уменьшения блокировок при чтении. Это альтернатива явному использованию SNAPSHOT изоляции или READ COMMITTED с блокировками.

Расширенные примеры

Пример демонстрации фантомного чтения и его предотвращения с помощью SERIALIZABLE. Создадим таблицу и проверим разное поведение.

-- Сеанс 1
CREATE TABLE #TestPhantom (ID INT, Value NVARCHAR(50));
INSERT INTO #TestPhantom VALUES (1, 'First'), (2, 'Second');

-- Уровень по умолчанию READ COMMITTED (фантомное чтение возможно)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM #TestPhantom; -- Первое чтение

-- В это время в Сеансе 2 выполняется вставка новой строки
WAITFOR DELAY '00:00:05';

SELECT * FROM #TestPhantom; -- Второе чтение может показать новую строку (фантом)
COMMIT TRANSACTION;

-- Повторение с SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM #TestPhantom;
WAITFOR DELAY '00:00:05';
SELECT * FROM #TestPhantom; -- Фантомные строки отсутствуют
COMMIT TRANSACTION;

DROP TABLE #TestPhantom;

-- Результат первого чтения (оба сеанса)
ID   Value
1    First
2    Second
-- Результат второго чтения в READ COMMITTED (если вставка была)
ID   Value
1    First
2    Second
3    Phantom -- Фантомная строка
-- Результат второго чтения в SERIALIZABLE
ID   Value
1    First
2    Second -- Фантомная строка не появилась

Пример сравнения READ COMMITTED SNAPSHOT (RCSI) и SNAPSHOT ISOLATION. При RCSI каждая инструкция видит снимок на момент своего начала, а при SNAPSHOT вся транзакция работает с одним снимком.