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

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

Скачать PDF

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

Свойства транзакций (ACID)

Транзакции обладают четырьмя основными свойствами, известными как ACID:

• Атомарность (Atomicity): транзакция выполняется полностью или не выполняется вообще.
• Согласованность (Consistency): транзакция переводит базу данных из одного согласованного состояния в другое.
• Изолированность (Isolation): результаты выполнения транзакции не видны другим транзакциям до её завершения.
• Долговечность (Durability): после завершения транзакции изменения сохраняются в базе данных даже в случае сбоя системы.

Поддержка транзакций в MySQL

В MySQL транзакции поддерживаются не всеми типами таблиц. Наиболее распространенный тип таблиц с поддержкой транзакций — InnoDB. MyISAM не поддерживает транзакции.

Основные команды для работы с транзакциями

START TRANSACTION или BEGIN

Начинает новую транзакцию.

START TRANSACTION;
-- или
BEGIN;

COMMIT

Фиксирует изменения, сделанные в текущей транзакции.

COMMIT;

ROLLBACK

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

ROLLBACK;

SAVEPOINT

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

SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT

Отменяет изменения до указанной точки сохранения.

ROLLBACK TO SAVEPOINT savepoint_name;

RELEASE SAVEPOINT

Удаляет точку сохранения.

RELEASE SAVEPOINT savepoint_name;

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

-- Начало транзакции
START TRANSACTION;

-- Операции с базой данных
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 456;

-- Проверка условия
SELECT @balance := balance FROM accounts WHERE account_id = 123;

IF @balance < 0 THEN
    -- Если баланс отрицательный, отменяем транзакцию
    ROLLBACK;
ELSE
    -- Если всё в порядке, фиксируем изменения
    COMMIT;
END IF;

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

MySQL поддерживает четыре уровня изоляции транзакций, которые определяют, как транзакции взаимодействуют друг с другом:

READ UNCOMMITTED

Самый низкий уровень изоляции. Транзакция может видеть незафиксированные изменения, сделанные другими транзакциями ("грязное чтение").

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ COMMITTED

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ

Уровень изоляции по умолчанию в InnoDB. Гарантирует, что если транзакция дважды выполняет одну и ту же выборку, она получит одинаковые результаты, даже если другие транзакции изменили данные и зафиксировали изменения. Однако, возможны "фантомные чтения", когда новые строки появляются в результате выборки.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SERIALIZABLE

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

Для текущей сессии

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Глобально для всех новых сессий

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Для конкретной транзакции

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- операции с базой данных
COMMIT;

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

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

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

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

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

Фантомное чтение (Phantom Read)

Транзакция выполняет запрос, который возвращает набор строк, удовлетворяющих условию. Другая транзакция вставляет новые строки, которые также удовлетворяют этому условию. Если первая транзакция повторяет запрос, она увидит дополнительные "фантомные" строки.

Блокировки в транзакциях

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

Блокировка строк (Row-level Locking)

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

Блокировка таблиц (Table-level Locking)

MyISAM использует блокировку на уровне таблиц, что означает, что одновременно только одна транзакция может изменять таблицу.

Явная блокировка

Можно явно блокировать строки для чтения или записи:

-- Блокировка для чтения
SELECT * FROM table_name WHERE condition FOR SHARE;

-- Блокировка для записи
SELECT * FROM table_name WHERE condition FOR UPDATE;

Автоматическая фиксация транзакций

По умолчанию MySQL работает в режиме автоматической фиксации (autocommit), когда каждый SQL-запрос рассматривается как отдельная транзакция и автоматически фиксируется.

Проверка статуса autocommit

SELECT @@autocommit;

Включение/выключение autocommit

SET autocommit = 1; -- включить
SET autocommit = 0; -- выключить

Заключение

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