Главная » Языки программирования

Mysql php commit transaction

Содержание
  1. PHP MySQL Transaction
  2. MySQL transaction in PHP
  3. PHP MySQL transaction example
  4. mysqli_commit
  5. Parameters
  6. Return Values
  7. Errors/Exceptions
  8. Changelog
  9. Examples
  10. Notes
  11. See Also
  12. User Contributed Notes 6 notes
  13. Mysql php commit transaction
  14. User Contributed Notes

PHP MySQL Transaction

Summary: in this tutorial, you will learn how to handle MySQL transaction in PHP to ensure data integrity of the database.

A transaction is a set of inter-dependent SQL statements that needs to execute in all-or-nothing mode. A transaction is successful if all SQL statements executed successfully. A failure of any statement will trigger the system to rollback to the original state to avoid data inconsistency.

A classic example of the transaction is a money transfer transaction from one bank account to another. It requires three steps:

  • Check the balance of the transferred account to see if the amount is sufficient for the transfer.
  • If the amount is sufficient, deduct the amount from the balance of the transferred account.
  • Add the transfer amount to the balance of the receiving account.

If an error occurs in the second step, the third step should not continue. In addition, if an error occurs in the third step, the second step must be reversed. The amounts of both bank accounts are intact in case of failure or adjusted correctly if the transaction is completed successfully.

MySQL transaction in PHP

When you use PDO to create a connection to the database that supports the transaction, the auto-commit mode is set. It means that every query you issue is wrapped inside an implicit transaction.

Notice that not all storage engines in MySQL support transaction e.g., MyISAM does not support the transaction, however, InnoDB does.

Читайте также:  Javascript переменная с несколькими значениями

To handle MySQL transaction in PHP, you use the following steps:

  1. Start the transaction by calling the beginTransaction() method of the PDO object.
  2. Place the SQL statements and the commit() method call in a try block.
  3. Rollback the transaction in the catch block by calling the rollBack() method of the PDO object.

PHP MySQL transaction example

We will create a table named accounts to demonstrate the money transfer between two bank accounts.

First, execute the following statement to create the accounts table:

CREATE TABLE accounts ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (50) NOT NULL, amount DECIMAL (19, 4) NOT NULL );Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the accounts table:

INSERT INTO accounts(name,amount) VALUES('John',25000), ('Mary',95000);Code language: SQL (Structured Query Language) (sql)

Third, query the accounts table:

SELECT * FROM accounts;Code language: PHP (php)

PHP MySQL Transaction example

Let’s take a look at the following TransactionDemo class:

 /** * PHP MySQL Transaction Demo */ class TransactionDemo < const DB_HOST = 'localhost'; const DB_NAME = 'classicmodels'; const DB_USER = 'root'; const DB_PASSWORD = ''; /** * Open the database connection */ public function __construct() < // open database connection $conStr = sprintf("mysql:host=%s;dbname=%s", self::DB_HOST, self::DB_NAME); try < $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD); > catch (PDOException $e) < die($e->getMessage()); > > /** * PDO instance * @var PDO */ private $pdo = null; /** * Transfer money between two accounts * @param int $from * @param int $to * @param float $amount * @return true on success or false on failure. */ public function transfer($from, $to, $amount) < try < $this->pdo->beginTransaction(); // get available amount of the transferer account $sql = 'SELECT amount FROM accounts WHERE $stmt = $this->pdo->prepare($sql); $stmt->execute(array(":from" => $from)); $availableAmount = (int) $stmt->fetchColumn(); $stmt->closeCursor(); if ($availableAmount < $amount) < echo 'Insufficient amount to transfer'; return false; > // deduct from the transferred account $sql_update_from = 'UPDATE accounts SET amount = amount - :amount WHERE $stmt = $this->pdo->prepare($sql_update_from); $stmt->execute(array(":from" => $from, ":amount" => $amount)); $stmt->closeCursor(); // add to the receiving account $sql_update_to = 'UPDATE accounts SET amount = amount + :amount WHERE $stmt = $this->pdo->prepare($sql_update_to); $stmt->execute(array(":to" => $to, ":amount" => $amount)); // commit the transaction $this->pdo->commit(); echo 'The amount has been transferred successfully'; return true; > catch (PDOException $e) < $this->pdo->rollBack(); die($e->getMessage()); > > /** * close the database connection */ public function __destruct() < // close the database connection $this->pdo = null; > > // test the transfer method $obj = new TransactionDemo(); // transfer 30K from from account 1 to 2 $obj->transfer(1, 2, 30000); // transfer 5K from from account 1 to 2 $obj->transfer(1, 2, 5000);Code language: PHP (php)

