MySQL – Транзакции
Транзакция – это последовательная группа операций манипулирования базой данных, которая выполняется так, как если бы это была одна единица работы. Другими словами, транзакция никогда не будет завершена, если каждая отдельная операция в группе не будет успешной. Если какая-либо операция в транзакции завершится неудачей, вся транзакция будет неудачной.
Практически вы объедините множество SQL-запросов в группу и выполните все их вместе как часть транзакции.
Свойства сделок
Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID –
- Атомарность – это гарантирует, что все операции внутри рабочего блока успешно завершены; в противном случае транзакция прерывается в точке сбоя, а предыдущие операции возвращаются в прежнее состояние.
- Согласованность – это гарантирует, что база данных должным образом изменит состояния при успешно совершенной транзакции.
- Изоляция – это позволяет транзакциям работать независимо друг от друга и быть прозрачными друг для друга.
- Долговечность – это гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.
Атомарность – это гарантирует, что все операции внутри рабочего блока успешно завершены; в противном случае транзакция прерывается в точке сбоя, а предыдущие операции возвращаются в прежнее состояние.
Согласованность – это гарантирует, что база данных должным образом изменит состояния при успешно совершенной транзакции.
Изоляция – это позволяет транзакциям работать независимо друг от друга и быть прозрачными друг для друга.
Долговечность – это гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.
В MySQL транзакции начинаются с оператора BEGIN WORK и заканчиваются оператором COMMIT или ROLLBACK . Команды SQL между начальным и конечным операторами составляют основную часть транзакции.
COMMIT и ROLLBACK
Эти два ключевых слова Commit и Rollback в основном используются для транзакций MySQL.
- Когда успешная транзакция завершена, необходимо выполнить команду COMMIT, чтобы изменения во всех задействованных таблицах вступили в силу.
- Если происходит сбой, необходимо выполнить команду ROLLBACK для возврата каждой таблицы, на которую есть ссылка в транзакции, в ее предыдущее состояние.
Когда успешная транзакция завершена, необходимо выполнить команду COMMIT, чтобы изменения во всех задействованных таблицах вступили в силу.
Если происходит сбой, необходимо выполнить команду ROLLBACK для возврата каждой таблицы, на которую есть ссылка в транзакции, в ее предыдущее состояние.
Вы можете контролировать поведение транзакции, установив переменную сеанса AUTOCOMMIT . Если для AUTOCOMMIT задано значение 1 (по умолчанию), то каждый оператор SQL (внутри транзакции или нет) считается завершенной транзакцией и фиксируется по умолчанию после ее завершения.
Когда для AUTOCOMMIT установлено значение 0, с помощью команды SET AUTOCOMMIT = 0 последующая серия операторов действует как транзакция, и никакие действия не фиксируются, пока не будет выполнен явный оператор COMMIT.
Вы можете выполнить эти команды SQL в PHP с помощью функции mysql_query () .
Общий пример транзакции
Эта последовательность событий не зависит от используемого языка программирования. Логический путь может быть создан на любом языке, который вы используете для создания своего приложения.
Вы можете выполнить эти команды SQL в PHP с помощью функции mysql_query () .
- Начните транзакцию, введя команду SQL BEGIN WORK .
- Выполните одну или несколько команд SQL, таких как SELECT, INSERT, UPDATE или DELETE.
- Проверьте, нет ли ошибок, и все ли соответствует вашему требованию.
- Если есть какая-либо ошибка, введите команду ROLLBACK, в противном случае введите команду COMMIT.
Начните транзакцию, введя команду SQL BEGIN WORK .
Выполните одну или несколько команд SQL, таких как SELECT, INSERT, UPDATE или DELETE.
Проверьте, нет ли ошибок, и все ли соответствует вашему требованию.
Если есть какая-либо ошибка, введите команду ROLLBACK, в противном случае введите команду COMMIT.
Транзакционно-безопасные типы таблиц в MySQL
Вы не можете использовать транзакции напрямую, но для определенных исключений вы можете. Однако они не безопасны и не гарантированы. Если вы планируете использовать транзакции в программировании MySQL, то вам нужно создавать свои таблицы особым образом. Существует много типов таблиц, которые поддерживают транзакции, но наиболее популярной является InnoDB .
Поддержка таблиц InnoDB требует определенного параметра компиляции при компиляции MySQL из источника. Если ваша версия MySQL не поддерживает InnoDB, попросите интернет-провайдера создать версию MySQL с поддержкой типов таблиц InnoDB или загрузить и установить бинарный дистрибутив MySQL-Max для Windows или Linux / UNIX и работать с типом таблицы в среда разработки.
Если ваша установка MySQL поддерживает таблицы InnoDB, просто добавьте определение TYPE = InnoDB в оператор создания таблицы.
Например, следующий код создает таблицу InnoDB с именем tcount_tbl –
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tcount_tbl -> ( -> tutorial_author varchar(40) NOT NULL, -> tutorial_count INT -> ) TYPE = InnoDB ; Query OK, 0 rows affected (0.05 sec)
Для получения более подробной информации о InnoDB, вы можете нажать на следующую ссылку – InnoDB
Вы можете использовать другие типы таблиц, такие как GEMINI или BDB , но это зависит от вашей установки, поддерживает ли она эти два типа таблиц или нет.
Транзакции в MySQL
Транзакция представляет собой группу запросов SQL, обрабатываемых атомарно, то есть как единое целое. Если подсистема базы данных может выполнить всю группу запросов, она делает это, но если какой-либо запрос не может быть выполнен в результате сбоя или по иной причине, ни один запрос группы не будет выполнен. Все или ничего.
Банковское приложение является классическим примером, демонстрирующим необходимость транзакций. Представьте банковскую базу данных с двумя таблицами: checking (текущие счета) и savings (сберегательные счета). Чтобы перевести 200 долларов с текущего счета Джейн на ее сберегательный счет, вам нужно сделать по меньшей мере три шага.
- Убедиться, что остаток на ее текущем счете больше 200 долларов.
- Вычесть 200 долларов из остатка текущего счета.
- Добавить 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:
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;
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 =
Стоит сказать, что в СУБД 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 создаёт блокировку на чтение и продолжается.
- Транзакция 2 хочет заблокировать эксклюзивно и ожидает момента, когда Транзакция 1 снимет блокировку на чтение.
- Транзакция 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 >
Транзакции
Не помню определения транзакции и не собираюсь сейчас давать его, поэтому просто своими словами скажу – это возможность базы данных, которая поддерживается SQL. Когда мы выполняем одну команду, то она будет выполнена или не выполнена. Если команда не может быть выполнена, то все изменения отменяются.
А что, если нам нужно выполнить несколько команд, которые должны выполниться все или не выполниться вовсе? Вот тут как раз и подтягиваются транзакции. С помощью специальных команд мы начинаем и заканчиваем транзакцию, а все команды между началом и концом будут выполнены полностью или не будут выполнены вовсе.
В зависимости от базы данных команды и процесс может немного отличаться. В oracle, кажется, любая команда изменения данных начинала транзакцию. Я с этой базой данных не работал уже лет 14 и недавно дочке помогал с домашней работой и увидел, что у них до сих пор изменения начинают транзакцию. Но Oracle отличался неплохими настройками и скорей всего это настраивается.
Классическая задача – перевод денег с одного счета на другой. Допустим, что у вы хотите перевести деньги с одного счета на другой. Для этого нужно снять деньги с одного счета и прибавить на другой, а это две операции. Допустим, что после снятия денег со счета произошла ошибка, сеть отключилась или завис компьютер – все потеряно. Деньги сняли, а никуда не добавили, так что мы потеряли деньги.
Может сначала добавлять деньги на новый аккаунт, а потом снимать? Ну тогда в случае ошибки мы подарим счастливчику деньги и не спишем, а это уже невыгодно.
Нужно гарантировать, что обе операции будут выполнены полностью или не выполнены вовсе, если в процессе произошла ошибка.
Итак, транзакция – это какая-то фигня, которая гарантирует, что все команды выполнены или не выполнены. И мы должны как-то сказать, где начинается эта фигня, а где заканчивается. Как я уже сказал в Oracle любое обновление данных кажется создает эту фигню, которую все называют транзакцией, но можно и вручную указать начало с помощью команды START TRANSACTION. В MySQL для этого используется BEGIN WORK или START TRANSACTION, а в MS SQL это BEGIN TRANSACTION. Не знаю, зачем каждая база данных использует свой вариант команды, но вот такая печалька. Я все примеры показываю на MySQL, поэтому везде, где я буду использовать START TRANSACTION, вы должны заменять эту команду на BEGIN TRANSACTION, если работаете с MS SQL Server.
В MySQL транзакцию можно начать с помощью BEGIN WORK или START TRANSACTION, но в чем разница? Согласно документации MySQL разницы нет, а BEGIN WORK – это псевдоним к START TRANSACTION.
Итак, наша логика перевода денег должна выглядеть так:
START TRANSACTION; СНЯТЬ ДЕНЬГИ С АККАУНТА 1; ДОБАВИТЬ ДЕНЬГИ НА АККАУНТ 2; COMMIT или ROLLBACK
Если мы выполним COMMIT, то оба изменения попадут в базу данных. Если выполнить ROLLBACK, то все изменения будут потеряны.
Это и все, всего три команды:
— начать транзакцию START TRANSACTION для MySQL или BEGIN TRANSACTION для MS SQL Server
— зафиксировать изменения COMMIT
— отменить изменения ROLLBACK
Фиксируются или отменяются все изменения, которые могут находиться между началом и фиксацией/отменой. Теория простая, а теперь нужно на практики зафиксировать транзакцию, которую я начал в этой главе, чтобы информация не откатилась.
Открываем любимый редактор для работы с SQL, я сегодня буду использовать Workbench, который все чаще использую во время записи видео. Подключайтесь к базе данных и начинаем выполнять команды:
START TRANSACTION;
Теперь давайте вставим новую запись в таблицу команд:
INSERT team (name) VALUES ('sdfsdf');
Проверим, вставилась ли запись или нет, просто выбирая все записи из таблицы:
SELECT * FROM team;
Вы должны увидеть новую запись с именем команды ‘sdfsdf’.
Теперь подключимся к базе данных еще раз, выбираем Database — Connect to Database второй раз.
В первой (слева) закладке я начал транзакцию и вставил запись, а во второй закладке давайте тоже попробуем выполнить SELECT запрос и посмотрим на содержимое таблицы team. Вы не должны увидеть команды с названием sdfsdf.
Если выбирать данные из таблицы team в той закладке, в которой мы создали транзакцию, то мы будем видеть изменения, потому что транзакция создается для определенного подключения. Если сейчас убить программу через процессы, если выключить питание компьютера, то транзакция не будет завершенной и все изменения будут отменены. Потеряв доступ к подключению, вы никогда уже не сможете завершить транзакцию с помощью команды COMMIT и сервер должен отменить изменения.
Пока commit не выполнен, данные в базе данных не зафиксированы. Значит ли это, что никто другой не сможет их видеть, как в нашем случае? Не факт, потому что тут есть еще одно понятие – уровень изоляции, но об этом стоит поговорить отдельно, совсем чуть-чуть позже. В зависимости от базы данных поведение по умолчанию может отличаться, запрос на второй закладке может как бы зависнуть и висеть, пока вы его не отмените или пока не завершите транзакцию на первой закладке.
Выполните ROLLBACK и теперь попробуйте снова посмотреть на содержимое таблиц. На какой закладке вы не выполняли бы запрос, вы больше изменения не увидите, они полностью отменены.
Попробуем еще раз выполнить те же команды, но на этот раз не будем откатывать изменения, а зафиксируем их с помощью COMMIT:
START TRANSACTION; INSERT team (name) VALUES ('sdfsdf'); COMMIT
Отлично, теперь все подключения к базе данных будут видеть одно и то же, потому что теперь вставка новой строки зафиксирована и реально сохранена в базе.
Уровни изоляции транзакций
Уровень изоляции можно устанавливать для базы данных и для текущей сессии. Для базы данных меняется в настройках и тут скорей всего понадобиться перезапускать сервер базы данных. Опять же, я все базы данных знать не могу, поэтому говорить не буду. Мы будем менять уровень изоляции только для текущей сессии (соединение к базе данных) и рассмотрим только mysql. Тут нужно выполнить команду:
SET SESSION TRANSACTION ISOLATION LEVEL УРОВЕНЬ ИЗОЛЯЦИИ;
Если вы работаете с MS SQL Server, то команда выглядит точно также, только слово SESSION нужно опустить, то есть в нем выполняем команду:
SET TRANSACTION ISOLATION LEVEL УРОВЕНЬ ИЗОЛЯЦИИ;
Посмотреть текущий уровень можно с помощью команды:
SHOW VARIABLES LIKE 'tx_isolation';
Это команда не совсем SQL
Уровень изоляции READ COMMITTED
Давайте теперь поговорим про уровни изоляции. READ COMMITTED, это когда все изменения доступны внутри транзакции, которую мы создали, но эти изменения не будут видны другим. Именно это мы уже видели. Это очень хороший уровень, но у него есть недостаток – мы создали транзакцию, изменили данные, а другие считают, что данные не изменены и будут видеть старые данные и поэтому другие пользователи могут принять неверное решение.
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Уровень изоляции REPEATABLE READ
По умолчанию в MySQL используется REPEATABLE READ – работает почти как READ COMMITTED, только еще более жестко, потому что транзакция влияет еще и на чтение данных. Например, вы начинаете транзакцию и читаете данные из базы данных:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT * FROM team;
Теперь в другом окне попробуйте вставить новую строку и вернитесь в первое окно с транзакцией и попробуйте прочитать данные. При изоляции READ COMMITTED вы увидите новую строку, а при REPEATABLE READ – нет. В этом режиме гарантируется, что при первом чтении вы как бы фиксируете данные и после этого повторные чтения будут возвращать тот же результат. Именно чтение фиксирует данные. Подчеркиваю, именно «как бы» фисксируете, потому что реальной фиксации нет.
Этот уровень изоляции значит, что если сделать что-то типа:
СОЕДИНЕНИЕ 1: Начинаем транзакцию СОЕДИНЕНИЕ 2: Вставляет данные 1 СОЕДИНЕНИЕ 1: Выбирает данные СОЕДИНЕНИЕ 2: Вставляет данные 2
В этом случае вставленные данные 1 будут видны в соединении/сессии 1. А вот вторая вставка видна не будет, потому что соединение 1 к этому моменту уже прочитала данные, зафиксировало состояние и последующие чтения должны возвращать тот же результат.
В остальном REPEATABLE READ – работает также, как и READ COMMITTED. Если где-то внутри транзакции вставлены или изменены данные, вы их не увидите.
Уровень изоляции READ UNCOMMITTED
Следующий уровень изоляции – READ UNCOMMITTED, он позволяет видеть изменения, которые сделала другая транзакция и эти данные еще не подтверждены.
Давайте посмотрим его на практике. В одном окне выполняем вставку в транзакции:
START TRANSACTION; INSERT team (name) VALUES ('sdfsdf');
Данные еще не зафиксированы и по умолчанию мы их не должны видит. Но давайте в другой закладке с другим подключением выполним:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM team;
Отлично, мы увидели данные, которые не подтверждены.
Такой уровень называют грязным чтением и не просто так, потому что это хуже, чем какой-либо из уровней. Если какая-то транзакция прочитает не зафиксированные данные, а оригинальная транзакция не будет завершена удачно и изменения отменятся, то результат будет еще хуже. Пользователи, прочитавшие не зафиксированные данные могут потом их зафиксировать, потому что будут думать, что это норм. Они могут принять неверные решения.
Уровень изоляции SERIALIZABLE
Это еще более жестокий вровень, который не разрешает другим соединениям/сессиям модифицировать данные, потому что SELECT создает range lock.
Допустим в одной сессии мы начинаем транзакцию и выбираем данные из базы:
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; SELECT * FROM team;
Теперь попробуйте в другой сессии вставить новую запись в таблицу.
INSERT TEAM VALUES ('test')
Запрос зависнет, вы не сможете вставить данные, потому что при этом уровне изоляции первая транзакция прочитала данные и заблокировала их. Запрос на вставку будет висеть до тех пор, пока вы не завершите первую транзакцию. Как только вы выполните COMMIT в первом окне, вставка данных во втором окне завершиться успешно.
Уровень изоляции SNAPSHOT
Этот уровень не поддерживается в MySQL. При начале транзакции данные как бы фиксируются. В отличии от REPEATABLE READ нам не нужно читать данные, чтобы зафиксировать что-то, достаточно просто начать транзакцию, и мы будем видеть данные на момент старта.
Мертвые блокировки DEADLOCK
Вернем назад уровень изоляции repeatable read:
SET session transaction isolation level repeatable read;
Теперь в одном окне создаем транзакцию и выполняем обновление таблицы Team:
START TRANSACTION; update team set name = 'dfgerdg' where teamid = 1;
В результате обновляемая запись в таблице team будет заблокирована.
В другом окне с другой сессией тоже создаем транзакцию и обновляем таблицу Person:
START TRANSACTION; update person set firstname = 'sdfsdfds' where personid = 1;
В результате обновляемая запись в таблице person будет заблокирована.
Каждая транзакция блокирует по одной записи. А что если теперь в первой транзакции попытаться обновить заблокированную запись в таблице Person:
update person set firstname = 'sdfsdfds' where personid = 1;
Эта операция подвиснет и не сможет завершиться, потому что запись заблокирована в другой транзакции и пока та транзакция не завершиться COMMIT или ROLLBACK, первое окно будет в состоянии ожидании.
А что если теперь второе окно попытается обновить team:
update team set name = ‘dfgerdg’ where teamid = 1;
Вот тут сразу же произойдет мертвая блокировка deadlock и обе сразу же отменятся. Дело в том, что сервер видит, что две транзакции блокируют друг друга и тут же сообщит нам об ошибке.
Чтобы снизить вероятность возникновения таких проблем нужно стараться обновлять данные во всех транзакциях в одинаковом порядке. Это не будет гарантировать сто процентной защиты, но по крайней мере снизит вероятность возникновения проблем.
Еще нужно делать так, чтобы транзакции были как можно более короткими – если мы открываем транзакцию, то нужно делать так, чтобы она завершалась как можно быстрее, все запросы в транзакции должны выполняться как можно быстрее и тут больше вопрос оптимизации.
Транзакции в MySQL
Транзакцией называется атомарная группа запросов SQL, т. е. запросы, которые рассматриваются как единое целое. Если база данных может выполнить всю группу запросов, она делает это, но если любой из них не может быть выполнен в результате сбоя или по какой-то другой причине, не будет выполнен ни один запрос группы. Все или ничего.
Транзакции не могут быть вложенными, потому что любой оператор, начинающий транзакцию, приводит к завершению предыдущей.
Денежные переводы — отличный пример, показывающий, почему необходимы транзакции. Если при оплате покупки происходит перевод от клиента на счет магазина, то счет клиента должен уменьшиться на эту сумму, а счет магазина — увеличиться на нее же.
По шагам это будет выглядеть так:
- Убедиться, что остаток на счете клиента больше 3000 рублей.
- Вычесть 3000 рублей со счета клиента.
- Добавить 3000 к счету интернет-магазина.
Команды входящие в транзакцию:
Команда START TRANSACTION начинает транзакцию.
Убеждаемся, что на счету пользователя достаточно средств.
Снимаем средства со счета пользователя.
Перемещаем денежные средства на счет интернет-магазина.
Чтобы изменения вступили в силу, мы должны выполнить команду COMMIT.
Основные концепции транзакции ACID
Atomicity, Consistency, Isolation, Durability (Атомарность, Согласованность, Изолированность, Стойкость, или долговечность).
Atomicy — атомарност
Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Когда транзакции атомарны, не существует такого понятия, как частично выполненная транзакция. Атомарность гарантирует, что будут либо выполнены все подоперации транзакции, либо не выполнено ни одной.
Consistency — согласованность
Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. При выполнении принципа согласованности база данных должна всегда переходить из одного непротиворечивого состояния в другое непротиворечивое состояние. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. Это условие является необходимым для поддержки четвёртого свойства.
Isolation — изолированность
Изолированность — транзакция должна быть изолирована от других, т.е. её результат не должен зависеть от выполнения других параллельных транзакций. Изолированность — требование дорогое, поэтому в реальных БД существуют режимы, не полностью изолирующие транзакцию (уровни изолированности).
Durability — сохраняемость
Сохраняемость гарантирует, что изменения, внесенные в ходе транзакции, будучи зафиксированными, становятся постоянными. Это означает, что изменения должны быть записаны так, чтобы данные не могли быть потеряны в случае сбоя системы.
Управление транзакциями
Для управления транзакциями используются следующие команды:
- COMMIT — сохраняет изменения;
- ROLLBACK — откатывает (отменяет) изменения;
- SAVEPOINT — создаёт точку к которой группа транзакций может откатиться;
- SET TRANSACTION — размещает имя транзакции.
Команды управление транзакциями используются только для DML команд: INSERT , UPDATE , DELETE .
Rollback
ROLLBACK (от roll back — англ. откатывать, возвращаться) — оператор языка SQL, который применяется для того, чтобы:
- отменить все изменения, внесённые начиная с момента начала транзакции или с какой-то точки сохранения ( SAVEPOINT );
- очистить все точки сохранения данной транзакции;
- завершить транзакцию;
- освободить все блокировки данной транзакции.
При выполнении транзакции мы выясняем, что не можем завершить транзакцию, например, пользователь ее отменяет или происходит еще что-то. Чтобы ее отметить мы воспользовались командой ROLLBACK .
Для некоторых операторов нельзя выполнить откат при помощи оператора ROLLBACK . К их числу относят следующие команды:
- CREATE INDEX
- DROP INDEX
- CREATE TABLE
- DROP TABLE
- TRUNCATE TABLE
- ALTER TABLE
- RENAME TABLE
- CREATE DATABASE
- DROP DATABASE
- ALTER DATABASE
Не помещайте их в транзакции с другими операторами.
Кроме того, существует ряд операторов, которые неявно завершают транзакцию, как если бы был вызван оператор COMMIT :
- ALTER TABLE
- BEGIN
- CREATE INDEX
- CREATE TABLE
- CREATE DATABASE
- DROP DATABASE
- DROP INDEX
- DROP TABLE
- DROP DATABASE
- LOAD MASTER DATA
- LOCK TABLES
- RENAME
- SET AUTOCOMMIT=1
- START TRANSACTION
- TRUNCATE TABLE
SAVEPOINT и ROLLBACK TO SAVEPOINT
Точка сохранения представляет собой место в последовательности событий транзакции, которое может выступать промежуточной точкой восстановления. Откат транзакции может быть выполнен не к началу транзакции, а к точке сохранения. Для работы с точками сохранения предназначены два оператора:
- SAVEPOINT — создает точку сохранения;
- ROLLBACK TO SAVEPOINT — позволяет откатиться к одной из точек сохранения.
Несколько точек сохранения
Допускается создание нескольких точек сохранения. Если текущая транзакция имеет точку сохранения с таким же именем, старая точка удаляется и устанавливается новая. Все точки сохранения транзакций удаляются, если выполняется оператор COMMIT или ROLLBACK без указания имени точки сохранения.
Уровни изоляции
Стандарт SQL определяет четыре уровня изоляции с конкретными правилами, устанавливающими, какие изменения видны внутри и вне транзакции, а какие нет:
- READ UNCOMMITTED — используется редко, поскольку его производительность не намного выше, чем у других. На этом уровне вы видите промежуточные результаты чужих транзакций, т.е. осуществляете грязное чтение.
- READ COMMITTED — подразумевает, что транзакция увидит только те изменения, которые были уже зафиксированы другими транзакциями к моменту ее начала. Произведенные ею изменения останутся невидимыми для других транзакций, пока она не будет зафиксирована. На этом уровне возможен феномен невоспроизводимого чтения. Это означает, что вы можете выполнить одну и ту же команду дважды и получить различный результат.
- REPEATABLE READ — этот уровень изоляции установлен по умолчанию. Он гарантирует, что любые строки, которые считываются в контексте транзакции, будут выглядеть такими же при последовательных операциях чтения в пределах одной и той же транзакции, однако теоретически на этом уровне возможен феномен фантомного чтения (phantom reads). Он возникает в случае, если вы выбираете некоторый диапазон строк, затем другая транзакция вставляет новую строку в этот диапазон, после чего вы выбираете тот же диапазон снова. В результате вы увидите новую фантомную строку.
- SERIALIZABLE — самый высокий уровень изоляции, решает проблему фантомного чтения, заставляя транзакции выполняться в таком порядке, чтобы исключить возможность конфликта. Уровень SERIALIZABLE блокирует каждую строку, которую транзакция читает. На этом уровне может возникать множество задержек и конфликтов при блокировках. На практике данный уровень изоляции применяется достаточно редко.
Изменить уровень изоляции можно при помощи команды SET TRANSACTION :
Журнал транзакций
Запросить параметры журнала транзакций можно при помощи следующего запроса:
Где располагается журнал транзакций
ib_logfile0 и ib_logfile1 — файлы журнала транзакций, все транзакции сначала помещаются сюда, затем перегоняются в файлы единого табличного пространства: если сервер MySQL останавливается штатно, все транзакции из журнала сохраняются в таблицу. Если происходит сбой и сервер останавливается, например из-за отсутствия питания, перед стартом MySQL проверяет журнал транзакций и перегоняет в единое табличное пространство все транзакции которые не были сохранены в таблицах. Таким образом, потерять сохраненные транзакции невозможно.
Получить путь к каталогу, в котором размещены файлы транзакций, можно при помощи следующего запроса:
Управление режимом сохранения транзакций
За режим управления сохранения транзакций отвечает переменная innodb_flush_log_at_trx_commit, которая может принимать следующие значения:
- 0 — сохранение журнала раз в секунду,
- 1 — сохранение после каждой транзакции,
- 2 — сохранение журнала раз в секунду и после каждой транзакции.
Узнать текущий режим сохранения транзакций:
Установить новое значение: