PHP operation MySQL Transaction instance _php tips

This article describes the PHP operation of the MySQL transaction method, share for everyone to reference. The specific methods are as follows:

In general, transactions should have acid characteristics. The so-called acid is atomic (atomicity), consistent (consistency), Isolated (isolation), durable (persistence) four words written in the first letter, the following "bank transfer" as an example to explain their meaning:

① atomicity: The statement that makes up a transaction forms a logical unit and cannot execute only part of it. In other words, a transaction is an indivisible smallest unit. For example: In the process of bank transfer, you must deduct the transfer amount from one account at the same time and add it to another account, it is unreasonable to change only one account.
② Consistency: The database is consistent before and after transaction execution. In other words, the transaction should correctly transform the system state. For example: In the bank transfer process, either transfer amount from one account to another account, or two accounts are unchanged, there is no other situation.
③ Isolation: One transaction has no effect on another transaction processing. This means that no transaction can see a transaction in an incomplete state. For example, in the course of bank transfers, another Giro transaction can only wait until the transfer transaction has been committed.
④ Persistence: The effect of transaction processing can be permanently preserved. Conversely, a transaction should be able to withstand all failures, including server, process, communication, and media failures, and so on. For example: In the process of bank transfer, transfer account the state of the household to be able to be preserved.

In PHP, Mysqli has been very good at encapsulating MySQL transaction related operations. The following example:

Copy Code code as follows:

$sql 1 = "Update User set scorecount = Scorecount +10 where id= ' 123456 '";
$sql 2 = "Update scoredetail set fscore = where id= ' 123456 '";
$sql 3 = "INSERT into Scoredetail id,score) VALUES (' 123456 ', 60)";
$mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test ');
$mysqli->autocommit (false);/Start Things
$mysqli->query ($sql 1);
$mysqli->query ($sql 2);
if (! $mysqli->errno) {
$mysqli->commit ();
echo ' OK ';
}else{
echo ' err ';
$mysqli->rollback ();
}

Here, we use the PHP MySQL series function to execute the transaction.

Copy Code code as follows:

$sql 1 = "Update User set scorecount = Scorecount +10 where id= ' 123456 '";
$sql 2 = "Update scoredetail set fscore = where id= ' 123456 '";
$sql 3 = "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 ($sql 1);
mysql_query ($sql 2);
if (Mysql_errno ()) {
mysql_query (' rollback ');
echo ' err ';
}else{
mysql_query (' commit ');
echo ' OK ';
}
mysql_query (' SET autocommit=1 ');
mysql_query ($sql 3);

Here to note:

MyISAM: Transactions are not supported for read-only programs to improve performance
InnoDB: Supports acid transactions, row-level locks, concurrency
Berkeley DB: Supporting Transactions

I hope this article is helpful to the Php+mysql database program design.