Материал: Письменные лекции по дисциплине «Базы данных»
Вы можете установить или отключить режим AUTOCOMMIT для
текущего соединения, задав значение переменной:
mysql> SHOW VARIABLES LIKE 'autocommit';
+ |
--------------- |
+------- |
+ |
| Variable_name | Value |
| |
||
+--------------- |
|
+------- |
+ |
| autocommit |
| ON |
| |
|
+--------------- |
|
+------- |
+ |
1 |
row in set (0.00 sec) |
|
|
mysql> SET AUTOCOМMIT = |
1; |
||
Значения 1 и ON эквивалентны, так же как 0 и OFF.
Транзакция начинается со специального запроса «START TRANSACTION», либо «BEGIN». Чтобы закончить транзакцию, нужно либо зафиксировать изменения (запрос COMMIT), либо откатить их (запрос
ROLLBACK).
Пример с COMMIT:
set autocommit=0; // Отключаем autocommit
start transaction; (также, можно написать BEGIN) …какие-то действий с БД (insert, update,delete…) commit; // Фиксация действий, запись их в физическую БД
Пример с ROLLBACK:
set autocommit=0; // Отключаем autocommit start transaction;
…какие-то действия с БД (insert, update,delete…)
rollback; // Отменяем серию действий, не производим запись в физическую БД
В MySQL не существует механизма вложенных транзакций. Одно соединение с БД — одна транзакция. Новая транзакция в пределах одного соединения может начаться только после завершения предыдущей.
4.2. Операторы транзакции
Для некоторых операторов нельзя выполнить откат с помощью ROLLBACK. Это операторы языка определения данных (Data Definition Language — DDL). Сюда входят запросы CREATE, ALTER, DROP, TRUNCATE,
COMMENT, RENAME.
Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):
ALTER TABLE
DROP DATABASE
LOAD MASTER DATA
SET AUTOCOMMIT = 1
BEGIN
DROP INDEX
LOCK TABLES
START TRANSACTION
CREATE INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
Обратите внимание, что в случае SQL ошибки, транзакция сама по себе не откатится. Обычно ошибки обрабатываются уже с помощью sql wrapper'ов в самом приложении, таких как PHP PDO например. Если вы захотите откатывать изменения в случае ошибки прямо в MySQL, можно создать специальную процедуру и уже в ней выполнять ROLLBACK в
обработчике. Однако так делать крайне не рекомендуется, так как в основном ошибки базы данных обрабатываются с помощью SQL оберток на стороне приложения, таких как PHP PDO например, чтобы оттуда полностью управлять транзакциями.
Рассмотрим практический пример: есть 2 таблицы, пользователи — users и информация о пользователях — user_info. Представим, что нам
нужно либо выполнить 3 запроса к базе данных, либо не выполнять их вообще, так как иначе это приведет к сбоям в работе приложения.
start transaction;
INSERT INTO user (id, nik) VALUES (1, 'nikola');
INSERT |
INTO |
user_info |
(id, |
id_user, |
item_name, |
|||
item_value) VALUES |
(1, |
1, |
'Имя', 'Николай'); |
|
||||
INSERT |
INTO |
user_info |
(id, |
id_user, |
item_name, |
|||
item_value) VALUES |
(2, |
1, |
'Возраст', '24'); |
|
||||
commit; |
|
|
|
|
|
|
|
|
В целом я думаю принцип работы транзакции понятен. Но все не так просто. Существуют проблемы параллельных транзакций. Рассмотрим пример. Представим, что во время выполнения этой транзакции, другой пользователь создал вторую параллельную транзакцию и сделал запрос SELECT * FROM user после того, как в нашей транзакции был выполнен первый запрос «INSERT INTO user (id, nik) VALUES (1, 'nikola')».
Что увидит пользователь второй транзакции? Сможет ли он увидеть вставленную запись даже тогда, когда результаты первой транзакции еще не зафиксировались (не произошел COMMIT)? Или он сможет увидеть изменения только после того, как результаты первой транзакции будут зафиксированы? Оказывается имеют место быть оба варианта. Все зависит от уровня изоляции транзакции.
4.3. Уровни изоляции (изолированности) транзакций
0 — Чтение неподтверждённых данных (грязное чтение) (Read Uncommitted, Dirty Read) — самый низкий уровень изоляции. При этом уровне возможно чтение незафиксированных изменений параллельных транзакций. Как раз в этом случае второй пользователь увидит вставленную запись из первой незафиксированной транзакции. Нет гарантии, что незафиксированная транзакция будет в любой момент откачена, поэтому такое чтение является потенциальным источником ошибок.
1 — Чтение подтверждённых данных (Read Committed) — здесь возможно чтение данных только зафиксированных транзакций. Но на этом уровне существуют две проблемы. В этом режиме строки, которые участвуют в выборке в рамках транзакции, для других параллельных транзакций не блокируются, из этого вытекает проблема №1 — «Неповторяемое чтение» (non-repeatable read) — это ситуация, когда в рамках транзакции происходит несколько выборок (SELECT) по одним и тем же критериям, и между этими выборками совершается параллельная транзакция, которая изменяет данные, участвующие в этих выборках. Так как параллельная транзакция изменила данные, результат при следующей выборке по тем же критериям в первой транзакции будет другой. Проблема №2 — «Фантомное чтение» — этот случай рассмотрен ниже.
2 — Повторяемое чтение (Repeatable Read, Snapshot) — на этом уровне изоляции так же возможно чтение данных только зафиксированных транзакций. Также на этом уровне отсутствует проблема «Неповторяемого чтения», то есть строки, которые участвуют в выборке в рамках транзакции, блокируются и не могут быть изменены другими параллельными транзакциями. Но таблицы целиком не блокируются. Из-за этого остается проблема «фантомного чтения». «Фантомное чтение»
— это когда за время выполнения одной транзакции результат одних и тех же выборок может меняться по причине того, что блокируется не вся таблица, а только те строки, которые участвуют в выборке. Это означает, что параллельные транзакции могут вставлять строки в таблицу, в которой совершается выборка, поэтому два запроса SELECT * FROM table могут дать разный результат в разное время при вставке данных параллельными транзакциями.
3 — Сериализуемый (Serializable) — сериализуемые транзакции. Самый надежный уровень изоляции транзакций, но и при этом самый медленный. На этом уровне вообще отсутствуют какие-либо проблемы
параллельных транзакций, но за это придется платить быстродействием системы, а быстродействие в большинстве случаев крайне важно.
Уровень изоляции |
Возможность |
Возможность |
Возможность |
Блокировка |
|
чернового |
неповторяющ |
фантомного |
чтения |
||
|
чтения |
егося чтения |
чтения |
||
|
|
||||
|
|
|
|
|
|
0. READ |
Да |
Да |
Да |
Нет |
|
UNCOMMITTED |
|
|
|
|
|
1. READ |
Нет |
Да |
Да |
Нет |
|
COMMITTED |
|
|
|
|
|
2. REPEATABLE |
Нет |
Нет |
Да |
Нет |
|
READ |
|||||
|
|
|
|
||
|
|
|
|
|
|
3. SERIALIZABLE |
Нет |
Нет |
Нет |
Да |
|
|
|
|
|
|
|
По умолчанию в MySQL установлен |
уровень изоляции №2 |
||||
(Repeatable Read). Разработчики MySQL не зря сделали по умолчанию именно этот уровень, так как он наиболее удачный для большинства случаев. С первого раза может показаться, что самый лучший вариант №3
— он самый надежный, но на практике вы можете испытать большие неудобства из-за очень медленной работы вашего приложения. Помните, что многое зависит не от того, насколько хорош уровень изоляции транзакций в БД, а от того, как спроектировано ваше приложение. При грамотном программировании, можно даже использовать самый низкий уровень изоляции транзакций — все зависит от особенностей структуры и грамотности разработки вашего приложения. Но не нужно стремиться к самому низкому уровню изоляции — нет, просто если вы используйте не самый защищенный режим, следует помнить о проблемах параллельных транзакций, в этом случае вы не потеряетесь и все сделаете правильно.
SET TRANSACTION — этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса.
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL