От автора: очень часто при решении тех или иных задач необходимо выполнять несколько запросов к базе данных. Но как же поступить, если это очень важные запросы, и каждый из них должен обязательно выполнится.
Другими словами, если при выполнении одного из запросов возникает ошибка либо запрос не выполняется по другим сторонним причинам – это может нарушить работу всего скрипта в целом и привести к довольно неприятным результатам.
Поэтому в данном уроке мы с Вами рассмотрим транзакции, которые обеспечат 100% выполнение всех запросов в базе данных либо не выполнение их вообще при возникновении ошибок.
1. Постановка задачи
Для сегодняшнего урока я подготовил вот такую HTML страницу:
- Бесплатный курс по PHP программированию
- Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
- В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Получить курс сейчас!
Как Вы видите — это всего одна страница интернет магазина. А именно страница отображения товаров из каталога – центральный блок, и корзина, в которой уже содержится несколько товаров.
На самом деле – это статическая страница и конечно сам механизм добавления товаров в корзину не работает, так как тема урока совсем другая.
Но тем не менее три товара, которые отображены в корзине – сохранены в сессии и если нажать по ссылке “Оформить заказ”, то заказ на покупку данных товаров будет оформлен.
Как раз на примере оформления заказа товара мы с Вами увидим все преимущества использования транзакций при работе с базой данных. Но перед этим, давайте рассмотрим, как работает данный тестовый сайт.
Итак, весь контент данного тестового сайта, содержится в базе данных, а именно в таблице magazine. Также в базе содержатся еще три таблицы – otpravleno, user_cash, zakazi.
Таблица otpravleno – используется для оплаченных и как бы отправленных заказов (опять же виртуально), таблица user_cash – хранит виртуальные средства пользователя, как будто у нас на сайте действуют виртуальные денежные средства, которыми можно оплачивать товар (это нужно просто для примера) и наконец, таблица zakazi –все заказы оформленные на сайте. То есть процесс оформления заказа сводится к последовательному выполнению нескольких SQL запросов к базе данных: добавление в таблицу zakazi – данных об оформленном заказе, снятие денежных средств у пользователя за купленные товары (то есть изменение данных в таблице user_cash), добавление в таблицу otpravleno данных об оплаченных товарах и наконец изменение количества товаров на складе(то есть уменьшение количества товаров в таблице magazine, что были куплены). Конечно, в реальном магазине такие запросы не выполняются, но для данного урока такой пример будет как раз кстати.
Теперь давайте кратко рассмотрим исходный код данного сайта.
Итак, основа логики сайта – это файл functions.php, в котором описаны все необходимые для работы сайта функции.
//Подключаемся к серверу базы данных $db = mysqli_connect('localhost','Viktor','1234','for_cart'); exit('Error'.mysqli_error()); //Устанавливаем кодировку запросов mysqli_query($db,»SET NAMES cp1251″);function get_goods($db) { //Запрос на выборку все товаров $sql = «SELECT * FROM magazine»; $result = mysqli_query($db,$sql); for($i = 0;$i $value) { $sql1 = «INSERT INTO zakazi (title) VALUES ('$value')»; $result1 = mysqli_query($db,$sql1); if(!$result1) { $sql2 = «UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')»; $sql3 = «INSERT INTO otpravleno (ti1tle) VALUES ('$value')»; $sql4 = «UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'»; mysqli_commit($db);
Как Вы видите, выполняем все, о чем я говорил выше, то есть отключаем автоподтверждение выполняемых запросов (mysqli_autocommit($db,FALSE)), затем после каждого запроса выполняем проверку наличия ошибок, если же есть ошибки немедленно выполняем откат всех изменений (mysqli_rollback($db)). Здесь также важно выполнять выход их функции, в нашем случае простой возврат (return), так как если запрос выполнен с ошибкой, нет смысла продолжать выполнение кода функции zakaz. Если же все запросы выполнены успешно, подтверждаем транзакцию (mysqli_commit($db);). Перед тем как проверить, я предлагаю функцию zakaz описать другим способом, а именно, использовать блоки try-catch для проверки выполненных запросов. Напомню, что блоки try-catch – реализуют механизм обработки исключений языка PHP. function zakaz($db) { mysqli_autocommit($db,FALSE); try { if(!$result1) { $sql2 = «UPDATE user_cash SET cash=cash-(SELECT price FROM magazine WHERE id='$key')»; $sql3 = «INSERT INTO otpravleno (ti1tle) VALUES ('$value')»; $sql4 = «UPDATE magazine SET kolvo=kolvo-1 WHERE id='$key'»; } mysqli_commit($db);
Смотрите, весь код цикла мы заключаем в блок try, а в каждом блоке if() мы с Вами генерируем исключение. То есть когда выполнится, хотя бы один блок if, сразу же будет создано исключение, и скрипт мгновенно будет перенаправлен в блок catch, который в свою очередь и выполнит откат всех изменений. Если же все нормально, и ни один из блоков if не выполнится, значит нужно просто подтвердить транзакцию, что мы собственно и делаем.
Как Вы видите, если при выполнении запроса возникает ошибка, то сразу же происходит откат всех изменений и восстановление базы данных к своему исходному состоянию. То есть все успешно работает. Поэтому если Вам необходимо выполнить несколько очень ответственных запросов к базе данных, всегда применяйте транзакции, что бы избежать неприятных последствий от появляющихся ошибок. На этом я данный урок завершаю, всего Вам доброго и до встречи в следующих уроках.
Источник: https://webformyself.com/tranzakcii-v-mysql/ Введение в транзакции в MySQL
Любая транзакция либо выполняется полностью, либо не выполняется вообще. В транзакционной модели есть два фундаментальных понятия: COMMIT и ROLLBACK. COMMIT означает фиксацию всех изменений в транзакции. ROLLBACK означает отмену (откат) изменений, произошедших в транзакции. При старте транзакции все последующие изменения сохраняются во временном хранилище. В случае выполнения COMMIT, все изменения, выполненные в рамках одной транзакции, сохранятся в физическую БД. В случае выполнения ROLLBACK произойдет откат и все изменения, выполненные в рамках этой транзакции, не сохранятся. В MySQL транзакции поддерживаются только таблицами innoDB. Таблицы MyISAM транзакции не поддерживают. В innoDB по умолчанию включен autocommit, это значит, что по умолчанию каждый запрос эквивалентен одной транзакции. Транзакция начинается со специального запроса «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 не существует механизма вложенных транзакций. Одно соединение с БД — одна транзакция. Новая транзакция в пределах одного соединения может начаться только после завершения предыдущей. Для некоторых операторов нельзя выполнить откат с помощью ROLLBACK. Это операторы языка определения данных (Data Definition Language — DDL). Сюда входят запросы CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME. Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):
Обратите внимание, что в случае SQL ошибки, транзакция сама по себе не откатится. Обычно ошибки обрабатываются уже с помощью sql wrapper’ов в самом приложении, таких как PHP PDO например. Если вы захотите откатывать изменения в случае ошибки прямо в MySQL, можно создать специальную процедуру и уже в ней выполнять ROLLBACK в обработчике: CREATE PROCEDURE prc_test() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; //Вот здесь откатываем транзакцию в случае ошибки END; START TRANSACTION; INSERT INTO tmp_table VALUES ('null'); COMMIT; END; CALL prc_test(); Но этот способ скорее просто для ознакомления, а не руководство к действию. Почему? Я крайне не рекомендую так поступать, так как в основном ошибки базы данных обрабатываются с помощью SQL оберток на стороне приложения, таких как PHP PDO например, чтобы оттуда полностью управлять транзакциями. Рассмотрим практический пример: есть 2 таблицы, пользователи — users и информация о пользователях — user_info. Представим, что нам нужно либо выполнить 3 запроса к базе данных, либо не выполнять их вообще, так как иначе это приведет к сбоям в работе приложения. Start transaction;
Представим, что во время выполнения этой транзакции, другой пользователь создал вторую параллельную транзакцию и сделал запрос SELECT * FROM user после того, как в нашей транзакции был выполнен первый запрос «INSERT INTO user (id, nik) VALUES (1, ‘nikola’)». Что увидит пользователь второй транзакции? Сможет ли он увидеть вставленную запись даже тогда, когда результаты первой транзакции еще не зафиксировались (не произошел COMMIT)? Или он сможет увидеть изменения только после того, как результаты первой транзакции будут зафиксированы? Оказывается имеют место быть оба варианта. Все зависит от уровня изоляции транзакции. У транзакций есть 4 уровня изоляции:
По умолчанию в MySQL установлен уровень изоляции № 2 (Repeatable Read). И, как я считаю, разработчики MySQL не зря сделали по умолчанию именно этот уровень, так как он наиболее удачный для большинства случаев. С первого раза может показаться, что самый лучший вариант № 3 — он самый надежный, но на практике вы можете испытать большие неудобства из-за очень медленной работы вашего приложения. Помните, что многое зависит не от того, насколько хорош уровень изоляции транзакций в БД, а от того, как спроектировано ваше приложение.
SET TRANSACTION — этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса.
Существующие соединения не затрагиваются. Для выполнения этого оператора нужно иметь привилегию SUPER. Применение ключевого слова SESSION устанавливает уровень изоляции по умолчанию всех будущих транзакций только для текущего сеанса. Вы можете также установить начальный глобальный уровень изоляции для сервера mysqld, запустив его с опцией —transaction-isolation Источник: https://webistore.ru/sql/tranzakcii-v-mysql/ [Конспект] Про блокировки и транзакции в MySQL В этом посте я хотел бы собрать воедино информацию о блокировках и транзакциях в MySQL. БлокировкиТипы блокировок
Стратегии блокировок
Блокировки в InnoDB:InnoDB использует блокировки на уровне строк. В зависимости от уровня изоляции транзакции могут блокироваться как строки, попавшие в результирующую выборку, так и все строки, что были просмотрены при поиске. Например, в REPEATABLE READ блокирующий запрос без использования индекса потребует перебора всей таблицы, а следовательно и блокировки всех записей. Есть два базовых типа блокировок:
Если копнуть глубже, то выяснится, что есть еще 2 типа блокировок, назовем их блокировками «о намерениях». Нельзя просто так взять и заблокировать запись в InnoDB. Блокировки intention shared и intention exclusive являются блокировками на уровне таблицы и блокируют только создание других блокировок и операции на всей таблице типа LOCK TABLE.
InnoDB накладывает блокировки не на сами строки с данными, а на записи индексов. Та или иная блокировка может накладываться на:
Блокировка промежутков нужна для того, чтобы избежать появления фантомных записей, когда, например, между двумя одинаковыми чтениями диапазона соседняя транзакция успевает вставить запись в этот диапазон. SELECT… LOCK IN SHARE MODE — блокирует считываемые строки на запись. Другие сессии могут читать, но ждут окончания транзакции для изменения затронутых строк. Если же в момент такого SELECT’а строка уже изменена другой транзакцией, но еще не зафиксирована, то запрос ждет окончания транзакции и затем читает свежие данные. Данная конструкция нужна, как правило, для того чтобы получить свежайшие данные (независимо от времени жизни транзакции) и заодно убедиться в том, что их никто не изменит. SELECT… FOR UPDATE — блокирует считываемые строки на чтение. Точно такую же блокировку ставит обычный UPDATE, когда считывает данные для обновления. Взаимоблокировки (deadlock) возникают тогда, когда две и более транзакции взаимно удерживают и запрашивают блокировку одних и тех же ресурсов, создавая циклическую зависимость.
Нельзя справиться с взаимоблокировками без отката одной из транзакций, частичного либо полного. Транзакции в MySQLMySQL предоставляет пользователям две транзакционные подсистемы хранения данных: InnoDB и NDB Cluster. MySQL позволяет устанавливать уровень изолированности с помощью команды SЕТ TRANSACТION ISOLAТION LEVEL, которая начинает действовать со следующей транзакции. Можете настроить уровень изолированности для всего сервера в конфигурационном файле или только для своей сессии. По умолчанию MySQL работает в режиме АUТОСОММIT. Это означает, что, пока вы не начали транзакцию явно, каждый запрос автоматически выполняется в отдельной транзакции. Некоторые команды, будучи запущенными во время начатой транзакции, заставляют MySQL подтвердить транзакцию до их выполнения. Обычно это команды языка определения данных (Data Definition Language, DDL), которые вносят изменения в структуру таблиц, например ALТЕR TABLE, но LOCK TABLES и другие директивы также обладают этим свойством. Если вы используете транзакционные и нетранзакционные таблицы (например, таблицы InnoDB и MyISAM) в одной транзакции, то все будет работать хорошо, пока не произойдет что-то неожиданное, откатить данные из нетранзакционных таблиц невозможно. ACID:
Будучи зафиксированы, внесенные в ходе транзакции изменения становятся постоянными. Это означает, что изменения должны быть записаны так, чтобы данные не могли быть потеряны в случае сбоя системы. Уровни изоляции транзакцийПроблемы параллельного доступа с использованием транзакций:
Уровни изоляции:
Конкретная реализация каждого уровня изоляции определяется подсистемой хранения данных MySQL. Уровни изоляции InnoDBREPEATABLE READ
READ COMMITED
READ UNCOMMITED
SERIALIZABLE
Журнал транзакцийВедение журнала помогает сделать транзакции более эффективными. Вместо обновления таблиц на диске после каждого изменения подсистема хранения данных может изменить находящуюся в памяти копию данных. Это происходит очень быстро. Затем подсистема хранения запишет сведения об изменениях в журнал транзакции, который хранится на диске и поэтому долговечен. Это тоже довольно быстрая операция, поскольку добавление событий в журнал сводится к операции последовательного ввода/вывода в пределах ограниченной области диска вместо случайного ввода/вывода в разных местах. Позже процесс обновит таблицу на диске. MVCC — multiversion concurrency controlMVCC сохраняет мгновенный снимок состояния данных в определенный момент времени. InnoDB реализует MVCC, сохраняя вместе с каждой строкой два скрытых значения, которые показывают, когда строка была создана и когда истек срок ее хранения (или она была удалена). Вместо хранения фактического момента времени, когда произошли данные события, строка хранит номер версии системы для этого момента. Применение для Repeatable Read:
Источник: https://zinvapel.github.io/prog/database/2018/10/04/mysqlc-transaction/ mysql Механизм транзакций — пример запроса— синтаксис
Представим, что во время выполнения 1-ой транзакции транзакции, другой пользователь создал вторую параллельную транзакцию и сделал запрос SELECT * FROM user после того, как в нашей транзакции был выполнен первый запрос «INSERT INTO user (id, nik) VALUES (1, ‘nikola’)». Сможет ли он увидеть вставленную запись даже тогда, когда результаты первой транзакции еще не зафиксировались (не произошел COMMIT)? Или он сможет увидеть изменения только после того, как результаты первой транзакции будут зафиксированы? Оказывается имеют место быть оба варианта. Все зависит от уровня изоляции транзакции. У транзакций есть 4 уровня изоляции:
По умолчанию в MySQL установлен уровень изоляции № 2 (Repeatable Read). И, как я считаю, разработчики MySQL не зря сделали по умолчанию именно этот уровень, так как он наиболее удачный для большинства случаев. С первого раза может показаться, что самый лучший вариант № 3 — он самый надежный, но на практике вы можете испытать большие неудобства из-за очень медленной работы вашего приложения. Помните, что многое зависит не от того, насколько хорош уровень изоляции транзакций в БД, а от того, как спроектировано ваше приложение.
SET TRANSACTION — этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса. SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL Существующие соединения не затрагиваются. Для выполнения этого оператора нужно иметь привилегию SUPER. Применение ключевого слова SESSION устанавливает уровень изоляции по умолчанию всех будущих транзакций только для текущего сеанса. Вы можете также установить начальный глобальный уровень изоляции для сервера mysqld, запустив его с опцией —transaction-isolation Источник:http://webistore.ru/sql/tranzakcii-v-mysql/ Источник: http://fkn.ktu10.com/?q=node/9194 Транзакции и MySQLЧто такое транзакции? Если Вы программируете интернет-магазин, форум, что угодно с большой посещаемостью, то вполне вероятно, что 2 пользователя обратятся к одним и тем же данным в один момент. Хорошо, если обратятся они только с чтением, а если они что-то хотят записать туда одновременно? И в этот же момент третий пользователь попытается прочитать эти данные? Как видите неуправляемый доступ к данным чреват проблемами искажения информации. Именно для этих ситуаций было создано понятие транзакций. Самое главное, что нужно знать про них, что они либо выполняются полностью либо не выполняются совсем. В транзакцию Вы можете добавить массу различных команд и СУБД будет воспринимать это как единую транзакцию. Другие транзакции в этот момент либо будут ждать, либо получат отказ. Транзакции в MySQLИтак, как организованы транзакции в MySQL? По умолчанию, на движке InnoDB, каждая инструкция воспринимается системой как отдельная транзакция. То есть, после каждого изменения данных – происходит автоматическая запись в физическую базу данных.
Проблемы параллельного доступа
Транзакция A дважды выполняет выборку строк с одним и тем же условием. Между выборками вклинивается транзакция B, которая добавляет новую строку, удовлетворяющую условию отбора.
Транзакция A ничего не знает о существовании транзакции B, и, т.к. сама она не меняет ничего в базе данных, то ожидает, что после повторного отбора будут отобраны те же самые строки. Результат. Транзакция A в двух одинаковых выборках строк получила разные результаты. Уровни изоляции транзакцийИтак, для того, чтобы избежать искажения информации при параллельном доступе были разработаны различные уровни изоляции транзакций. Суть в том, чтобы решить те проблемы искажения информации при параллельном доступе, которые были описаны выше. Read uncommitted (незафиксированное чтение) – наименее защищенный уровень транзакций. Этот уровень рекомендуется исопльзовать только в тех случаях, когда все транзакции работают в режиме чтения. Read committed (фиксированное чтение) – исключается “грязное чтение”, но другим транзакциям разрешено изменять заблокированные строки. Repeatable read (повторяемое чтение) – накладывает блокировки на обрабатываемые транзакцией строки и не допускает их изменение другими транзакциями, но не запрещает добавление новых записей, что может привести к появлению строк-фантомов. По умолчанию стоит для всех транзакций. Serializable (сериализуемость) – самый надежный уровень изоляции, полностью исключающий взаимное влияние транзакций. Синтаксис в MySQL будет выглядеть таким образом… Источник: http://digital-flame.ru/2015/08/05/tranzaktsii-i-mysql/ MySQL 8.0 Reference Manual :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements13.3.1 START TRANSACTION, COMMIT, and ROLLBACK StatementsSTART TRANSACTION [transaction_characteristic [, transaction_characteristic] …] transaction_characteristic: { BEGIN [WORK] These statements provide control over use of transactions:
By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement: START TRANSACTION; With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state. START TRANSACTION permits several modifiers that control transaction characteristics. To specify multiple modifiers, separate them by commas.
Important Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION statement from the client. See Chapter 28, Connectors and APIs, or the documentation for your API, for more information. To disable autocommit mode explicitly, use the following statement: SET autocommit=0; After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB or NDB) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes. autocommit is a session variable and must be set for each session. To disable autocommit mode for each new connection, see the description of the autocommit system variable at Section 5.1.8, “Server System Variables”.
The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN … END compound statement. The latter does not begin a transaction. See Section 13.6.1, “BEGIN … END Compound Statement”. Note Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN The optional WORK keyword is supported for COMMIT and ROLLBACK, as are the CHAIN and RELEASE clauses. CHAIN and RELEASE can be used for additional control over transaction completion. The value of the completion_type system variable determines the default completion behavior. See Section 5.1.8, “Server System Variables”. The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. The new transaction also uses the same access mode (READ The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction. Including the NO keyword suppresses CHAIN or RELEASE completion, which can be useful if the completion_type system variable is set to cause chaining or release completion by default. Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.
For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:
Each transaction is stored in the binary log in one chunk, upon COMMIT. Transactions that are rolled back are not logged. (Exception: Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that modifications to the nontransactional tables are replicated. ) See Section 5.4.4, “The Binary Log”. You can change the isolation level or access mode for transactions with the SET TRANSACTION statement. See Section 13.3.7, “SET TRANSACTION Statement”. Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs). Because of this, SHOW Источник: https://dev.mysql.com/doc/en/commit.html Adblockdetector |