SQL ·
‹ Предыдущий Следующий ›
⏱ 5 минут чтения Обновлено: 2026-11-21

Транзакции в SQL: ACID, COMMIT и ROLLBACK, уровни изоляции и аномалии

Вася переводит Семёну 50 баллов, и приложение выполняет две команды: UPDATE user_points SET points = points - 50 WHERE user_id = 2; и UPDATE user_points SET points = points + 50 WHERE user_id = 3;. После первой команды сервер приложения перезапускается. В режиме автофиксации первая команда уже сохранена, вторая не выполнялась никогда: у Васи списано 50 баллов, Семёну ничего не пришло, и база не знает, что эти две команды были одной операцией.

Транзакция в SQL — группа команд, которую база данных выполняет как одно целое: либо все изменения фиксируются командой COMMIT, либо все отменяются командой ROLLBACK, и промежуточное состояние не видно другим пользователям. Надёжность транзакций описывают четыре свойства ACID: атомарность, согласованность, изоляция и долговечность. В MySQL транзакции поддерживает движок InnoDB, который используется по умолчанию.

1. Данные для примеров: темы и баллы пользователей

Примеры урока выполняются по порядку на учебной базе forum (MySQL 8, InnoDB): у тем появляется счётчик просмотров views, а у пользователей — баллы в таблице user_points, которые нельзя увести в минус. Все выводы ниже рассчитаны на эти начальные данные.

Создайте базу заново скриптом из раздела Схема учебной базы форума (пользователи Kirill, Vasya, Semen и темы 1 и 2), затем выполните:

ALTER TABLE topics ADD COLUMN views INT NOT NULL DEFAULT 0;
UPDATE topics SET views = 10 WHERE id = 1;
UPDATE topics SET views = 20 WHERE id = 2;

CREATE TABLE user_points (
    user_id INT NOT NULL,
    points  INT NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_points_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT chk_points_non_negative CHECK (points >= 0)
);

INSERT INTO user_points (user_id, points) VALUES (1, 100), (2, 80), (3, 20);
SELECT user_id, points FROM user_points;
+---------+--------+
| user_id | points |
+---------+--------+
|       1 |    100 |
|       2 |     80 |
|       3 |     20 |
+---------+--------+

Пересоздайте базу, даже если проходили урок DML: INSERT, UPDATE и DELETE: он добавил тему 3 и новые строки, и выводы ниже с ними не совпадут. Ограничение CHECK разобрано в уроке про CREATE TABLE.

Две сессии — два окна клиента

С раздела 4 примеры показывают две параллельные транзакции. Откройте два окна терминала, в каждом запустите mysql -u root -p forum и выполняйте команды строго в порядке шагов таблицы. Одно окно — сессия A, другое — сессия B. В MySQL Workbench то же самое дают две вкладки с отдельными соединениями.

2. ACID: четыре свойства транзакции

ACID — аббревиатура из четырёх гарантий транзакции: атомарность (Atomicity) — всё или ничего, согласованность (Consistency) — данные остаются корректными, изоляция (Isolation) — параллельные транзакции не мешают друг другу, долговечность (Durability) — зафиксированное не теряется при сбое. InnoDB обеспечивает каждое свойство отдельным механизмом.

Свойство Что гарантирует Как обеспечивает InnoDB
Атомарность (Atomicity) Выполняются все команды транзакции или ни одна: перевод баллов не останется списанным наполовину Журнал отмены (undo log) хранит прежние версии строк; по нему ROLLBACK и восстановление после сбоя отменяют незафиксированные изменения
Согласованность (Consistency) Транзакция переводит базу из одного корректного состояния в другое: ограничения не нарушены Проверка ограничений PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK при каждой команде; бизнес-правила (сумма баллов не меняется при переводе) обеспечивает приложение
Изоляция (Isolation) Параллельные транзакции видят друг друга так, как разрешает уровень изоляции Многоверсионность (MVCC) для обычного SELECT и блокировки строк для записи и блокирующих чтений
Долговечность (Durability) После COMMIT изменения переживут падение сервера и отключение питания Журнал повтора (redo log) записывается на диск при фиксации; при innodb_flush_log_at_trx_commit = 1 (по умолчанию) — при каждом COMMIT, при 0 или 2 сбой может стоить последней секунды транзакций

Согласованность в ACID отличается от остальных свойств: база проверяет только объявленные ограничения. Правило «сколько списали у Васи, столько пришло Семёну» она не знает. Его соблюдает приложение, а атомарность и изоляция дают ему для этого инструменты. На собеседованиях этот нюанс любят: «какое из свойств ACID обеспечивает не только СУБД?»

3. START TRANSACTION, COMMIT, ROLLBACK и SAVEPOINT

Транзакцию открывает START TRANSACTION (или синоним BEGIN), сохраняет COMMIT, отменяет ROLLBACK. Без явного открытия MySQL работает в режиме автофиксации (autocommit = 1): каждая команда — отдельная транзакция, которая фиксируется сразу после выполнения. Точки сохранения SAVEPOINT позволяют отменить только часть транзакции.

Команда Что делает
START TRANSACTION / BEGIN Открывает транзакцию; автофиксация отключается до её конца
COMMIT Фиксирует все изменения транзакции, они становятся видны другим сессиям
ROLLBACK Отменяет все изменения транзакции
SAVEPOINT имя Ставит точку сохранения внутри транзакции
ROLLBACK TO SAVEPOINT имя Отменяет изменения после точки; транзакция продолжается
RELEASE SAVEPOINT имя Удаляет точку, изменения остаются
SET autocommit = 0 Каждая следующая команда начинает транзакцию, которую нужно закрыть COMMIT или ROLLBACK

Проверить режим текущей сессии можно так:

SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

Перевод баллов в транзакции

Тот же перевод, что в начале урока, но внутри транзакции. Пока не выполнен COMMIT, другие сессии видят у Васи 80 баллов, а у Семёна 20:

START TRANSACTION;
UPDATE user_points SET points = points - 50 WHERE user_id = 2;
UPDATE user_points SET points = points + 50 WHERE user_id = 3;
COMMIT;
SELECT user_id, points FROM user_points;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

+---------+--------+
| user_id | points |
+---------+--------+
|       1 |    100 |
|       2 |     30 |
|       3 |     70 |
+---------+--------+

Если бы сервер упал между двумя UPDATE, при перезапуске InnoDB откатил бы незафиксированное списание по журналу отмены — у Васи снова было бы 80.

ROLLBACK: отменить всё

Команда, которая обнулила бы баллы у всех, внутри транзакции безопасна до COMMIT:

START TRANSACTION;
UPDATE user_points SET points = 0;
SELECT SUM(points) FROM user_points;
ROLLBACK;
SELECT SUM(points) FROM user_points;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

+-------------+
| SUM(points) |
+-------------+
|           0 |
+-------------+

Query OK, 0 rows affected (0.00 sec)

+-------------+
| SUM(points) |
+-------------+
|         200 |
+-------------+

Внутри транзакции сессия видит свои незафиксированные изменения (сумма 0), после отката — прежние 200. Как этим пользоваться при ручных UPDATE и DELETE, показано в уроке DML: INSERT, UPDATE и DELETE.

SAVEPOINT: откат части транзакции

Начислим Кириллу 10 бонусных баллов, затем ошибочно начислим ещё и Васе с Семёном и отменим только вторую команду:

START TRANSACTION;
UPDATE user_points SET points = points + 10 WHERE user_id = 1;
SAVEPOINT bonus_kirill;
UPDATE user_points SET points = points + 10 WHERE user_id IN (2, 3);
ROLLBACK TO SAVEPOINT bonus_kirill;
COMMIT;
SELECT user_id, points FROM user_points;
+---------+--------+
| user_id | points |
+---------+--------+
|       1 |    110 |
|       2 |     30 |
|       3 |     70 |
+---------+--------+

