ИТ.03 - 27 - Расширенные транзакции и уровни изоляции

Введение

В предыдущей лекции мы познакомились с основами транзакций в MySQL, изучили их свойства ACID и базовые команды управления (START TRANSACTION, COMMIT, ROLLBACK). Вы узнали, что транзакции обеспечивают атомарность, согласованность, изолированность и долговечность операций с базой данных.

Однако в реальных приложениях, где множество пользователей одновременно работают с одними и теми же данными, простых транзакций может оказаться недостаточно. Возникают вопросы: как предотвратить конфликты между параллельными транзакциями? Как обеспечить согласованность данных без потери производительности? Какие механизмы позволяют гибко управлять откатами внутри сложных многошаговых операций?

В этой лекции мы углубимся в расширенные аспекты транзакций в MySQL:

Уровни изоляции — как MySQL управляет видимостью изменений между параллельными транзакциями.
Аномалии параллельного доступа — какие проблемы могут возникнуть при неправильной настройке изоляции.
Точки сохранения (SAVEPOINT) — как создавать промежуточные состояния для частичного отката.
Блокировки и deadlock-и — как MySQL предотвращает конфликты и что делать при взаимных блокировках.

Понимание этих тем критически важно для разработки высоконагруженных приложений, где корректность данных и производительность идут рука об руку.

Уровни изоляции транзакций

Изоляция — одно из ключевых свойств ACID. Она определяет, насколько изменения внутри одной транзакции видны другим транзакциям, выполняющимся параллельно. MySQL поддерживает четыре стандартных уровня изоляции, определённых в стандарте SQL:

READ UNCOMMITTED (чтение незафиксированных данных)
READ COMMITTED (чтение зафиксированных данных)
REPEATABLE READ (повторяемое чтение) — уровень по умолчанию в MySQL
SERIALIZABLE (сериализуемый)

Каждый уровень представляет собой компромисс между согласованностью данных и производительностью. Более строгие уровни лучше защищают от аномалий, но снижают параллелизм и увеличивают накладные расходы.

1. READ UNCOMMITTED

На этом уровне транзакция может читать данные, которые были изменены другой транзакцией, но ещё не зафиксированы. Это самый слабый уровень изоляции, который допускает «грязное» чтение (dirty read).

Пример:

-- Транзакция 1
START TRANSACTION;
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;
-- Пока не выполнен COMMIT

-- Транзакция 2 (уровень READ UNCOMMITTED)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Увидит незафиксированные +1000
COMMIT;

Если Транзакция 1 выполнит ROLLBACK, то Транзакция 2 прочитает данные, которые никогда не существовали в согласованном состоянии.

2. READ COMMITTED

Транзакция видит только те изменения, которые были зафиксированы другими транзакциями на момент выполнения каждого отдельного оператора SELECT. Это устраняет «грязное» чтение, но допускает неповторяющееся чтение (non‑repeatable read).

Пример:

-- Транзакция 1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Допустим, balance = 5000

-- Транзакция 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
COMMIT;

-- Транзакция 1 (снова)
SELECT balance FROM accounts WHERE id = 1; -- Уже balance = 4000!
COMMIT;

В рамках одной транзакции два последовательных чтения одного и того же ряда дали разные результаты.

3. REPEATABLE READ (по умолчанию в MySQL)

MySQL гарантирует, что в рамках одной транзакции повторное чтение одних и тех же данных даст одинаковый результат, даже если другие транзакции изменяли эти данные и зафиксировали изменения. Достигается за счёт снимка данных (snapshot) на момент первого чтения.

Пример:

-- Транзакция 1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- balance = 5000

-- Транзакция 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
COMMIT;

-- Транзакция 1 (снова)
SELECT balance FROM accounts WHERE id = 1; -- Всё ещё balance = 5000 (снимок)
COMMIT; -- После COMMIT баланс станет 4000

Уровень REPEATABLE READ защищает от «грязного» и неповторяющегося чтения, но не от чтения фантомов (phantom read).

4. SERIALIZABLE

Самый строгий уровень. Транзакции выполняются так, как если бы они шли последовательно, одна за другой. MySQL использует блокировки для предотвращения любых конфликтов. Этот уровень полностью исключает все аномалии, но существенно снижает параллелизм.

Пример:

-- Транзакция 1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000; -- Блокирует диапазон

-- Транзакция 2
START TRANSACTION;
INSERT INTO accounts (user_name, balance) VALUES ('Новый', 2000);
-- Будет заблокирована до COMMIT или ROLLBACK Транзакции 1

