Транзакции MySQL

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

В MySQL транзакция — это выполнение набора операторов SQL, которые рассматриваются как единая единица работы.

  • В MySQL только базы данных или таблицы, использующие движок базы данных InnoDB, поддерживают транзакции.
  • Обработка транзакций может использоваться для поддержания целостности базы данных, гарантируя, что пакет операторов SQL либо выполняется весь, либо не выполняется вовсе.
  • Транзакции используются для управленияinsert, update, deleteоператорами

Обычно транзакция должна удовлетворять 4 условиям (ACID): Атомарность (Aатомарность, или неделимость), Согласованность (Cсогласованность), Изоляция (Iизоляция, также известная как независимость), Долговечность (Dдолговечность).

  • Атомарность:Все операции в транзакции (транзакция) должны либо все выполниться, либо не выполниться вовсе, и транзакция не должна завершаться на каком-либо промежуточном этапе. Если во время выполнения транзакции происходит ошибка, она откатывается к состоянию до начала транзакции, как если бы транзакция никогда не выполнялась.

  • Согласованность:Целостность базы данных не нарушается до начала транзакции и после её завершения. Это означает, что записанные данные должны полностью соответствовать всем предустановленным правилам, включая точность и связность данных, а также способность базы данных самостоятельно выполнить запланированную работу впоследствии.

  • Изоляция:Способность базы данных позволять множеству параллельных транзакций одновременно читать, записывать и изменять её данные. Изоляция может предотвратить несогласованность данных, вызванную перекрёстным выполнением при одновременном запуске нескольких транзакций. Изоляция транзакций делится на разные уровни, включая Read uncommitted, Read committed, Repeatable read и Serializable.

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

В настройках по умолчанию командной строки MySQL транзакции автоматически фиксируются, то есть после выполнения SQL-инструкции немедленно выполняется операция COMMIT. Поэтому, чтобы явно начать транзакцию, необходимо использовать команду BEGIN или START TRANSACTION либо выполнить команду SET AUTOCOMMIT=0, чтобы отключить автофиксацию для текущего сеанса.

Операторы управления транзакциями:

  • BEGIN или START TRANSACTION явно начинают транзакцию;

  • COMMIT, или COMMIT WORK, хотя они эквивалентны. COMMIT фиксирует транзакцию и делает все изменения, внесённые в базу данных, постоянными;

  • ROLLBACK, или ROLLBACK WORK, хотя они эквивалентны. Откат завершает транзакцию пользователя и отменяет все незафиксированные изменения, выполняемые в данный момент;

  • SAVEPOINT identifier. SAVEPOINT позволяет создать точку сохранения в транзакции, и транзакция может иметь несколько SAVEPOINT;

  • RELEASE SAVEPOINT identifier удаляет точку сохранения транзакции. Если указанная точка сохранения не существует, выполнение этого оператора вызовет исключение;

  • ROLLBACK TO identifier откатывает транзакцию к отмеченной точке;

  • SET TRANSACTION используется для установки уровня изоляции транзакции. Механизм хранения InnoDB предоставляет уровни изоляции транзакций, включая READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE.

Существует в основном два способа обработки транзакций в MySQL:

1. Реализация с помощью BEGIN, ROLLBACK, COMMIT

  • BEGIN или START TRANSACTION: используется для начала транзакции.
  • ROLLBACKОткат транзакции, отмена предыдущих изменений.
  • COMMIT: Подтверждение транзакции, фиксация транзакции, делающая изменения постоянными.

2. Прямо используйте SET для изменения режима auto-commit в MySQL:

  • SET AUTOCOMMIT=0Отключить auto-commit
  • SET AUTOCOMMIT=1Включить auto-commit

BEGIN или START TRANSACTION-- Используется для начала транзакции:

BEGIN; -- 或者使用 START TRANSACTION;

COMMIT-- Используется для фиксации транзакции, постоянного сохранения всех изменений в базе данных:

COMMIT;

ROLLBACK-- Используется для отката транзакции, отменяя все изменения, сделанные с момента последней фиксации:

ROLLBACK;

SAVEPOINT-- Используется для установки точки сохранения внутри транзакции, чтобы позже можно было откатиться к этой точке:

SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT-- Используется для отката к ранее установленной точке сохранения:

ROLLBACK TO SAVEPOINT savepoint_name;

Ниже приведён простой пример транзакции MySQL:

Пример

-- Начать транзакцию
START TRANSACTION;

-- Выполнить несколько SQL-инструкций
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Определить, зафиксировать или откатить
IF (Condition) THEN
    COMMIT; -- Зафиксировать транзакцию
ELSE
    ROLLBACK; -- Откатить транзакцию
END IF;

Пример

Простой пример транзакции:

Тест транзакции

mysql> use EXAMPLE; База данных изменена mysql> CREATE TABLE example_transaction_test( id int(5)) engine=innodb; # Создать таблицу Запрос OK, 0 строк затронуто (0.04 sec) mysql> select * from example_transaction_test; Пустой набор (0.01 sec) mysql> begin; # Начать транзакцию Запрос OK, 0 строк затронуто (0.00 sec) mysql> insert into example_transaction_test value(5); Запрос OK, 1 строк затронуто (0.01 sec) mysql> insert into example_transaction_test value(6); Запрос OK, 1 строк затронуто (0.00 sec) mysql> commit; # Зафиксировать транзакцию Запрос OK, 0 строк затронуто (0.01 sec) mysql> select * from example_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows в наборе (0.01 sec) mysql> begin; # Начать транзакцию Запрос OK, 0 строк затронуто (0.00 sec) mysql> insert into example_transaction_test values(7); Запрос OK, 1 строк затронуто (0.00 sec) mysql> rollback; # Откат Запрос OK, 0 строк затронуто (0.00 сек) mysql> select * from example_transaction_test; # Данные не были вставлены из-за отката +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows в наборе (0.01 sec) mysql>

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

Тест MySQL ORDER BY:

<?php $dbhost = 'localhost'; //адрес хоста сервера MySQL $dbuser = 'root'; //имя пользователя MySQL $dbpass = '123456'; //Пароль пользователя MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Ошибка подключения:' . mysqli_error($conn)); } //Установите кодировку, чтобы предотвратить появление искажённых китайских символов mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'EXAMPLE' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); //Установите, чтобы отключить автофиксацию, потому что MySQL по умолчанию выполняет сразу mysqli_begin_transaction($conn); //Определение начала транзакции if(!mysqli_query($conn, "insert into example_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); //Определить откат при сбое выполнения } if(!mysqli_query($conn, "insert into example_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); //Определить откат при сбое выполнения } mysqli_commit($conn); //Выполнить транзакцию mysqli_close($conn); ?>
Другие расширения