Deep test of mysql transaction operations in php

What is a transaction? Normal SQL statements are executed one by one, and each SQL statement does not affect each other. The transaction mechanism is similar to a black box. The SQL statements (multiple rows) in the black box either succeed or all fail. (I will not talk about the term here.) 2. How to Apply transactions? A transaction has three key steps: Start, commit, and rollback.

What is a transaction? Normal SQL statements are executed one by one, and each SQL statement does not affect each other. The transaction mechanism is similar to a black box. The SQL statements (multiple rows) in the black box either succeed or all fail. (I will not talk about the term here.) 2. How to Apply transactions? A transaction has three key steps: Start, commit, and rollback.

What is a transaction?

Normal SQL statements are executed one by one, and each SQL statement does not affect each other.

The transaction mechanism is similar to a black box. The SQL statements (multiple statements) in the black box either succeed or fail. (In this case, I will not talk about the term)

2. How to Apply transactions?

A transaction has three key steps: Start, commit, and rollback.

The first step is to tell the database that I want to start the transaction. Then the database will provide you with a black box, and then you will start to execute the SQL statement. If the SQL statement is normal, execute the commit statement, the results of operations in the black box are actually written to the database. If any of the operations fails, perform the following rollback and the database remains unchanged.

From this perspective, the black box is more like a "sandbox ".

Php wants to correspond to three methods for operating the mysqli Library: mysqli: autocommit, mysqli: commit (), mysqli: rollback ().

Unit 3 Test

1. Create a mysql test table (the MyISAM engine does not support transactions and the InnoDB engine does. Therefore, create the test table as the InnoDB engine)

Table Name: transaction_test, two fields: id Primary Key auto-increment, content string type.

Create table 'transaction _ test' ('id' int (11) Not null AUTO_INCREMENT, 'content' varchar (256) Default null, primary key ('id') ENGINE = InnoDB default charset = utf8 COMMENT = 'php transaction test table ';

2. Insert initialization data

Insert into transaction_test (content) values ('beijing'), ('shanghai'), ('guangzhou ');

　　

　　

3. php connect to the database and insert data for testing

Create transaction_test.php

　　　　

 Query ($ SQL _set_charset); if ($ query = false) {echo 'set charset failed
'; Exit (0);}/* insert */$ SQL _1 = "insert into transaction_test ('content') values ('tianjin ')"; $ query1 = $ mysqli-> query ($ SQL _1); if ($ query1 = false) {echo 'query1 failed
';} Else {printf ("insert_id one: % d
", $ Mysqli-> insert_id) ;}$ mysqli-> close ();?>

Running results and database results:

　　　　　　　　　　

Insert OK.

4. Test mysqli: autocommit ().

Autocommit () is used to set whether the database is automatically submitted. The parameters are true and false. The default value is true. By default, the database will submit each time a query () is executed. If it is set to false, the database will not submit automatically, and the transaction can begin, for specific usage, see the following test:

Based on the code above, disable automatic submission before the SQL statement query for data insertion.

　　　　

 Query ($ SQL _set_charset); if ($ query = false) {echo 'set charset failed
'; Exit (0);}/* close automatic commit and start the transaction */$ mysqli-> autocommit (false ); /* insert */$ SQL _1 = "insert into transaction_test ('content') values ('chongqing ')"; $ query1 = $ mysqli-> query ($ SQL _1 ); if ($ query1 = false) {echo 'query1 failed
';} Else {printf ("insert_id one: % d
", $ Mysqli-> insert_id) ;}$ mysqli-> close ();?>

Running results and database results:

　　　　　　　　　　

Analysis: php returns id 5, but the database does not have this record. It means that the query executed after the transaction starts is only in a "sandbox" and is not actually written to the database.

5. Test mysqli: commit ()

Add the commit () method after the code is inserted in the above Code.

　　　　

 Query ($ SQL _set_charset); if ($ query = false) {echo 'set charset failed
'; Exit (0);}/* close automatic commit and start the transaction */$ mysqli-> autocommit (false ); /* insert */$ SQL _1 = "insert into transaction_test ('content') values ('harbin ')"; $ query1 = $ mysqli-> query ($ SQL _1 ); if ($ query1 = false) {echo 'query1 failed
';} Else {printf ("insert_id one: % d
", $ Mysqli-> insert_id);}/* transaction commit */$ mysqli-> commit (); $ mysqli-> close ();?>

Running results and database results:

　　　　　　　　　　

Analysis: The data is actually written to the database after the execution is committed. In addition, if id is 6, it can be seen that the transactions that have not been committed have not been written to the database, but they consume an incremental id.

6. Test mysqli: rollback ()

Replace the preceding commit () with rollback ()

　　　　　　

 Query ($ SQL _set_charset); if ($ query = false) {echo 'set charset failed
'; Exit (0);}/* close automatic commit and start the transaction */$ mysqli-> autocommit (false ); /* insert */$ SQL _1 = "insert into transaction_test ('content') values ('shenyang ')"; $ query1 = $ mysqli-> query ($ SQL _1 ); if ($ query1 = false) {echo 'query1 failed
';} Else {printf ("insert_id one: % d
", $ Mysqli-> insert_id);}/* transaction rollback */$ mysqli-> rollback (); $ mysqli-> close ();?>

Running results and database results:

　　　　　　　　

Analysis: The returned id is 7, but the database does not insert this record. Isn't the result of not writing rollback () the same as above? It must be different. Continue the test.

7. Continue to test rollback ()

Remove the rollback () of the code above. After the query of the insert statement above, modify autocommit to true because other SQL statements are executed after a transaction is completed, restore automatic submission to the default state, and then execute the insert statement again.

　　　　　　

 Query ($ SQL _set_charset); if ($ query = false) {echo 'set charset failed
'; Exit (0);}/* close automatic commit and start the transaction */$ mysqli-> autocommit (false ); /* insert */$ SQL _1 = "insert into transaction_test ('content') values ('shenyang ')"; $ query1 = $ mysqli-> query ($ SQL _1 ); if ($ query1 = false) {echo 'query1 failed
';} Else {printf ("insert_id one: % d
", $ Mysqli-> insert_id);}/* transaction rollback $ mysqli-> rollback (); * // * restore automatic submission */$ mysqli-> autocommit (true);/* insert */$ SQL _2 = "insert into transaction_test ('content ') values ('changchun ') "; $ query2 = $ mysqli-> query ($ SQL _2); if ($ query2 = false) {echo 'query2 failed
';} Else {printf ("insert_id two: % d
", $ Mysqli-> insert_id) ;}$ mysqli-> close ();?>

Running results and database results:

　　　　　　　　

Analysis: although transactions have been enabled before, the first insert statement is written into the database after automatic commit is restored by default, so no rollback () is written and not rolled back.

8 continue to test rollback ()

Remove the rollback () comment in the above Code

　　　　　　

 Query ($ SQL _set_charset); if ($ query = false) {echo 'set charset failed
'; Exit (0);}/* close automatic commit and start the transaction */$ mysqli-> autocommit (false ); /* insert */$ SQL _1 = "insert into transaction_test ('content') values ('shenyang ')"; $ query1 = $ mysqli-> query ($ SQL _1 ); if ($ query1 = false) {echo 'query1 failed
';} Else {printf ("insert_id one: % d
", $ Mysqli-> insert_id);}/* transaction rollback */$ mysqli-> rollback (); /* restore automatic submission */$ mysqli-> autocommit (true);/* insert */$ SQL _2 = "insert into transaction_test ('content') values ('changchun ') "; $ query2 = $ mysqli-> query ($ SQL _2); if ($ query2 = false) {echo 'query2 failed
';} Else {printf ("insert_id two: % d
", $ Mysqli-> insert_id) ;}$ mysqli-> close ();?>

Running results and database results:

　　　　　　　　

Analysis: the record with the id of 10 is not inserted into the database. This execution is rolled back.

Bytes --------------------------------------------------------------------------------------------------------------

9 based on the above tests, we should have a deep understanding of autocommit (), commit (), and rollback (), and finally paste a piece of code to simulate the real environment.

1
 Query ($ SQL _set_charset); 13 if ($ query = false) {14 echo 'set charset failed
'; 15 exit (0); 16} 17 18/* close automatic commit, start transaction */19 $ mysqli-> autocommit (false ); 20 21/* insert */22 $ commit = true; 23 $ SQL _array = array ("insert into transaction_test ('content') values ('shijiazhuang ')", "insert into transaction_test ('content2') values ('zhengzhou ')"); 24 foreach ($ SQL _array as $ key => $ item) {25 $ query = $ mysqli-> query ($ item); 26 if ($ query = false) {27/* transaction rollback */28 printf ("query failed: % d
", $ Key); 29 $ mysqli-> rollback (); 30 $ commit = false; 31 break; 32} else {33 printf (" insert_id $ key: % d
", $ Mysqli-> insert_id); 34} 35} 36 37/* commit transaction */38 if ($ commit) {39 $ mysqli-> commit (); 40} 41 42/* restore automatic submission */43 $ mysqli-> autocommit (true ); 44 45/* insert */46 $ SQL _2 = "insert into transaction_test ('content') values ('Last insert ')"; 47 $ query2 = $ mysqli-> query ($ SQL _2); 48 if ($ query2 = false) {49 echo 'query2 failed
'; 50} else {51 printf ("insert_id two: % d
", $ Mysqli-> insert_id); 52} 53 54 $ mysqli-> close (); 55?>

Analysis: The two SQL statements in line 23 in the above Code are correct. During the test, the test can be as follows: both are correct, 1 is correct, 2 is incorrect, 1 is incorrect, and 2 is correct, both 1 and 2 are incorrect.

Result: (the database is written only when both of them are correct, and the other three cases are rolled back)

The broader world depends on everyone.

It seems that there are few php users here, and there are more. net users who want to write useful code.

Author: Old Mo http://www.cnblogs.com/laomo/