MySQL transaction processing and MySQL Transaction Processing

MySQL transaction processing and MySQL Transaction Processing

Steps:

1. Enable transaction start transaction

When we start a transaction, all SQL operations occur in the memory, but no real feedback is sent to the database disk file!

2. Roll Back rollback

Rollback is to restore to the original state before the transaction is started!

Note: The rollback operation will automatically close a transaction. If you want to execute the transaction again, you need to restart the transaction!

3. Submit commit

 

Basic Principles of transactions

 

Normal execution takes effect immediately because MySQL automatically submits SQL statements by default! Therefore, the essence of enabling a transaction is to disable the previous automatic commit function, but manually commit (using the commit Statement )!

Summary of the transaction steps:

1. Start the transaction.

2. If the execution is successful, submit commit.

3. If any SQL statement fails to be executed, roll back!

 

The most typical transaction processing is to pay back the money.The following uses another 1000 yuan account as an example.

First, check the amount of money in the database.

The following code is used to handle the refund transaction:

<? Php/*** MySQL transaction operations */echo "<meta charset = UTF-8>"; // 1 connect to the database $ link = @ mysql_connect ('localhost', 'root ', '') or die ('database connection failed'); mysql_select_db ('test', $ link); mysql_query ('set names utf8 '); // 2 enable the transaction mysql_query ("start transaction"); // set a variable to determine whether all SQL statements are successful $ flag = true; // 2.1 execute a group of SQL statements in the transaction
// Li Si's money + 1000 $ SQL = "update pdo set mone = money + 1000 where name = 'Li si'"; $ res = mysql_query ($ SQL); if (! $ Res) {// If SQL statement execution fails, set $ falg to false $ flag = false ;} // Zhang San's money-1000 $ SQL = "update pdo set money = money-1000 where name = 'zhang san'"; $ res = mysql_query ($ SQL); if (! $ Res) {// if the SQL statement execution fails, set $ falg to false $ flag = false;} // 2.2 to determine whether the transaction is successfully executed if ($ flag) {// All SQL statements are successfully executed. Submit the SQL statement mysql_query ('commit '); echo "the refund is successful! ";} Else {// if one of the execution fails, roll back to the status mysql_query ('rollback') before the transaction is started; echo" failed to pay back! ";}

Result:

Next, we intentionally write an error in one of the fields to see if the transaction is properly processed and whether the amount of money in the database has changed!

// Li Si's money + 1000 $ SQL = "update pdo set mone = money + 1000 where name = 'Li si'"; // write the moeny field as mone by mistake

Result:

The result is that the payment fails and the amount of money in the database remains unchanged. This means that when a statement is not executed successfully, the transaction will not be submitted, but will be rolled back, recover data to the original state before starting the transaction. This is also the role of the transaction. That is, the transaction will be committed only when all SQL statements in the transaction are successfully executed. Otherwise, the transaction will be rolled back!

Next, we will introduce the transaction processing in PDO.