ИТ.03 - 26 - Транзакции: введение в MySQL

Введение

Реляционные базы данных проектируются с учётом необходимости сохранения целостности данных. Целостность данных — это полнота, точность и единообразие данных. Для поддержания целостности данных в реляционных БД используется ряд инструментов: первичные ключи, внешние ключи, ограничения и другие механизмы. Однако когда несколько операций должны быть выполнены как единое целое, например, перевод денег между счетами или регистрация заказа с несколькими позициями, простых ограничений недостаточно. В таких случаях на помощь приходят транзакции.

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

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

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

Что такое транзакция?

Транзакция (англ. transaction) — это последовательность операций с базой данных, которая удовлетворяет четырём ключевым требованиям, известным как ACID. С точки зрения пользователя транзакция выглядит как единый, неделимый блок: либо все изменения сохраняются, либо откатываются.

Представьте себе банковский перевод:

Снять 10 000 ₽ со счета А.
Добавить 10 000 ₽ на счет Б.

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

В MySQL транзакции поддерживаются для таблиц, использующих движки InnoDB (по умолчанию) и NDB. Таблицы типа MyISAM не поддерживают транзакции.

Свойства ACID

Все транзакции в БД должны соответствовать требованиям ACID. Этот акроним образован по первым буквам четырёх принципов сохранности данных:

1. Атомарность (Atomicity)

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

Пример: В переводе средств атомарность гарантирует, что не может быть ситуации, когда деньги списаны, но не зачислены.

2. Согласованность (Consistency)

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

Пример: Если в таблице accounts есть ограничение balance >= 0, транзакция, которая пытается установить отрицательный баланс, будет откачена.

3. Изолированность (Isolation)

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

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

4. Долговечность (Durability)

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

Пример: После подтверждения перевода (COMMIT) данные сохраняются на диск, и последующий сбой сервера не приведёт к потере этого перевода.

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

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

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

`START TRANSACTION` (или `BEGIN`)

Начинает новую транзакцию. После этой команды все последующие операции будут частью этой транзакции до тех пор, пока не будет выполнена COMMIT или ROLLBACK.

START TRANSACTION;

`COMMIT`

Фиксирует все изменения, сделанные в рамках текущей транзакции. После выполнения COMMIT изменения становятся постоянными и видимыми для других транзакций.

COMMIT;

`ROLLBACK`

Откатывает все изменения, сделанные в рамках текущей транзакции. База данных возвращается в состояние, которое было на момент начала транзакции.

ROLLBACK;

`SAVEPOINT`

Создает точку сохранения внутри транзакции, к которой можно later откатиться, не отменяя всю транзакцию целиком.

SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT savepoint_name;

Автоматическая фиксация (autocommit)

По умолчанию в MySQL включен режим autocommit = 1. Каждая отдельная SQL-команда выполняется как отдельная транзакция и автоматически фиксируется. Чтобы работать с многооперационными транзакциями, нужно либо отключить autocommit, либо явно использовать START TRANSACTION.

SET autocommit = 0;  -- отключение автоматической фиксации

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

Рассмотрим практический пример — перевод денег между двумя банковскими счетами.

Подготовка таблиц

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    balance DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

INSERT INTO accounts (user_name, balance) VALUES
('Иванов', 50000.00),
('Петров', 30000.00);

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

-- Списание денег у Иванова
UPDATE accounts SET balance = balance - 10000 WHERE user_name = 'Иванов';
-- Зачисление денег Петрову
UPDATE accounts SET balance = balance + 10000 WHERE user_name = 'Петров';

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

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

START TRANSACTION;

-- Проверяем, достаточно ли средств у Иванова
SELECT balance INTO @ivan_balance FROM accounts WHERE user_name = 'Иванов';

IF @ivan_balance >= 10000 THEN
    -- Списание
    UPDATE accounts SET balance = balance - 10000 WHERE user_name = 'Иванов';
    -- Зачисление
    UPDATE accounts SET balance = balance + 10000 WHERE user_name = 'Петров';
    COMMIT;
    SELECT 'Перевод успешно выполнен' AS result;
ELSE
    ROLLBACK;
    SELECT 'Недостаточно средств' AS result;
END IF;

В этом примере оба обновления либо выполнятся вместе, либо будут откачены. Если после START TRANSACTION произойдет сбой, MySQL автоматически выполнит ROLLBACK при переподключении.

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

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

READ UNCOMMITTED — транзакция может читать незафиксированные изменения других транзакций (возможны «грязные» чтения).
READ COMMITTED — транзакция видит только зафиксированные изменения других транзакций.
REPEATABLE READ (уровень по умолчанию в MySQL) — гарантирует, что в рамках одной транзакции повторное чтение одних и тех же данных даст одинаковый результат, даже если другие транзакции изменяли эти данные.
SERIALIZABLE — самый строгий уровень, полностью изолирует транзакции, как если бы они выполнялись последовательно.

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

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

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

Заключение

Транзакции — фундаментальный механизм обеспечения целостности данных в реляционных базах данных. Они гарантируют, что группы операций выполняются атомарно, согласованно, изолированно и долговечно.

Использование транзакций в MySQL позволяет создавать надёжные приложения, особенно в сценариях, где несколько операций должны быть выполнены как единое целое: финансовые операции, регистрация заказов, обновление инвентаря и т.д.

На практике важно:

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

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