ROLLBACK TO SAVEPOINT не закрывает транзакцию: после него нужен COMMIT или полный ROLLBACK. Точки сохранения пригодятся в длинной обработке, где сбой одного шага не должен отменять уже сделанную работу.

Неявный COMMIT

Некоторые команды фиксируют открытую транзакцию сами. Это все команды DDL (CREATE TABLE, ALTER TABLE, DROP, TRUNCATE) — подробно в уроке DDL в SQL. Неявно фиксирует транзакцию и повторный START TRANSACTION: вложенных транзакций в MySQL нет, новая команда сначала сохраняет всё, что было сделано в предыдущей. То же делает SET autocommit = 1, если транзакция была открыта.

Ошибка не откатывает транзакцию

Когда команда внутри транзакции завершается ошибкой, MySQL отменяет только эту команду. Всё, что было сделано до неё, остаётся в транзакции. Переведём 100 баллов от Васи (у него 30) Семёну, начав с зачисления:

START TRANSACTION;
UPDATE user_points SET points = points + 100 WHERE user_id = 3;
UPDATE user_points SET points = points - 100 WHERE user_id = 2;
SELECT user_id, points FROM user_points;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ERROR 3819 (HY000): Check constraint 'chk_points_non_negative' is violated.

+---------+--------+
| user_id | points |
+---------+--------+
|       1 |    110 |
|       2 |     30 |
|       3 |    170 |
+---------+--------+

Списание отклонено, а зачисление осталось: у Семёна 170. Транзакция всё ещё открыта, и COMMIT сейчас зафиксировал бы 100 баллов, взявшихся ниоткуда. Правильное действие после ошибки — ROLLBACK:

ROLLBACK;

Откат при ошибке — забота приложения

MySQL сам откатывает всю транзакцию только в одном частом случае — при дедлоке (ошибка 1213, раздел 7). При нарушении ограничения, ошибке синтаксиса или таймауте блокировки (1205) отменяется одна команда. Поэтому в Java rollback() вызывают в блоке catch — см. раздел 8.

Транзакции работают только с таблицами InnoDB: изменения в таблице на движке MyISAM записываются сразу, и ROLLBACK их не вернёт, а лишь выдаст предупреждение 1196 Some non-transactional changed tables couldn't be rolled back.

4. Аномалии: потерянное обновление, грязное, неповторяемое и фантомное чтение

Аномалии параллельного доступа — ошибки, которые возникают, когда две транзакции одновременно читают и меняют одни данные. Их четыре классических вида: потерянное обновление (lost update), грязное чтение (dirty read), неповторяемое чтение (non-repeatable read) и фантомное чтение (phantom read). Какие из них возможны, определяет уровень изоляции транзакции.

Текущее состояние: у темы 1 — 10 просмотров, у темы 2 — 20; баллы — 110, 30 и 70. Примеры ниже выполняются в двух сессиях по шагам.

Потерянное обновление (lost update)

Две транзакции прочитали одно значение, каждая посчитала новое у себя и записала его — второе изменение затёрло первое. Так ведёт себя код «прочитать views в Java, прибавить 1, записать». Обе сессии работают на уровне по умолчанию, REPEATABLE READ:

Шаг Сессия A Сессия B
1 START TRANSACTION;
SELECT views FROM topics WHERE id = 1; → 10
2 START TRANSACTION;
SELECT views FROM topics WHERE id = 1; → 10
3 UPDATE topics SET views = 11 WHERE id = 1; → Changed: 1
4 UPDATE topics SET views = 11 WHERE id = 1; → ждёт блокировку строки
5 COMMIT; Команда шага 4 выполнилась: Rows matched: 1 Changed: 0
6 COMMIT; → в базе views = 11, хотя просмотров было два

