Транзакции php. Как оказалось, знают все, а понимают не все. Транзакции в mysql и SELECT FOR UPDATE

(9)

Я действительно не нашел нормального примера PHP-файла, в котором используются транзакции MySQL. Можете ли вы показать мне простой пример этого?

И еще один вопрос. Я уже много программировал и не использовал транзакции. Могу ли я поместить функцию PHP или что-то в header.php , если один из mysql_query терпит неудачу, то остальные тоже не mysql_query ?

Думаю, я это понял, верно?

Mysql_query("SET AUTOCOMMIT=0"); mysql_query("START TRANSACTION"); $a1 = mysql_query("INSERT INTO rarara (l_id) VALUES("1")"); $a2 = mysql_query("INSERT INTO rarara (l_id) VALUES("2")"); if ($a1 and $a2) { mysql_query("COMMIT"); } else { mysql_query("ROLLBACK"); }

Answers

При использовании PDO-соединения:

$pdo = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8", $user, $pass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // this is important ]);

Я часто использую следующий код для управления транзакциями:

Function transaction(Closure $callback) { global $pdo; // let"s assume our PDO connection is in a global var // start the transaction outside of the try block, because // you don"t want to rollback a transaction that failed to start $pdo->beginTransaction(); try { $callback(); $pdo->commit(); } catch (Exception $e) // it"s better to replace this with Throwable on PHP 7+ { $pdo->rollBack(); throw $e; // we still have to complain about the exception } }

Пример использования:

Transaction(function() { global $pdo; $pdo->query("first query"); $pdo->query("second query"); $pdo->query("third query"); });

Таким образом, код транзакции не дублируется в проекте. Это хорошо, потому что, судя по другим PDO-решениям в этой теме, легко ошибиться в этом. Наиболее распространенные из них - забыть о повторном исключении и начать транзакцию внутри блока try .

У меня было это, но не уверен, что это правильно. Мог бы попробовать это тоже.

Mysql_query("START TRANSACTION"); $flag = true; $query = "INSERT INTO testing (myid) VALUES ("test")"; $query2 = "INSERT INTO testing2 (myid2) VALUES ("test2")"; $result = mysql_query($query) or trigger_error(mysql_error(), E_USER_ERROR); if (!$result) { $flag = false; } $result = mysql_query($query2) or trigger_error(mysql_error(), E_USER_ERROR); if (!$result) { $flag = false; } if ($flag) { mysql_query("COMMIT"); } else { mysql_query("ROLLBACK"); }

Проверьте, какой механизм хранения вы используете. Если это MyISAM, Transaction("COMMIT","ROLLBACK") не будет поддерживаться, потому что только транзакция InnoDB, а не MyISAM, поддерживает транзакции.

Я сделал функцию, чтобы получить вектор запросов и выполнить транзакцию, может быть, кто-то найдет ее полезной:

Function transaction ($con, $Q){ mysqli_query($con, "START TRANSACTION"); for ($i = 0; $i < count ($Q); $i++){ if (!mysqli_query ($con, $Q[$i])){ echo "Error! Info: <" . mysqli_error ($con) . "> Query: <" . $Q[$i] . ">"; break; } } if ($i == count ($Q)){ mysqli_query($con, "COMMIT"); return 1; } else { mysqli_query($con, "ROLLBACK"); return 0; } }

Поскольку это первый результат в google для «транзакции php mysql», я подумал, что добавлю ответ, который явно демонстрирует, как это сделать с помощью mysqli (как того хотели оригинальные авторы). Вот упрощенный пример транзакций с PHP / mysqli:

// let"s pretend that a user wants to create a new "group". we will do so // while at the same time creating a "membership" for the group which // consists solely of the user themselves (at first). accordingly, the group // and membership records should be created together, or not at all. // this sounds like a job for: TRANSACTIONS! (*cue music*) $group_name = "The Thursday Thumpers"; $member_name = "EleventyOne"; $conn = new mysqli($db_host,$db_user,$db_passwd,$db_name); // error-check this // note: this is meant for InnoDB tables. won"t work with MyISAM tables. try { $conn->autocommit(FALSE); // i.e., start transaction // assume that the TABLE groups has an auto_increment id field $query = "INSERT INTO groups (name) "; $query .= "VALUES ("$group_name")"; $result = $conn->query($query); if (!$result) { $result->free(); throw new Exception($conn->error); } $group_id = $conn->insert_id; // last auto_inc id from *this* connection $query = "INSERT INTO group_membership (group_id,name) "; $query .= "VALUES ("$group_id","$member_name")"; $result = $conn->query($query); if (!$result) { $result->free(); throw new Exception($conn->error); } // our SQL queries have been successful. commit them // and go back to non-transaction mode. $conn->commit(); $conn->autocommit(TRUE); // i.e., end transaction } catch (Exception $e) { // before rolling back the transaction, you"d want // to make sure that the exception was db-related $conn->rollback(); $conn->autocommit(TRUE); // i.e., end transaction }

Кроме того, имейте в виду, что PHP 5.5 имеет новый метод mysqli::begin_transaction . Однако это еще не задокументировано командой PHP, и я все еще придерживаюсь PHP 5.3, поэтому я не могу прокомментировать это.

Еще один пример процедурного стиля с mysqli_multi_query предполагает, что $query заполняется операторами с разделителями с запятой.

Mysqli_begin_transaction ($link); for (mysqli_multi_query ($link, $query); mysqli_more_results ($link); mysqli_next_result ($link)); ! mysqli_errno ($link) ? mysqli_commit ($link) : mysqli_rollback ($link);

Думаю, я это понял, верно?

Mysql_query("START TRANSACTION"); $a1 = mysql_query("INSERT INTO rarara (l_id) VALUES("1")"); $a2 = mysql_query("INSERT INTO rarara (l_id) VALUES("2")"); if ($a1 and $a2) { mysql_query("COMMIT"); } else { mysql_query("ROLLBACK"); }

Функции mysql_ * были обесценены (как и php 5.5 ), учитывая тот факт, что были разработаны лучшие функции и структуры кода. Тот факт, что функция была обесценена, означает, что больше не будет прилагаться усилий для ее улучшения с точки зрения производительности и безопасности, а это означает , что она менее надежна для будущего .

Если вам нужно больше причин:

• Функции mysql_ * не поддерживают подготовленные операторы.
• Функции mysql_ * не поддерживают привязку параметров.
• Функции mysql_ * не имеют функциональности для объектно-ориентированного программирования.
• список продолжается...

Чтобы начать транзакцию, необходимо выполнить метод «beginTransaction()» у объекта класса «PDO». Рассмотрим пример на php:

$dsn = "mysql:dbname=1;host=localhost"; $user = "root"; $password = ""; $driver = array(PDO:: MYSQL_ATTR_INIT_COMMAND => "SET NAMES `utf8`"); try { $db = new PDO($dsn, $user, $password, $driver); //создаем новый объект класса PDO для взаимодействия с БД } catch (PDOException $e) { echo "Подключение не удалось: ". $e->getCode() ."|". $e->getMessage()); exit(); } $db->beginTransaction(); //Начинаем транзакцию $db->exec("INSERT INTO user VALUES (1, "Коля")"); $db->exec("INSERT INTO user VALUES (2, "Алексей")"); $db->exec("INSERT INTO user VALUES (1, "Иван")"); ... //далее commit() или rollBack()

Чтобы зафиксировать изменения в транзакции, у объекта PDO нужно выполнить метод commit():

$db->commit();

Чтобы отменить изменения (откатить транзакцию), у объекта $db PDO необходимо вызвать метод rollBack():

$db->rollBack();

Обратите внимание, если начать транзакцию и ее не завершить (то есть в рамках скрипта не выполнить ни commit() ни rollback()), то при завершении работы скрипта транзакция откатится автоматически, если не установлено постоянного соединения с БД (не установлен атрибут PDO::ATTR_PERSISTENT => true). Тоже самое произойдет при уничтожении PDO объекта ($db=null) в коде скрипта, в этом случае PDO завершит текущее соединение с БД. Откат транзакции при завершении соединения с БД делает PDO драйвер, это очень удобно при аварийном завершении скриптов.

Транзакции доступны только для таблиц с типом InnoDB. Для MyISAM таблиц транзакции недоступны.

По умолчанию в MySQL включен autocommit. Это означает подтверждение (фиксацию) каждого запроса к БД, это означает, что каждый запрос к базе данных в MySQL по умолчанию является транзакцией. Поэтому вставка данных в таблицы типа InnoDB идет медленнее, чем в таблицы типа MyISAM. При импорте данных и вставке больших объемов информации в таблицы InnoDB следует отключать autocommit и фиксировать изменения, т. е. делать commit не после каждой вставки, а после нескольких вставок (коммитить только после совершения группы запросов).

Обработка ошибок PDO в PHP и откат транзакций при ошибках

По умолчанию в PDO установлен «тихий» режим обработки ошибок (silent mode). Это означает, что при возникновении ошибки в PDO, исключение выброшено не будет и работа скрипта продолжится. Ошибки не будут ловиться с помощью try catch блоков, код ошибки и описание будет возможно получить только с помощью специальных методов у объекта PDO или PDOStatement: errorCode() и errorInfo(). Для того, чтобы ошибки PDO можно было «ловить» в try..catch, нужно изменить режим обработки ошибок с PDO::ERRMODE_SILENT на PDO::ERRMODE_EXCEPTION . Внимание: после установки этого режима желательно обрабатывать исключения при каждом запросе к БД, так как при возникновении ошибки остановится работа скрипта и произойдет остановка всего web-приложения. Если вы устанавливайте этот режим, обязательно используйте try..catch блоки в каждом запросе, чтобы ловить ошибки.

Рассмотрим, как изменится логика приложения после включения режима обработки ошибок PDO::ERRMODE_EXCEPTION:

$dsn = "mysql:dbname=1;host=localhost";$user = "root";$password = ""; $driver = array(PDO:: MYSQL_ATTR_INIT_COMMAND => "SET NAMES `utf8`"); try { $db = new PDO($dsn, $user, $password, $driver); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Устанавливаем режим обработки ошибок ERRMODE_EXCEPTION } catch (PDOException $e) { echo "Подключение не удалось: ". $e->getCode() ."|". $e->getMessage(); exit(); } try { $db->beginTransaction(); //Начинаем транзакцию $db->exec("INSERT INTO user VALUES (1, "Коля")"); $db->exec("INSERT INTO user VALUES (2, "Алексей")"); $db->exec("INSERT INTO user VALUES (1, "Иван")"); catch (PDOException $e) { //Ловим ошибку $db->rollBack(); echo "PDOException: ".$e->getCode() ."|". $e->getMessage()); exit(); } $db->commit(); //Если все запросы прошли успешно - коммитим

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

Рассмотрим пример:

Function connect_db() { $dsn = "mysql:dbname=1;host=localhost"; $user = "root"; $password = ""; $driver = array(PDO:: MYSQL_ATTR_INIT_COMMAND => "SET NAMES `utf8`"); try { $db = new PDO($dsn, $user, $password, $driver); //создаем новый объект класса PDO для взаимодействия с БД $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //Устанавливаем режим обработки ошибок ERRMODE_EXCEPTION } catch (PDOException $e) { echo "Подключение не удалось: ". $e->getCode() ."|". $e->getMessage()); return false; } return $db; } function doQuery($db, $sql, $count_db = 0) { if($count_db>5) { echo "Кол-во попыток подключения превысило допустимый лимит"; return false; } try { if($db->inTransaction()) { echo "Транзакция уже начата"; return false; } $db->beginTransaction();//Начинаем транзакцию $db->exec($sql); } catch (PDOException $e) { if($db->inTransaction()) $db->rollBack(); if($e->errorInfo >= 2000&&$db=connect_db()) { //если код ошибки > 2000 (это потеря соединения с БД и пр.) то пробуем переподключится и выполнить запрос заново return doQuery($db, $sql, $count_db++); } else { echo "PDOException: ".$e->getCode() ."|". $e->getMessage(); return false; } } if($db->inTransaction()) return $db->commit(); }

Обратите внимание на метод:

$db->inTransaction();

Он проверяет, начата ли транзакция или нет. Это очень важно, так как если вызвать метод beginTransaction() в том случае, если транзакция уже начата, или наоборот вызвать метод rollBack() или commit() когда транзакция не еще начата, то в любом из этих случаев вы получите ФАТАЛЬНУЮ ошибку. Да, поэтому всегда проверяйте начата ли транзакция, прежде чем ее завершить, в противном случае вы просто словите ошибку и ваше приложение аварийно завершится.

По долгу службы мне приходится иногда проводить собеседования на позицию "[старший|младший] разработчик python/django", «тимлид». К моему великому удивлению я обнаружил, что 9 из 10 соискателей, в резюме которых значатся слова " Mysql/Innodb/transactions/triggers/stored proc etc.", абсолютно ничего не могут рассказать о своем прошлом опыте работы с ними. Ни одного описания варианта использования, к сожалению, я так и не получил.

Далее по собеседованию я предлагал попробовать предложить вариант решения для следующей ситуации:

Допустим, мы являемся онлайн сервисом, который в свою очередь пользуется каким-то внешним платным API (активация услуги, платный контент, или что вашей душе угодно), то есть наш сервис сам платит деньги за пользование API. Пользователь в нашей системе создает запрос на активацию услуги, заполняет все поля и на последней странице жмет кнопку «Активировать услугу». То есть на момент отправки HTTP запроса мы имеем в нашей БД запись (запрос на активацию услуги). Каков наш алгоритм?- спрашиваю я и сам продолжаю:

Достаем из базы баланс пользователя;
- если баланса достаточно, то дергаем API;
- если всё хорошо, то списываем с баланса сумму за услугу, делаем UPDATE, коммитим, иначе откатываемся;
- отвечаем пользователю.

Вроде бы всё тривиально, но когда привожу первую и самую очевидную проблему в виде 10 конкурентных запросов (что все они в начале получат одинаковый баланс и начнут звонить в API), решения начинают предлагать самые изощренные, начиная от выполнения 5 селектов (стоит признаться, я ничего не понял в этом варианте), использования автоинкрементных счетчиков, внешних кешей, новых таблиц в бд, слипов и еще не пойми чего.

Как известно (а это знали все кандидаты!), innodb в mysql предоставляет транзакционный механизм и возможность построчной блокировки. Для того, чтобы применить этот самый построчный лок, достаточно добавить в конце SELECT-а выражение FOR UPDATE, например так:

SELECT * FROM requests WHERE id=5 FOR UPDATE

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

Также стоит заметить, что использование FOR UPDATE лучше делать с выключенным autocommit-ом, так как вне зависимости от того, что вы залочили, после первого апдейта лок снимется.

Вроде мелочь, вроде очевидно, но 9 из 10…

Upd
прежнее название «Транзакции в mysql», не раскрытое в статье было заменено на " Транзакции в mysql и SELECT FOR UPDATE"

ЗЫ
в статье не говорится о том, что АПИ нужно дергать в рамках транзакции и что делать в случае сбоя и как обрабатывать исключительные ситуации.

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

Такой механизм базы данных с поддержкой транзакций, как InnoDB или BDB, начинает транзакцию по команде start transaction. Завершается транзакция при подтверждении или отмене изменений. Завершить транзакцию можно двумя командами. Команда commit сохраняет все изменения в базе данных. Команда rollback отменяет все изменения.

В примере ниже создается таблица с поддержкой транзакций, в нее вставляются данные, затем запускается транзакция, в рамках которой данные удаляются, и в заключение выполняется откат транзакции (отмена удаления):

CREATE TABLE sample_innodb (id int(11) NOT NULL auto_increment, name varchar(150) default NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO sample_innodb VALUES (1, "Александр"), (2, "Дмитрий"); start transaction; DELETE FROM sample_innodb WHERE id = 1; DELETE FROM sample_innodb WHERE id = 2; rollback;

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

А если бы вместо rollback мы написали commit, то обе строки были бы удалены.

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

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