PHP operation MySQL transaction example code

MySQL transactions are mainly used to process data with large operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete the basic information of the person, and also delete the information related to the person, such as mailbox, articles, etc. In this way, these Database Operations## The # statement constitutes a transaction!

This article mainly introduces the method of PHP operating MySQL transactions. It analyzes the ACID characteristics in detail in the form of examples. It has good reference value. Friends in need can refer to it

The examples in this article describe how PHP operates MySQL transactions and are shared with you for your reference. The specific method is as follows:

Generally speaking, transactions should have ACID characteristics. The so-called ACID is written with the first letters of the four words Atomic (atomicity), Consistent (consistency), Isolated (isolation), and Durable (persistence). Let's take "bank transfer" as an example to explain their meanings. :

① Atomicity: The statements that make up a transaction form a logical unit, and only part of it cannot be executed. In other words, a transaction is the smallest indivisible unit. For example: During the bank transfer process, the transfer amount must be subtracted from one account and added to another account at the same time. It is unreasonable to only change one account.

② Consistency: The database is consistent before and after transaction processing is executed. That is, transactions should correctly transform the system state. For example: During the bank transfer process, either the transfer amount is transferred from one account to another account, or both accounts remain unchanged, and there is no other situation.
③ Isolation: One transaction has no impact on another transaction. That is to say, it is impossible for any transaction to see a transaction in an incomplete state. For example, during a bank transfer, before the transfer transaction is submitted, another transfer transaction can only be in a waiting state.
④ Durability: The effects of transaction processing can be permanently saved. On the other hand, transactions should be able to withstand all failures, including server, process, communication, media failures, etc. For example: During the bank transfer process, the account status after the transfer must be saved.

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

The code is as follows:

$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);//开始事物
$mysqli->query($sql1);
$mysqli->query($sql2);
if(!$mysqli->errno){
  $mysqli->commit();
  echo 'ok';
}else{
 echo 'err';
  $mysqli->rollback();
}

Here, we use the php mysql series

function to execute the transaction.

The code is as follows:

$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: does not support transactions and is used for read-only programs to improve performance InnoDB :Support ACID transactions, row-level locks, concurrency
Berkeley DB:Support transactions

The above is the detailed content of PHP operation MySQL transaction example code. For more information, please follow other related articles on the PHP Chinese website!