Блокировка строки заставила B подождать, но не спасла: B записывает значение, посчитанное по устаревшему чтению. Changed: 0 на шаге 5 — MySQL увидел, что в строке уже 11. В PostgreSQL на уровне REPEATABLE READ сессия B получила бы ошибку could not serialize access due to concurrent update, а InnoDB такой проверки не делает. Два способа исправления: атомарный UPDATE topics SET views = views + 1 WHERE id = 1 (разобран в уроке про UPDATE) или блокирующее чтение SELECT ... FOR UPDATE из раздела 6.

Грязное чтение (dirty read)

Транзакция читает изменения другой транзакции, которые ещё не зафиксированы и могут быть отменены. Возможно только на уровне READ UNCOMMITTED, поэтому сессия B сначала переключается на него:

Шаг Сессия A Сессия B
1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2 START TRANSACTION;
UPDATE user_points SET points = points + 50 WHERE user_id = 2;
3 SELECT points FROM user_points WHERE user_id = 2; → 80
4 ROLLBACK;
5 SELECT points FROM user_points WHERE user_id = 2; → 30

На шаге 3 B увидела 80 баллов, которых у Васи никогда не было: если по этому значению разрешить покупку за 60 баллов, деньги потрачены из воздуха. На любом уровне выше READ UNCOMMITTED шаг 3 вернул бы 30.

Неповторяемое чтение (non-repeatable read)

Транзакция дважды читает одну и ту же строку и получает разные значения, потому что между чтениями другая транзакция изменила строку и зафиксировала изменения. Сессия B работает на READ COMMITTED:

Шаг Сессия A Сессия B
1 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT views FROM topics WHERE id = 2; → 20
2 UPDATE topics SET views = views + 1 WHERE id = 2; (автофиксация)
3 SELECT views FROM topics WHERE id = 2; → 21
4 COMMIT;

Отчёт, который на шаге 1 посчитал одно, а на шаге 3 — другое, внутри одной транзакции противоречит сам себе. На уровне REPEATABLE READ шаг 3 вернул бы 20.

Фантомное чтение (phantom read)

Транзакция дважды выполняет запрос с одним условием и получает разный набор строк: другая транзакция вставила (или удалила) строку, подходящую под условие. Отличие от неповторяемого чтения в том, что меняется не значение известной строки, а состав выборки. Сессия B всё ещё на READ COMMITTED:

Шаг Сессия A Сессия B
1 START TRANSACTION;
SELECT COUNT(*) FROM topics WHERE author_id = 1; → 1
2 INSERT INTO topics (title, author_id) VALUES ('Зимняя рыбалка', 1);
3 SELECT COUNT(*) FROM topics WHERE author_id = 1; → 2
4 COMMIT;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Новая тема получила id = 3 и появилась в выборке B как «фантом». В InnoDB на уровне REPEATABLE READ шаг 3 вернул бы 1 — почему, разобрано в следующем разделе. Последняя команда шага 4 возвращает сессии B уровень по умолчанию.

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

Уровень изоляции определяет, какие изменения параллельных транзакций видит текущая. Стандарт SQL задаёт четыре уровня от слабого к строгому: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE. Чем строже уровень, тем меньше аномалий и тем больше ожиданий блокировок. В MySQL с InnoDB по умолчанию действует REPEATABLE READ, в PostgreSQL, Oracle и SQL Server — READ COMMITTED.

Уровень Грязное чтение Неповторяемое чтение Фантомное чтение Потерянное обновление в InnoDB
READ UNCOMMITTED Возможно Возможно Возможно Возможно
READ COMMITTED Нет Возможно Возможно Возможно
REPEATABLE READ (MySQL по умолчанию) Нет Нет По стандарту возможно; в InnoDB обычный SELECT фантомов не видит Возможно
SERIALIZABLE Нет Нет Нет Нет: одна из транзакций получит дедлок и откатится

Столбец «потерянное обновление» относится к сценарию «прочитать, посчитать, записать» из раздела 4: в InnoDB он защищён только на SERIALIZABLE или явной блокировкой FOR UPDATE.

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

SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
-- только для следующей транзакции этой сессии
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- для всех следующих транзакций сессии
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- для новых сессий сервера (нужна привилегия)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Уровень нельзя сменить посреди транзакции: SET TRANSACTION внутри неё завершится ошибкой ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress. В старых статьях встречается переменная tx_isolation — в MySQL 8.0 её удалили, вместо неё transaction_isolation; в конфигурации сервера уровень задаёт параметр transaction-isolation.

MVCC и согласованное чтение в InnoDB

Обычный SELECT в InnoDB не ставит блокировок и не ждёт чужих транзакций. Он читает снимок (consistent read snapshot): версии строк, зафиксированные к моменту создания снимка, — старые версии берутся из журнала отмены. Этот механизм называется MVCC (multi-version concurrency control).

Уровни отличаются тем, когда создаётся снимок. В READ COMMITTED — заново для каждого SELECT, поэтому второй запрос видит свежие изменения. В REPEATABLE READ — при первом чтении в транзакции (не при START TRANSACTION; сразу создаёт его START TRANSACTION WITH CONSISTENT SNAPSHOT), и все следующие SELECT транзакции читают тот же снимок. Поэтому в REPEATABLE READ не видно ни изменённых, ни новых строк других транзакций — и фантомов при обычном SELECT нет. В SERIALIZABLE InnoDB при отключённой автофиксации превращает каждый обычный SELECT в SELECT ... FOR SHARE.

Команды записи снимок не используют: UPDATE, DELETE и блокирующие чтения работают с последней зафиксированной версией строк. Из-за этого в REPEATABLE READ возможен такой сценарий. Что выведет шаг 5?

Шаг Сессия A Сессия B (REPEATABLE READ)
1 START TRANSACTION;
SELECT COUNT(*) FROM topics WHERE author_id = 1; → 2
2 INSERT INTO topics (title, author_id) VALUES ('Подлёдный лов', 1);
3 SELECT COUNT(*) FROM topics WHERE author_id = 1; → 2
4 UPDATE topics SET views = views + 1 WHERE author_id = 1; → Rows matched: 3 Changed: 3
5 SELECT COUNT(*) FROM topics WHERE author_id = 1; → 3
6 ROLLBACK;

На шаге 3 снимок скрывает тему 4 «Подлёдный лов», но UPDATE на шаге 4 находит три темы Кирилла и меняет все. Строку, изменённую своей транзакцией, InnoDB показывает в снимке, и на шаге 5 фантом появляется. Защита REPEATABLE READ от фантомов распространяется только на чтение, а не на запись. Шаг 6 откатывает увеличение просмотров, тема 4 остаётся в базе.

6. SELECT ... FOR UPDATE и FOR SHARE

Блокирующее чтение — SELECT, который не только читает строки, но и блокирует их до конца транзакции. SELECT ... FOR UPDATE ставит исключающую блокировку: другие транзакции не могут изменить эти строки или заблокировать их сами. SELECT ... FOR SHARE ставит разделяемую: читать с FOR SHARE могут все, а менять — никто. Обычный SELECT другой транзакции эти блокировки не останавливают.

Чтение Блокировка Что не сможет другая транзакция Когда использовать
SELECT Нет (снимок MVCC) Ничего не блокирует Обычное чтение
SELECT ... FOR SHARE Разделяемая (S) UPDATE, DELETE, FOR UPDATE этих строк Убедиться, что строка существует и не изменится, пока вы её используете: например, тема при добавлении сообщения
SELECT ... FOR UPDATE Исключающая (X) UPDATE, DELETE, FOR UPDATE, FOR SHARE этих строк Прочитать значение, чтобы затем его изменить

FOR SHARE появился в MySQL 8.0; старый синтаксис LOCK IN SHARE MODE по-прежнему работает и встречается в старом коде. Повторим сценарий потерянного обновления с блокировкой. У темы 1 сейчас 11 просмотров:

Шаг Сессия A Сессия B
1 START TRANSACTION;
SELECT views FROM topics WHERE id = 1 FOR UPDATE; → 11
2 START TRANSACTION;
SELECT views FROM topics WHERE id = 1 FOR UPDATE; → ждёт
3 UPDATE topics SET views = 12 WHERE id = 1;
COMMIT;
Чтение шага 2 выполнилось → 12
4 UPDATE topics SET views = 13 WHERE id = 1;
COMMIT; → в базе 13

B ждёт на чтении, а не на записи, и получает уже зафиксированное значение 12 — блокирующее чтение даже в REPEATABLE READ берёт последнюю версию строки, а не снимок. Для простого счётчика блокировка избыточна: UPDATE topics SET views = views + 1 решает задачу одной командой. FOR UPDATE нужен, когда между чтением и записью есть логика в приложении: проверить остаток баллов, а потом списать.

Если ждать нельзя, MySQL 8.0 предлагает FOR UPDATE NOWAIT — сразу ошибка 3572 вместо ожидания — и FOR UPDATE SKIP LOCKED, который пропускает занятые строки (так разбирают очередь задач несколькими обработчиками).

FOR UPDATE без транзакции не держит блокировку

В режиме автофиксации SELECT ... FOR UPDATE — отдельная транзакция: блокировка снимается сразу после чтения, и следующий UPDATE уже ничем не защищён. Блокирующее чтение имеет смысл только после START TRANSACTION или setAutoCommit(false).

Какие строки на самом деле блокируются

InnoDB блокирует не строки результата, а записи индекса, которые просмотрел запрос. В REPEATABLE READ к ним добавляются блокировки промежутков между записями — вместе это next-key locks. После SELECT * FROM topics WHERE author_id = 1 FOR UPDATE другая сессия не сможет не только изменить темы Кирилла, но и вставить новую тему с author_id = 1: её место в индексе fk_topics_author попадает в заблокированный промежуток. Так InnoDB защищает от фантомов блокирующие чтения. В READ COMMITTED блокировки промежутков почти не используются — меньше ожиданий, но фантомы возможны.

Отсюда главный подвох: если условие FOR UPDATE или UPDATE не может использовать индекс, InnoDB просматривает и блокирует все строки таблицы — одна транзакция останавливает работу остальных. Почему запрос не использует индекс и как это проверить через EXPLAIN, разобрано в уроке Индексы в SQL.

7. Дедлок и ожидание блокировки

Дедлок (deadlock, взаимная блокировка) — ситуация, когда две транзакции ждут друг друга: каждая держит блокировку, нужную другой. InnoDB обнаруживает дедлок сразу, откатывает одну из транзакций целиком с ошибкой 1213 и даёт второй продолжить. Обычное ожидание без цикла ограничено таймаутом innodb_lock_wait_timeout и заканчивается ошибкой 1205.

Кирилл (110 баллов) и Семён (70) одновременно переводят друг другу по 10 баллов, каждый начинает со своего списания:

Шаг Сессия A (Кирилл → Семён) Сессия B (Семён → Кирилл)
1 START TRANSACTION;
UPDATE user_points SET points = points - 10 WHERE user_id = 1;
2 START TRANSACTION;
UPDATE user_points SET points = points - 10 WHERE user_id = 3;
3 UPDATE user_points SET points = points + 10 WHERE user_id = 3; → ждёт B
4 UPDATE user_points SET points = points + 10 WHERE user_id = 1; → ERROR 1213, транзакция B откачена
5 Команда шага 3 выполнилась
COMMIT;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Перевод A прошёл (баллы стали 100, 30 и 80), перевод B отменён полностью, включая списание на шаге 2. Жертву InnoDB выбирает сам — обычно транзакцию, которая изменила меньше строк, поэтому в вашем запуске ошибку может получить и A. Подробности последнего дедлока показывает SHOW ENGINE INNODB STATUS в секции LATEST DETECTED DEADLOCK.

Ошибка Когда возникает Что откатывается Что делать
ERROR 1213 (40001): Deadlock found when trying to get lock Транзакции ждут друг друга по кругу Вся транзакция Повторить транзакцию с начала
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Блокировку держат дольше innodb_lock_wait_timeout (по умолчанию 50 секунд) Только команда, если не включён innodb_rollback_on_timeout Сделать ROLLBACK и повторить; найти долгую транзакцию, которая держит блокировку