We open a database connection in the __construct() method and close it in the __destruct() method. In the transfer() method:

  • First, we query amount of the transferred account and compare it with the transfer amount to check if the balance of the transferred account is sufficient.
  • Second, in case the amount is sufficient, we deduct the transfer amount from the transferred account and add it to the receiving account.
  • Third, we commit the transaction by calling the commit() method. If any error occurs, we call the rollBack() method in the catch block to rollback the transaction.

Let’s test the transfer() method.

// transfer 30K from from account 1 to 2 $obj->transfer(1, 2, 30000);Code language: PHP (php)

We transferred 30K from John’s account to Mary’s. We got the following message:

Insufficient amount to transfer

Let’s make another transfer:

// transfer 5K from from account 1 to 2 $obj->transfer(1, 2, 5000);Code language: PHP (php)

The script returns the following message:

The amount has been transferred successfully.

We have transferred money between two bank accounts successfully.

You can download the source code via the following link:

In this tutorial, we have shown you step by step how to handle MySQL transactions in PHP to ensure data integrity.

Источник

mysqli_commit

Commits the current transaction for the database connection.

Parameters

Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()

A bitmask of MYSQLI_TRANS_COR_* constants.

If provided then COMMIT/*name*/ is executed.

Return Values

Returns true on success or false on failure.

Errors/Exceptions

If mysqli error reporting is enabled ( MYSQLI_REPORT_ERROR ) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT , a mysqli_sql_exception is thrown instead.

Changelog

Version Description
8.0.0 name is now nullable.

Examples

Notes

Note:

This function does not work with non transactional table types (like MyISAM or ISAM).

See Also

  • mysqli_autocommit() — Turns on or off auto-committing database modifications
  • mysqli_begin_transaction() — Starts a transaction
  • mysqli_rollback() — Rolls back current transaction
  • mysqli_savepoint() — Set a named transaction savepoint

User Contributed Notes 6 notes

Please note that calling mysqli::commit() will NOT automatically set mysqli::autocommit() back to ‘true’.

This means that any queries following mysqli::commit() will be rolled back when your script exits.

I never recomend to use the ? with only one value variant like: $var = expression ? $var : other_value or $var = expression ? null : other_value ,and php suport Exception catchin so,use it 🙂

here my opinion abut lorenzo’s post:

$mysqli -> autocommit ( FALSE );

$mysqli -> query ( «INSERT INTO myCity (id) VALUES (100)» ) or throw new Exception ( ‘error!’ );

