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

Банковское приложение является классическим примером, демонстрирующим необходимость транзакций. Представьте банковскую базу данных с двумя табли­цами: checking (текущие счета) и savings (сберегательные счета). Чтобы перевести 200 долларов с текущего счета Джейн на ее сберегательный счет, вам нужно сделать по меньшей мере три шага.

  1. Убедиться, что остаток на ее текущем счете больше 200 долларов.
  2. Вычесть 200 долларов из остатка текущего счета.
  3. Добавить 200 долларов к остатку сберегательного счета.

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

Вы начинаете транзакцию командой START TRANSACTION, а затем либо сохраняете изменения командой COMMIT, либо отменяете их командой ROLLBACK. Код SQL для транзакции может выглядеть следующим образом:

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;

Но сами по себе транзакции - это еще не все. Что произойдет в случае сбоя сервера базы данных во время выполнения четвертой строки? Кто знает... Клиент, вероят­но, потеряет 200 долларов. А если другой процесс вклинится между выполнением строк 3 и 4 и снимет весь остаток с текущего счета? Банк предоставит клиенту кредит 200 долларов, даже не зная об этом.

Транзакций недостаточно, пока система не прошла тест ACID. Аббревиатура ACID расшифровывается как Atomicity, Consistency, Isolation и Durability (атомарность, со­гласованность, изолированность и долговечность). Это тесно связанные критерии, которым должна соответствовать правильно функционирующая система обработки транзакций.

  • Атомарность. Транзакция должна функционировать как единая неделимая ра­бочая единица таким образом, чтобы вся она была либо выполнена, либо отменена. Для атомарных транзакций не существует такого понятия, как частичное выполнение: все или ничего.
  • Согласованность. База данных всегда должна переходить из одного согласован­ного состояния в другое. В нашем примере согласованность гарантирует, что сбой между строками 3 и 4 не приведет к исчезновению с текущего счета 200 долларов. Поскольку транзакция не будет подтверждена, ни одно из изменений не отразится в базе данных.
  • Изолированность. Результаты транзакции обычно невидимы другим транзак­циям, пока она не подтверждена. В нашем примере это гарантирует, что, если программа суммирования остатков на банковских счетах будет запущена после третьей строки перед четвертой, она по-прежнему увидит 200 долларов на те­кущем счете. Когда будем рассматривать уровни изолированности, вы поймете, почему здесь сказано "обычно невидимы".
  • Долговечность. После подтверждения внесенные в ходе транзакции изменения становятся постоянными. Это значит, что они должны быть записаны так, чтобы данные не потерялись при сбое системы. Долговечность, однако, является несколько расплывчатой концепцией, поскольку у нее довольно много уровней. Некоторые стратегии обеспечения долговечности дают более высокие гарантии безопасности, чем другие, и ни одна из них не является надежной на 100 % (если база данных долговечна сама по себе, то каким образом резервное копирование повышает долговечность?).

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

Как и в случае увеличения детализации блокировок, оборотной стороной усиленной безопасности является увеличение объема работы сервера базы. Сервер базы данных с транзакциями ACID также требует больших мощности процессора, объема памяти и дискового пространства, чем сервер без них. Как мы уже отмечали, это тот самый случай, когда архитектура подсистем хранения данных MySQL является вашим со­юзником. Вы сами можете решить, требует ли приложение использования транзак­ций. Если они не нужны, вы можете добиться большей производительности, выбрав для некоторых типов запросов нетранзакционную подсистему хранения данных. С помощью команды LOCK TABLES можно установить нужный уровень защиты без использования транзакций. Все в ваших руках.

Уровни изолированности

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

Вкратце рассмотрим четыре уровня изолированности.

  • READ UNCOMMITTED. На этом уровне изолированности транзакции могут видеть результаты незавершенных транзакций. Вы можете столкнуться с множеством проблем, если не знаете абсолютно точно, что делаете. Используйте этот уровень, только если у вас есть на то веские причины. На практике этот уровень применя­ется редко, поскольку в этом случае производительность лишь немного выше, чем на других уровнях, имеющих множество преимуществ. Чтение незавершенных данных называют еще черновым, или "грязным" чтением (dirty read).
  • READ COMMITTED. Это уровень изолированности, который устанавливается по умолча­нию в большинстве СУБД (но не в MySQL!). Он соответствует приведенному ранее простому определению изолированности: транзакция увидит только те изменения, которые к моменту ее начала подтверждены другими транзакциями, а произведен­ные ею изменения останутся невидимыми для других транзакций, пока текущая не будет подтверждена. На этом уровне возможно так называемое неповторяющееся чтение (nonrepeatable read). Это означает, что вы можете выполнить одну и ту же команду дважды и получить разный результат.
  • REPEATABLE READ. Этот уровень изолированности позволяет решить проблемы, ко­торые возникают на уровне READ UNCOMMITTED. Он гарантирует, что любые строки, которые считываются транзакцией, будут выглядеть одинаково при последовательных операциях чтения в пределах одной транзакции, однако теоретически на этом уровне возможна другая проблема, которая называется фантомным чтением (phantom reads). Проще говоря, фантомное чтение может произойти в случае, если вы выбираете некоторый диапазон строк, затем другая транзакция вставляет в него новую строку, после чего вы снова выбираете тот же диапазон. В результате вы увидите новую, фантомную строку. InnoDB и XtraDB решают проблему фантомного чтения с помощью многоверсионного управления конку­рентным доступом (multiversion concurrency control). Уровень изолированности REPEATABLE READ устанавливается в MySQL по умол­чанию.
  • SERIALIZABLE. Самый высокий уровень изолированности, который решает про­блему фантомного чтения, заставляя транзакции выполняться в таком порядке, чтобы исключить возможность конфликта. Если коротко, уровень SERIALIZABLE блокирует каждую читаемую строку. На этом уровне может возникать множество задержек и конфликтов блокировок. Нам редко встречались люди, использующие этот уровень, но потребности вашего приложения могут заставить применять его, смирившись с меньшей степенью конкурентного доступа, но обеспечивая стабильность данных.

