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

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

Если Вы программируете интернет-магазин, форум, что угодно с большой посещаемостью, то вполне вероятно, что 2 пользователя обратятся к одним и тем же данным в один момент. Хорошо, если обратятся они только с чтением, а если они что-то хотят записать туда одновременно? И в этот же момент третий пользователь попытается прочитать эти данные? Как видите неуправляемый доступ к данным чреват проблемами искажения информации. Именно для этих ситуаций было создано понятие транзакций.

Самое главное, что нужно знать про них, что они либо выполняются полностью либо не выполняются совсем. В транзакцию Вы можете добавить массу различных команд и СУБД будет воспринимать это как единую транзакцию. Другие транзакции в этот момент либо будут ждать, либо получат отказ.

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

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

Это можно остановить командой

set autocommit=0;

Или включить обратно

set autocommit=1;

Проблемы параллельного доступа

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

– потерянные обновления (когда вторая транзакция начинается посреди первой, а они пишут и читают одни и те же данные – возникают искажения в записи / чтении);

–“грязное чтение” – первая транзакция ещё не зафиксирвала изменения в физической БД, а вторая уже начала чтение (по сути более частный случай потерянных обновлений);

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

–чтение строк фантомов… (вклинивание в длительную транзакцию и искажение)

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

Транзакция A дважды выполняет выборку строк с одним и тем же условием. Между выборками вклинивается транзакция B, которая добавляет новую строку, удовлетворяющую условию отбора.

Транзакция A	Время	Транзакция B
Выборка строк, удовлетворяющих условию. (Отобрано n строк)		—
—		Вставка новой строки, удовлетворяющей условию.
—		Фиксация транзакции
Выборка строк, удовлетворяющих условию. (Отобрано n+1 строк)		—
Фиксация транзакции		—
Появились строки, которых раньше не было

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

Результат. Транзакция A в двух одинаковых выборках строк получила разные результаты.

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

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

Read uncommitted (незафиксированное чтение) – наименее защищенный уровень транзакций. Этот уровень рекомендуется исопльзовать только в тех случаях, когда все транзакции работают в режиме чтения.

Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки.

Repeatable read (повторяемое чтение) – накладывает блокировки на обрабатываемые транзакцией строки и не допускает их изменение другими транзакциями, но не запрещает добавление новых записей, что может привести к появлению строк-фантомов. По умолчанию стоит для всех транзакций.

Serializable (сериализуемость) – самый надежный уровень изоляции, полностью исключающий взаимное влияние транзакций.

Синтаксис в MySQL будет выглядеть таким образом…

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

При этом, если, например, написать инструкцию таким образом
Set transaction isolation level serializable;

То она будет справедлива только для одной последующей транзакции. Когда я попробовал ввести её, а потом просмотреть командой (<span class="keyword">SHOW</span> VARIABLES <span class="keyword">LIKE</span> <span class="string">'%tx_isolation%'</span>;) какой у меня установлен уровень изоляции, то система мне выдала REPEATABLE READ, как будто я ничего и не менял... Видимо пока недостаточно знаю об этом вопросе...

Но, когда я добавил в инструкцию слово SESSION и вновь просмотрел уровень изоляции, то всё встало как нужно.

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

При этом, если, например, написать инструкцию таким образом

Set transaction isolation level serializable;

То она будет справедлива только для одной последующей транзакции. Когда я попробовал ввести её, а потом просмотреть командой (SHOW VARIABLES LIKE '%tx_isolation%';) какой у меня установлен уровень изоляции, то система мне выдала REPEATABLE READ, как будто я ничего и не менял... Видимо пока недостаточно знаю об этом вопросе...

Но, когда я добавил в инструкцию слово SESSION и вновь просмотрел уровень изоляции, то всё встало как нужно.

Прочитав описание на MySQL.ru, я обнаружил такую вещь…

По умолчанию уровень изоляции устанавливается для последующей (не начальной) транзакции. При использовании ключевого слова GLOBAL данная команда устанавливает уровень изоляции по умолчанию глобально для всех новых соединений, созданных от этого момента. Однако для того чтобы выполнить данную команду, необходима привилегия SUPER. При использовании ключевого слова SESSION устанавливается уровень изоляции по умолчанию для всех будущих транзакций, выполняемых в текущем соединении.

Как использовать транзакции в MySQL?

Синтаксис будет таким…

set autocommit=0;

Start transaction; (также, можно написать BEGIN; )

…какие-то действия с БД (insert, update,delete…)

commit; // подтверждаем серию действий, производим запись в физическую БД

Обратите внимание, в “куске” таблицы сверху, видно, что последняя запись под номером 22, а в нижней таблице – под номером 24. То есть, запись всё-таки произошла! Теперь попробуем инструкцию rollback, то есть отмену транзакции..

Либо другой вариант…

set autocommit=0;

Start transaction;

…какие-то действия с БД (insert, update,delete…)

rollback; // отменяем серию действий, не производим запись в физическую БД

Обратите внимание, верхняя и нижняя таблицы с последним номером записи 24! То есть механизм rollback сработал как нужно и откатил изменения транзакции!!!

Но, если мы не можем сделать rollback при наличии того или иного условия, то транзакции не будут настолько эффективными, насколько это возможно.

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

(Забыл отобразить на рисунке команду Delimiter//)

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

Сommit и если что не так rollback средствами MySQL

Пока искал как сделать такую конструкцию, понял, что готовые ответы не всегда существуют. Поэтому, средствами mysql сделал такую конструкцию самостоятельно. То есть, делаем что-то, и если ошибка то откат, если все Ок, то запись в физическую базу данных.

Оформил всё в виде хранимой процедуры таким образом…

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

Вызываем процедуру с корректными данными…

Строка добавилась успешно… Теперь вызываем процедуру с некорректными данными…

Процедура успешно откатилась, дав запрограммированное описание ошибки.

Если только транзакция, без rollback;

Поставил эксперимент, убрав секцию DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;

При провоцировании ошибки, часть инструкций, которые уже выполнились до ошибки внутри транзакции – всё равно оставались в физической базе. Я пробовал с включенным set autocommit=1 и выключенным set autocommit=0;

Вывод

Чтобы откат транзакции работал корректно, на языке MySQL, необходимо, внутри хранимой процедуры обрабатывать секцию

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;

Вот примеры обработки секции, которые мне понравились. Примеры с сайта

CREATE PROCEDURE prc_test()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;
  START TRANSACTION;
    INSERT
    INTO t_test VALUES ('test', 'test');
    INSERT
    INTO no_such_table
    VALUES ('no');
  COMMIT;
END;

CALL prc_test();

SELECT *
FROM t_test;

0 rows fetched.

CREATE PROCEDURE prc_test()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

END;

START TRANSACTION;

INSERT

INTO t_test VALUES ('test', 'test');

INSERT

INTO no_such_table

VALUES ('no');

COMMIT;

END;

CALL prc_test();

SELECT *

FROM t_test;

0 rows fetched.

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
    UPDATE foo SET bar = 3;
    UPDATE bar SET thing = 5;
  COMMIT;
END;

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

START TRANSACTION;

UPDATE foo SET bar = 3;

UPDATE bar SET thing = 5;

COMMIT;

END;

[block id=”mysql-first-steps”]

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

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

Проблемы параллельного доступа

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

Как использовать транзакции в MySQL?

Сommit и если что не так rollback средствами MySQL

Вывод

MY PROJECTS

Recent Posts

Categories