Как изменить уровень изоляции

Уровень изоляции можно задать для текущего сеанса или глобально:

-- Для текущего сеанса
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Для текущей транзакции (только перед START TRANSACTION)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

Узнать текущий уровень можно с помощью запроса:

SELECT @@SESSION.transaction_isolation;
SELECT @@GLOBAL.transaction_isolation;

Аномалии параллельного доступа

При неправильном выборе уровня изоляции могут возникать следующие аномалии:

Аномалия	READ UNCOMMITTED	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
Потерянное обновление (Lost Update)	Нет	Нет	Нет	Нет
«Грязное» чтение (Dirty Read)	Да	Нет	Нет	Нет
Неповторяющееся чтение (Non‑Repeatable Read)	Да	Да	Нет	Нет
Чтение фантомов (Phantom Read)	Да	Да	Да	Нет

Потерянное обновление (Lost Update)

Ситуация, когда два параллельных изменения одного ряда приводят к тому, что одно из обновлений теряется. Например:

-- Исходное значение balance = 1000
-- Транзакция 1: balance = balance + 100
-- Транзакция 2: balance = balance + 200
-- Ожидаемый результат: 1300
-- Возможный результат, если нет защиты: 1100 или 1200

В MySQL уровень REPEATABLE READ (и выше) предотвращает потерю обновлений за счёт механизма блокировок на запись.

«Грязное» чтение (Dirty Read)

Чтение данных, которые были изменены другой транзакцией, но ещё не зафиксированы. Если та транзакция откатится, прочитанные данные окажутся недействительными.

Неповторяющееся чтение (Non‑Repeatable Read)

В рамках одной транзакции два одинаковых запроса возвращают разные данные из‑за того, что другая транзакция изменила и зафиксировала эти данные между чтениями.

Чтение фантомов (Phantom Read)

Ситуация, когда повторный запрос возвращает набор строк, отличающийся от первоначального из‑за вставки или удаления строк другими транзакциями.

Пример:

-- Транзакция 1
START TRANSACTION;
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- Результат: 5

-- Транзакция 2
START TRANSACTION;
INSERT INTO accounts (user_name, balance) VALUES ('Фантом', 1500);
COMMIT;

-- Транзакция 1 (снова)
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- Результат: 6
COMMIT;

Уровень REPEATABLE READ в MySQL защищает от фантомов для операций с уже существующими строками, но не для диапазонных запросов в чистом виде (хотя InnoDB использует next‑key locking, который частично решает проблему).

Точки сохранения (SAVEPOINT) и расширенное управление

Транзакции не всегда должны быть «всё или ничего». Иногда внутри длинной транзакции нужно иметь возможность откатиться к определённому промежуточному состоянию, не отменяя всю работу. Для этого в MySQL существует механизм точек сохранения (SAVEPOINT).

Создание и использование SAVEPOINT

START TRANSACTION;

-- Шаг 1
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
SAVEPOINT after_first_transfer;

-- Шаг 2
UPDATE accounts SET balance = balance + 5000 WHERE id = 3;
SAVEPOINT after_second_transfer;

-- Шаг 3 (ошибочная операция)
UPDATE accounts SET balance = balance - 10000 WHERE id = 1; -- Недостаточно средств?

-- Если ошибка, откатываемся к after_second_transfer
ROLLBACK TO SAVEPOINT after_second_transfer;

-- Или откатываемся к after_first_transfer
ROLLBACK TO SAVEPOINT after_first_transfer;

-- Если всё хорошо, фиксируем
COMMIT;

Освобождение точки сохранения

Точку сохранения можно явно удалить, чтобы освободить ресурсы:

RELEASE SAVEPOINT after_first_transfer;

После COMMIT или ROLLBACK (без указания точки) все точки сохранения, созданные в транзакции, автоматически удаляются.

Практический пример: многошаговый перевод

START TRANSACTION;

-- Проверяем баланс
SELECT balance INTO @current_balance FROM accounts WHERE id = 1;
IF @current_balance < 10000 THEN
    ROLLBACK;
    SELECT 'Недостаточно средств' AS result;
ELSE
    -- Перевод 5000 первому получателю
    UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
    SAVEPOINT transfer1;

    -- Перевод 3000 второму получателю
    UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 3000 WHERE id = 3;
    SAVEPOINT transfer2;

    -- Проверяем, остался ли минимальный баланс
    SELECT balance INTO @remaining FROM accounts WHERE id = 1;
    IF @remaining < 1000 THEN
        -- Откатываем второй перевод
        ROLLBACK TO SAVEPOINT transfer1;
        SELECT 'Второй перевод отменён (ниже минимума)' AS result;
    END IF;

    COMMIT;
END IF;

Блокировки и deadlock-и

Чтобы обеспечить изоляцию и согласованность, MySQL использует блокировки. Блокировки бывают:

Неявные — автоматически устанавливаются движком InnoDB в зависимости от уровня изоляции и типа операции.
Явные — программист может явно запросить блокировку с помощью SELECT ... FOR UPDATE или SELECT ... LOCK IN SHARE MODE.

Явные блокировки

START TRANSACTION;
-- Блокируем строку для обновления (монопольная блокировка)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Другая транзакция, пытающаяся выполнить SELECT ... FOR UPDATE для той же строки,
-- будет ждать, пока первая транзакция не завершится.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Deadlock (взаимная блокировка)

Deadlock возникает, когда две или более транзакции взаимно блокируют друг друга, каждая ожидает ресурс, занятый другой.

Пример:

-- Транзакция 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Блокирует строку id=1

-- Транзакция 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 2;
-- Блокирует строку id=2

-- Теперь:
-- Транзакция 1 пытается заблокировать строку id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Ожидает Транзакцию 2
-- Транзакция 2 пытается заблокировать строку id=1
UPDATE accounts SET balance = balance + 200 WHERE id = 1; -- Ожидает Транзакцию 1

MySQL обнаруживает deadlock и автоматически откатывает одну из транзакций (обычно ту, которая потребовала меньше ресурсов), позволяя другой завершиться. В логах появляется сообщение:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Как избежать deadlock-ов

Упорядочивайте доступ к данным — всегда блокируйте ресурсы в одинаковом порядке.
Держите транзакции короткими — чем меньше времени транзакция удерживает блокировки, тем ниже вероятность конфликта.
Используйте менее строгие уровни изоляции, если это допустимо.
Обрабатывайте ошибки deadlock в коде приложения — при получении ошибки 1213 можно повторить транзакцию.

Практические примеры

Пример 1: Настройка уровня изоляции для отчётов

-- Для генерации отчётов нам не нужна абсолютная актуальность,
-- но важно не блокировать операционные транзакции.
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT user_name, balance FROM accounts WHERE balance > 0;
-- Здесь могут быть неповторяющиеся чтения, но для отчёта это приемлемо.
COMMIT;

Пример 2: Безопасное обновление с блокировкой

START TRANSACTION;
-- Блокируем строку, чтобы гарантировать актуальность данных
SELECT * FROM products WHERE id = 123 FOR UPDATE;

-- Проверяем остаток
IF stock_quantity >= 10 THEN
    UPDATE products SET stock_quantity = stock_quantity - 10 WHERE id = 123;
    INSERT INTO orders (product_id, quantity) VALUES (123, 10);
    COMMIT;
ELSE
    ROLLBACK;
    SELECT 'Недостаточно товара на складе' AS result;
END IF;

Пример 3: Обработка deadlock с повтором

# Псевдокод приложения
max_retries = 3
for attempt in range(max_retries):
    try:
        execute_sql("START TRANSACTION")
        execute_sql("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        execute_sql("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        execute_sql("COMMIT")
        break
    except DeadlockError:
        if attempt == max_retries - 1:
            raise
        sleep(0.1 * (2 ** attempt))  # Экспоненциальная задержка

Заключение

Расширенные механизмы транзакций в MySQL предоставляют мощные инструменты для управления параллельным доступом и обеспечения целостности данных:

Уровни изоляции позволяют выбрать баланс между согласованностью и производительностью. По умолчанию REPEATABLE READ подходит для большинства приложений.
Точки сохранения дают гибкость для частичного отката внутри сложных многошаговых операций.
Блокировки (явные и неявные) защищают данные от конфликтов, но требуют осторожности, чтобы избежать deadlock-ов.
Deadlock-и — неизбежное зло в высококонкурентных средах; важно проектировать транзакции так, чтобы минимизировать их вероятность, и обрабатывать в коде приложения.

На практике:

Всегда анализируйте требования приложения к согласованности данных.
Тестируйте поведение транзакций под нагрузкой.
Мониторьте deadlock-и в логах MySQL и оптимизируйте проблемные запросы.

В следующих лекциях мы рассмотрим дополнительные аспекты работы с базами данных: оптимизацию запросов, репликацию, резервное копирование и восстановление.