PHP Operation MySQL Transaction Instance

This article example tells the PHP operation MySQL transaction method, shares to everybody for everybody reference. The specific methods are as follows:

1 Generally speaking, transactions should have ACID characteristics. The so-called ACID is written in the initials of Atomic (atomicity), Consistent (uniformity), Isolated (isolation) and Durable (continuity). Take "bank transfer" as an example to explain their meanings respectively:

(1) Atomicity: The statements that make up the transaction process form a logical unit, and only one part of it cannot be executed. In other words, a transaction is an indivisible smallest unit. For example, in the process of bank transfer, the transfer amount must be subtracted from one account at the same time and added to another account. It is unreasonable to change only one account.
② Unity: Before and after the transaction is executed, the database is unitary. That is, transactions should correctly transition system states. For example, in the process of bank transfer, either the transfer amount is transferred from one account to another, or both accounts remain unchanged, and there is no other situation.
③ Isolation: One transaction has no effect on the other. That is to say, it is impossible for any transaction to see a transaction in an incomplete state. For example, in the process of bank transfer, another transfer transaction can only be in a waiting state before the transfer transaction is submitted.
④ Persistence: The effect of transaction processing can be permanently preserved. Conversely, transactions should be able to withstand all failures, including server, process, communication, and media failures. For example, in the process of bank transfer, the status of the account should be saved after the transfer.

In PHP, mysqli has well encapsulated mysql transactions related operations. The following example:

$sql1 = "update User set ScoreCount = ScoreCount +10 where ID= '123456'";

$sql2 = "update ScoreDetail  set FScore = 300 where ID= '123456'";

$sql3 = "insert into  ScoreDetail ID,Score) values ('123456',60)";

$mysqli = new mysqli('localhost','root','','DB_Lib2Test');

$mysqli->autocommit(false);// Start things 

$mysqli->query($sql1);

$mysqli->query($sql2);

if(!$mysqli->errno){

  $mysqli->commit();

  echo 'ok';

}else{

 echo 'err';

  $mysqli->rollback();

}

Here again, we use the php mysql family of functions to perform transactions.

$sql1 = "update User set ScoreCount = ScoreCount +10 where ID= '123456'";

$sql2 = "update ScoreDetail  set FScore = 300 where ID= '123456'";

$sql3 = "insert into  ScoreDetail ID,Score) values ('123456',60)";

$conn = mysql_connect('localhost','root','');

mysql_select_db('DB_Lib2Test');

mysql_query('start transaction');

//mysql_query('SET autocommit=0');

mysql_query($sql1);

mysql_query($sql2);

if(mysql_errno ()){

    mysql_query('rollback');

    echo 'err';

}else{

    mysql_query('commit');

    echo 'ok';

}

// mysql_query('SET autocommit=1');

// mysql_query($sql3);

Note here:

MyISAM: Transactions are not supported for read-only programs to improve performance
InnoDB: Support for ACID transactions, row-level locks, concurrency
Berkeley DB: Transaction Support

I hope this article is helpful to everyone's PHP+MySQL database programming.