Transaction processing for MySQL

See a reality

There is a bank account form

CREATE TABLE Account

(ID int primary KEY,

Balance float);

Now there is a PHP program , to complete the 1 yuan, go to 2 Number on the account

<?PHP $mysqli=NewMysqli ("localhost","Root","hsp123","Test"); if($mysqliconnect_error) {Die ($mysqli-connect_error); } $sql 1="Update account set balance=balance-2 where Id=1"; $sql 2="Update Account2 set balance=balance+2 where id=2"; $b 1= $mysqli->query ($sql 1) or Die ($mysqlierror); $b 2= $mysqli->query ($sql 2) or Die ($mysqlierror); if(! $b 1| |!$b 2) {echo"failed"; }Else{echo"Success"; } $mysqli-close ();?>

At this point, we need to have a way to control the two sentences of SQL statements while succeeding and failing simultaneously .

Services

Transaction

Basic : transactions are used to ensure consistency of data , which consists of a set of related DML statements , a group of The DML statements either all succeed or all fail. such as : Online transfer is typical to use transactions to handle, to ensure the consistency of data

Now we use the transaction to complete the above code

<?PHP $mysqli=NewMysqli ("localhost","Root","hsp123","Test"); if($mysqliconnect_error) {Die ($mysqli-connect_error); }        //set commit as false[transaction once committed, there is no chance of rollback.]    $mysqli->autocommit (false); //savepoint A; I'll record the situation before.$sql 1 ="Update account set balance=balance-2 where Id=1"; $sql 2="Update Account2 set balance=balance+2 where id=2"; $b 1= $mysqliquery ($sql 1); $b 2= $mysqliquery ($sql 2); if(! $b 1| |!$b 2) {echo"failed, rollback". $mysqlierror; //Roll back!$mysqlirollback (); }Else{        //submit [once submitted no chance of rollback]$mysqlicommit (); } $mysqli-Close (); //Display the console?>

? In the MySQL console, you can use transactions to work with the following steps:

1. Open a transaction

Start transaction

1. Make a save point

SavePoint Save Point Name

1. Operation ....
2. Can be rolled back and can be submitted

4.1 If no problem is submitted

Commit

4.2 If you feel a problem, roll it back.

Rollback to save point .

acid Properties of U-Transactions

Atomicity, consistency, persistence, isolation .

Transaction processing for MySQL