if( ! $mysqli -> query ( «INSERT INTO myCity (id) VALUES (200)» ) <
throw new Exception ( ‘error!’ );
>

>catch( Exception $e ) $mysqli -> rollback ();
>
$mysqli -> commit ();

This is an example to explain the powerful of the rollback and commit functions.
Let’s suppose you want to be sure that all queries have to be executed without errors before writing data on the database.
Here’s the code:

$all_query_ok = true ; // our control variable

//we make 4 inserts, the last one generates an error
//if at least one query returns an error we change our control variable
$mysqli -> query ( «INSERT INTO myCity (id) VALUES (100)» ) ? null : $all_query_ok = false ;
$mysqli -> query ( «INSERT INTO myCity (id) VALUES (200)» ) ? null : $all_query_ok = false ;
$mysqli -> query ( «INSERT INTO myCity (id) VALUES (300)» ) ? null : $all_query_ok = false ;
$mysqli -> query ( «INSERT INTO myCity (id) VALUES (100)» ) ? null : $all_query_ok = false ; //duplicated PRIMARY KEY VALUE

//now let’s test our control variable
$all_query_ok ? $mysqli -> commit () : $mysqli -> rollback ();

$mysqli -> close ();
?>

hope to be helpful!

This is to clarify the Flags parameters and what they mean:

Appends «AND CHAIN» to mysqli_commit or mysqli_rollback.

Appends «AND NO CHAIN» to mysqli_commit or mysqli_rollback.

Appends «RELEASE» to mysqli_commit or mysqli_rollback.

Appends «NO RELEASE» to mysqli_commit or mysqli_rollback.

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 RELEASE clause causes the server to disconnect the current client session after terminating the current transaction.

When you have alot of transactions to make, say you are applying inserting items to the database from a loop, it will be better to use the mysqli_commit for this kind of process as it will only hit the database once.

$con = mysqli_connect(«host», «username», «password», «database») or die(«Could not establish connection to database»);

$users = [«chris», «james», «peter», «mark», «joe», «alice», «bob»]

for($i=0; $i $user= $users[$i];
$query = mysqli_query($con, «INSERT INTO users (username) VALUES (‘$user’) «);
>

$con = mysqli_connect(«host», «username», «password», «database») or die(«Could not establish connection to database»);

$users = [«chris», «james», «peter», «mark», «joe», «alice», «bob»]

//Turn off autocommit
mysqli_autocommit($con, FALSE)

//Make some transactions
for($i=0; $i $user= $users[$i];
$query = mysqli_query($con, «INSERT INTO users (username) VALUES (‘$user’) «);
>

//Make a one-time hit to the database
mysqli_commit($con)

As with the Example 1, since we had 7 items in the list, this means that their will be a 7 times hit to our database which can really affect performance. But with the Example 2, since we already turned off autocommit this means that the transactions will be queued ontill will explicitly call mysqli_commit($con)

The compactness of Lorenzo’s code is admirable.
However, it is a good idea to also check $mysqli->affected_rows to make sure that the INSERT statement did not fail.

$result_query = @ mysqli_query ( $query , $connect );
if (( $result_query == false ) &&
( mysqli_affected_rows ( $connect ) == 0 ))
<
// verify the query executed completely and verify that it
// had impact on the table

// here also, the developer could choose to add a ROLLBACK
// statement
>
?>

Источник

Mysql php commit transaction

Поддержка транзакций в СУБД MySQL зависит от используемого движка хранилища данных. Начиная с MySQL 5.5, по умолчанию используется движок InnoDB. InnoDB полностью поддерживает модель транзакций ACID.

Транзакциями можно управлять как средствами SQL, так и вызовами API-функций. Для включения и выключения режима автофиксации изменений ( autocommit ) рекомендуется пользоваться API функциями.

Пример #1 Установка режима автофиксации ( autocommit ) средствами SQL и функциями API

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Рекомендуется управлять настройками транзакций средствами API */
$mysqli -> autocommit ( false );

/* Не будет распознаваться и учитываться плагинами репликации и балансировки нагрузки */
$mysqli -> query ( ‘SET AUTOCOMMIT = 0’ );

Дополнительные службы сервера, такие как плагины репликации и балансировки нагрузки, могут отслеживать вызовы API-функций. Плагин репликации может сообщать балансировщику нагрузки о запущенной транзакции, если эта транзакция обслуживается API-функциями. Сервер не сможет распределять нагрузку между репликами базы, если смена режима автофиксации ( autocommit ), фиксация и откат транзакций осуществляются SQL-запросами.

Пример #2 Фиксация и откат

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );
$mysqli -> autocommit ( false );

$mysqli -> query ( «INSERT INTO test(id) VALUES (1)» );
$mysqli -> rollback ();

$mysqli -> query ( «INSERT INTO test(id) VALUES (2)» );
$mysqli -> commit ();

Следует заметить, что сервер MySQL не может откатить результаты всех запросов. Некоторые изменения фиксируются неявно.

User Contributed Notes

Источник

Оцените статью
© 2024 Программирование