Взаимоблокировки

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

Транзакция №1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

Транзакция №2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

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

Для борьбы с этой проблемой в СУБД реализованы различные формы обнаружения взаимоблокировок и тайм-аутов. Более совершенные подсистемы хранения данных, такие как InnoDB, легко обнаруживают циклические зависимости и немедленно возвращают ошибку. Это очень хорошо, иначе взаимоблокировки проявлялись бы в виде очень медленных запросов. Другие системы откатывают транзакцию по ис­течении тайм-аута, что не очень хорошо. InnoDB обрабатывает взаимоблокировки откатом той транзакции, которая захватила меньше всего монопольных блокировок строк (приблизительный показатель легкости отката).

Поведение и порядок блокировок зависят от подсистемы хранения данных, так что в одних подсистемах при определенной последовательности команд могут происхо­дить взаимоблокировки, а в других - нет. Взаимоблокировки имеют двойственную природу: некоторые неизбежны из-за конфликта данных, другие вызваны схемой работы конкретной подсистемы хранения.

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

Ведение журнала транзакций

Ведение журнала помогает сделать транзакции более эффективными. Вместо обнов­ления таблиц на диске после каждого изменения подсистема хранения данных может изменить находящуюся в памяти копию данных. Это происходит очень быстро. Затем подсистема хранения запишет сведения об изменениях в журнал транзакции, который хранится на диске и поэтому долговечен. Это тоже доволь­но быстрая операция, поскольку добавление событий в журнал сводится к опе­рации последовательного ввода/вывода в пределах ограниченной области диска вместо случайного ввода/вывода в разных местах. Позже процесс обновит табли­цу на диске. Таким образом, большинство подсистем хранения данных, которые используют этот метод (упреждающую запись в журнал), дважды сохраняют из­менения на диске.

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

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

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic:
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

Стоит сказать, что в СУБД MySQL транзакции поддерживаются только в движке InnoDB. Для таблиц, которые не поддерживают транзакции (например, MyISAM), применяется другой принцип поддержания целостности данных - атомарные операции. В сравнении с транзакциями они зачастую дают сравнимую или лучшую целостность, имея более высокую производительность. MySQL поддерживает оба принципа, что позволяет выбирать между скоростью и возможностями транзакций. Если код приложения позволяет в случае критических ошибок вызов ROLLBACK, то транзакции явно предпочтительней атомарных операций. Также они гарантируют, что незавершенные обновления не будут сохраняться в БД. Сервер может произвести автоматический откат операций, а БД будет сохранена в консистентном состоянии. 

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

Атомарность операции состоит в гарантии отсутствия влияния других запросов на выполнение отдельно взятой атомарной операции.

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

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

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

UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer
  SET
    customer_date='current_date',
    address='new address',
    phone='new phone',
    money_he_owes_us=money_he_owes_us+'new_money'
  WHERE
    customer_id=id AND address='old address' AND phone='old phone';

Обратите внимание: данный подход эффективно работает даже в случае, если другой пользователь заменит значения в pay_back или money_he_owes_us.

Синтаксис команд BEGIN / COMMIT / ROLLBACK

Из коробки СУБД MySQL функционирует с настройкой autocommit. Данная настройка говорит серверу, что обновления будут моментально фиксироваться на хранилище.

Для движков с поддержкой механизма транзакций (InnoDB) режим autocommit вы можете выключить при помощи:

SET AUTOCOMMIT = 0

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

Также переключение на autocommit возможно и для отдельно взятой операции:

BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

Выполняя команды BEGIN или SET AUTOCOMMIT=0, применяйте binary log MySQL, чтобы обеспечить оптимальное хранение резервных копий. Транзакции хранятся в таком журнале в виде единого пакета данных, завершающегося через COMMIT, для обеспечения консистентности.

Deadlock

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

"Deadlock found when trying to get lock; try restarting transaction".

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

Официальная документация говорит, что в MySQL имеется два типа блокировок - на чтение (Shared - S) и эксклюзивная (Exclusive - X). Блокировка типа S блокирует выбранные данные на изменение, но позволяет другим запросам читать заблокированные данные. Блокировка типа X более строгая. Она не даёт ни читать, ни писать, ни удалять, ни получать блокировку на чтение.

При более детальном изучении выясняется, что существует два дополнительных вида блокировок. Это intention shared и intention exclusive - блоки таблиц. Они запрещают создание иных блокировок, а также операции LOCK TABLE. Создание подобной блокировки со стороны транзакции - это предупреждение, что она хочет создать соответствующую S или X блокировку.

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

Для получения deadlock-а нужны 2 транзакции, эксклюзивная блокировка и блокировка на чтение, а также строка, которую мы будем блокировать. Последовательность действий:

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

В момент 3 шага и возникает deadlock, или взаимная блокировка.

Разработчики СУБД MySQL рекомендуют чаще делать коммиты, контролировать коды ошибок и пытаться перезапустить неудавшуюся транзакцию. Либо можно сразу получать эксклюзивную блокировку. В таком случае на 3 шаге примера выше Транзакция 1 сможет получить блокировку и завершиться.

PHP & MySQL transaction example:

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();
    
    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');
    
    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (\Throwable $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
    throw $e; // but the error must be handled anyway
}