MYSQLI Extension Library---transaction control

1, there is a bank account form

CREATE TABLE account (    ID int primary key,    balance float);

2, now there is a PHP program, to complete the 1th Number 10 yuan, transfer to 2nd account number

<?php$mysqli = new Mysqli ("127.0.0.1", "root", "123456", "test"), if ($mysqli->connect_error) {die    ("Connection Error"). $ Mysqli->connect_error);} $sql 1 = "Update account set balance = balance-2 WHERE id = 1", $sql 2 = "Update account Set Balance2 = Balance + 2 where ID = 2 "; $b 1 = $mysqli->query ($sql 1) or Die ($mysqli->error), $b 2 = $mysqli->query ($sql 2) or Die ($mysqli->error); if (! $b 1 | |! $b 2) {    echo "failed! ";} Else{    echo "Success!" ";} $mysqli->close ();

From the above code, if $SQL1 need a way to control two SQL operations at the same time, the success, and also failed.

3, transactions are used to guarantee data consistency, which consists of a set of related DML statements that either all succeed or fail altogether. such as: online transfer is typical to use transactions to handle, to ensure the consistency of data.

<?php$mysqli = new Mysqli ("127.0.0.1", "root", "123456", "test"), if ($mysqli->connect_error) {die    ("Connection Error"). $ Mysqli->connect_error);} Set the commit to False, "the transaction will not be able to roll back once committed" $mysqli->autocommit (FALSE);//Set save point 1$SQL1 = "Update account set balance = Balance-2 WHERE id = 1 "; $sql 2 =" Update account Set Balance2 = Balance + 2 WHERE id = 2 "; $b 1 = $mysqli->query ($sql 1); Each time the query is executed, a savepoint is set, where the save point is set 2$b2 = $mysqli->query ($sql 2); Set Save point here 3if (! $b 1 | |! $b 2) {    echo "failed! ". $mysqli->error;    $mysqli->rollback (); If it fails, roll back to save point 1}else{    echo "Failed! ";    $mysqli->commit ();  Once submitted, no chance of rollback} $mysqli->close ();

4, the MySQL console can use transactions to operate, the following steps:

① open a transaction, start transaction.

② do the save point, if not, when the transaction is turned on, the default is to do a savepoint. SavePoint the name of the save point.

③sql operation.

④ can be rolled back and can be submitted. Commit a commit if there is no problem, roll back if you feel the problem, rollback to a savepoint, and once submitted, you cannot roll back.

5, the four characteristics of business acid

① atomicity (atomicity), atomicity means that a transaction is an inseparable unit of work, and the operations in the transaction either occur or do not occur.

② Consistency (consistency), a transaction must transform the database from one consistent state to another.

③ Isolation (isoiation), the isolation of transactions is when multiple users concurrently access the database, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

④ persistence (durability), persistence refers to the fact that once a transaction is committed, it changes the data in the database to be permanent, and then it should not have any effect even if the database fails.

MYSQLI Extension Library---transaction control