Базы данных Техническое интервью

Транзакции в MySQL

11 Ноября 2024 (ред)

Транзакция представляет собой группу операторов SQL, которые обрабатываются атомарно, то есть как цельная единица проведённой работы. Если механизм базы данных может применить всю группу операторов к базе данных, он делает это, но если какой-либо из операторов не может быть выполнен из-за сбоя или по другой причине, ни один из них не применяется.

Управление транзакциями

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

START TRANSACTION
- Эта команда обозначает начало новой транзакции. Все последующие команды DML (Data Manipulation Language), такие как INSERT, UPDATE или DELETE, будут рассматриваться как часть этой транзакции. Это позволяет управлять ее поведением и контролировать, когда изменения должны быть сохранены или отменены.

   START TRANSACTION;

COMMIT
- Команда COMMIT используется для сохранения всех изменений, произведенных в рамках текущей транзакции. После выполнения этой команды все изменения становятся постоянными и видимыми для других транзакций. Этот оператор завершает транзакцию успешно.

   COMMIT;

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

   ROLLBACK;

SAVEPOINT
- Команда SAVEPOINT создает точку в рамках текущей транзакции, к которой можно откатиться позже (если это необходимо). Это особенно полезно, если вы хотите откатить часть транзакции, не отменяя ее полностью. Вы можете создать несколько точек сохранения в одной транзакции.

   SAVEPOINT my_savepoint;

Чтобы откатиться к этой точке, используйте команду ROLLBACK TO SAVEPOINT.

   ROLLBACK TO SAVEPOINT my_savepoint;

SET TRANSACTION
- Команда SET TRANSACTION используется для установки параметров текущей транзакции. Например, можно указать уровень изоляции транзакции, который будет определять, как данные могут быть прочитаны другими транзакциями. Эта команда позволяет задавать параметры, определяющие поведение транзакции, такие как уровень изоляции, и предоставляет возможности для более детального управления процессом обработки данных в транзакциях.

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Применение команд управления транзакциями

Команды управления транзакциями используются только для DML-команд, таких как INSERT, UPDATE, DELETE. Это связано с тем, что эти команды изменяют данные в базе данных. Транзакции не применимы к DDL-командам (Data Definition Language), таким как CREATE, DROP, или ALTER, поскольку они изменяют структуру базы данных, а не содержимое данных.

Пример использования транзакций

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

START TRANSACTION;

-- Снять средства с одного счёта
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;

-- Проверка на достаточность средств
IF (SELECT balance FROM accounts WHERE account_id = 1) < 0 THEN
    ROLLBACK; -- Откатить транзакцию, если недостаточно средств
ELSE
    -- Зачислить средства на другой счёт
    UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
    COMMIT; -- Зафиксировать изменения
END IF;

Подсистемы хранения

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

Ведение журнала транзакций

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

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

AUTOCOMMIT

По умолчанию одиночная команда INSERT, UPDATE или DELETE неявно включается в транзакцию и сразу же подтверждается. Этот процесс называется режимом AUTOCOMMIT. Отключив данный режим, вы можете выполнить серию команд внутри транзакции и в конце — выполнить COMMIT или ROLLBACK.

Вы можете активировать или деактивировать переменную AUTOCOMMIT для текущего соединения при помощи команды SET. Значения 1 и ON считаются эквивалентными, как и 0 и OFF. Когда вы работаете с AUTOCOMMIT=0, вы всегда находитесь в состоянии транзакции, пока не выполните COMMIT или ROLLBACK. После этого MySQL немедленно инициирует новую транзакцию. Кроме того, при активированном AUTOCOMMIT вы можете начать транзакцию с несколькими командами, используя ключевое слово BEGIN или START TRANSACTION. Изменение значения AUTOCOMMIT не влияет на нетранзакционные таблицы, которые не имеют представления о подтверждении или отмене изменений.

Некоторые команды, выполняемые в открытой транзакции, вынуждают MySQL подтвердить транзакцию перед их исполнением. Обычно это команды языка определения данных (Data Definition Language, DDL), которые осуществляют значительные изменения, такие как ALTER TABLE, но команды LOCK TABLES и некоторые другие операторы также обладают этой характеристикой. Обратитесь к документации вашей версии для получения полного списка команд, автоматически фиксирующих транзакцию.

MySQL предоставляет возможность установить уровень изоляции с помощью команды SET TRANSACTION ISOLATION LEVEL, которая вступает в силу при запуске следующей транзакции. Вы можете настроить уровень изоляции для всего сервера в конфигурационном файле или только для вашей сессии:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Рекомендуется настраивать уровень изоляции, который вы используете наиболее часто, на уровне сервера и изменять его только в явных случаях. MySQL распознает все четыре стандартных уровня изоляции ANSI, и InnoDB поддерживает их все.

Взаимоблокировки

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

По мотивам источника: "MySQL по максимуму" 4-е издание, Ботрос С., Тинли Дж.

Подробнее в документации: http://www.mysql.ru/docs/man/ANSI_diff_Transactions.html

Транзакции с примерами: https://www.garb.ru/blog/transaction.html