Таймаут можно сократить для сессии: SET SESSION innodb_lock_wait_timeout = 5;. Полностью избежать дедлоков нельзя, но их можно сделать редкими. Транзакции должны быть короткими: никаких HTTP-запросов и ожидания пользователя между START TRANSACTION и COMMIT. Блокировки берут в одном порядке — при переводе баллов всегда сначала обновлять строку с меньшим user_id: тогда в примере выше B ждала бы на шаге 2, а не попала в дедлок. И код должен уметь повторить транзакцию, получившую 1213.

8. Транзакции в JDBC

В JDBC соединение по умолчанию работает в режиме автофиксации. Чтобы выполнить несколько команд в одной транзакции, вызывают connection.setAutoCommit(false), после всех команд — commit(), а при исключении — rollback() в блоке catch. Уровень изоляции задаёт setTransactionIsolation(), точки сохранения — setSavepoint().

Подключение к MySQL и выполнение запросов через PreparedStatement разобраны в уроке Работа с JDBC. Перевод баллов (сейчас 100, 30 и 80):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PointsTransfer {
    private static final String URL = "jdbc:mysql://localhost:3306/forum";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "root", "password")) {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            transfer(conn, 1, 3, 30);   // Kirill -> Semen
            transfer(conn, 2, 3, 500);  // у Васи только 30 баллов
        }
    }

    static void transfer(Connection conn, int fromId, int toId, int amount) throws SQLException {
        conn.setAutoCommit(false);
        try (PreparedStatement debit = conn.prepareStatement(
                     "UPDATE user_points SET points = points - ? WHERE user_id = ?");
             PreparedStatement credit = conn.prepareStatement(
                     "UPDATE user_points SET points = points + ? WHERE user_id = ?")) {
            debit.setInt(1, amount);
            debit.setInt(2, fromId);
            debit.executeUpdate();

            credit.setInt(1, amount);
            credit.setInt(2, toId);
            credit.executeUpdate();

            conn.commit();
            System.out.println("Перевод " + amount + " баллов: " + fromId + " -> " + toId + " выполнен");
        } catch (SQLException e) {
            conn.rollback();
            System.out.println("Откат: " + e.getMessage());
        } finally {
            conn.setAutoCommit(true);
        }
    }
}
Перевод 30 баллов: 1 -> 3 выполнен
Откат: Check constraint 'chk_points_non_negative' is violated.

После запуска в таблице 70, 30 и 110: первый перевод зафиксирован, второй откачен — списание у Васи нарушило CHECK, и до зачисления дело не дошло. Без rollback() в catch открытая транзакция осталась бы на соединении и зафиксировалась бы следующим commit() вместе с чужими изменениями (см. раздел 3). setAutoCommit(true) в finally возвращает соединение в обычный режим: это важно, если оно вернётся в пул и достанется другому коду.

Точка сохранения в JDBC работает так же, как SAVEPOINT в SQL:

conn.setAutoCommit(false);
// ... начисление бонуса Кириллу
Savepoint afterBonus = conn.setSavepoint("bonus_kirill");   // java.sql.Savepoint
// ... начисление остальным
conn.rollback(afterBonus);   // отменить только начисление остальным
conn.commit();

При дедлоке MySQL Connector/J выбрасывает SQLTransactionRollbackException с getSQLState(), равным "40001": по нему код решает, повторить ли транзакцию. В Spring-приложениях те же вызовы скрывает аннотация @Transactional: фреймворк сам отключает автофиксацию, фиксирует транзакцию при нормальном выходе из метода и откатывает при исключении.

9. Частые ошибки с транзакциями

Большинство ошибок с транзакциями не дают сообщения об ошибке: данные молча фиксируются не тогда, транзакция держит блокировки дольше нужного или «защита» на деле ничего не защищает. Ниже ситуации, на которых чаще всего ловятся при первых транзакциях в MySQL и JDBC.

Ситуация Что происходит Как правильно
После ошибки команды в транзакции выполняют COMMIT Фиксируются команды до ошибки, операция сохраняется наполовину После любой ошибки — ROLLBACK; в Java — rollback() в catch
setAutoCommit(false) без commit() При закрытии соединения MySQL откатывает незафиксированную транзакцию, изменения пропадают Каждая ветка кода заканчивается commit() или rollback()
Чтение в Java, расчёт, запись (views = 11) Потерянное обновление даже в REPEATABLE READ Атомарный UPDATE ... SET views = views + 1 или SELECT ... FOR UPDATE
SELECT ... FOR UPDATE в режиме автофиксации Блокировка снимается сразу после чтения Открыть транзакцию до блокирующего чтения
ALTER TABLE или CREATE INDEX посреди транзакции Неявный COMMIT: всё сделанное фиксируется, ROLLBACK уже не поможет DDL выполнять вне транзакций с данными
Долгая транзакция: внешний HTTP-запрос или ожидание пользователя внутри Другие сессии получают ERROR 1205, растёт журнал отмены Готовить данные до START TRANSACTION, внутри — только SQL
FOR UPDATE или UPDATE с условием по столбцу без индекса Блокируются все строки таблицы Индекс на столбце условия, проверка через EXPLAIN
Нет повтора при ERROR 1213 Пользователь получает ошибку из-за случайного совпадения по времени Повторить транзакцию 2–3 раза при SQLState 40001
Таблица на движке MyISAM ROLLBACK ничего не отменяет, только предупреждение 1196 InnoDB (по умолчанию в MySQL 8)

Часто задаваемые вопросы

Нужна ли транзакция для одного UPDATE или INSERT?

Нет. В режиме автофиксации каждая команда — отдельная транзакция, и InnoDB выполняет её атомарно: многострочный UPDATE или INSERT при ошибке не изменит ни одной строки. Явная транзакция нужна, когда несколько команд должны выполниться вместе, или когда чтение и последующая запись должны видеть одни и те же данные.

Какой уровень изоляции выбрать для Java-приложения на MySQL?

Обычно хватает уровня по умолчанию, REPEATABLE READ, а места, где важна корректность при параллельной записи, защищают SELECT ... FOR UPDATE или атомарным UPDATE. Многие проекты переключают MySQL на READ COMMITTED: без блокировок промежутков меньше ожиданий и дедлоков, а поведение совпадает с PostgreSQL. Учтите, что при включённом бинарном журнале READ COMMITTED требует binlog_format = ROW — он и так по умолчанию в MySQL 8.

Что будет с транзакцией, если соединение оборвалось?

Сервер MySQL откатывает незафиксированную транзакцию разорванного соединения и снимает её блокировки. Ограничения на длительность транзакции нет, поэтому зависшее соединение с открытой транзакцией может держать блокировки часами. Найти такие транзакции помогает таблица information_schema.INNODB_TRX (столбец trx_started).

Чем транзакции в PostgreSQL отличаются от MySQL?

По умолчанию PostgreSQL работает на READ COMMITTED. DDL в PostgreSQL транзакционен и откатывается. После ошибки внутри транзакции PostgreSQL отклоняет все следующие команды (current transaction is aborted) до ROLLBACK — MySQL же продолжает транзакцию. На уровне REPEATABLE READ PostgreSQL вместо потерянного обновления выдаёт ошибку сериализации, которую нужно обработать повтором.

Как увидеть свежие данные внутри транзакции REPEATABLE READ?

Обычный SELECT читает снимок, созданный при первом чтении, и свежих изменений не покажет. Последнюю зафиксированную версию строки возвращают блокирующие чтения SELECT ... FOR SHARE и FOR UPDATE; другой путь — закончить транзакцию и начать новую. Как создаётся снимок — в разделе MVCC и согласованное чтение.

Комментарии

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