PHP MySQL Transaction processing

Transactions are required to meet 4 conditions (ACID): atomicity (autmic), consistency (consistency), isolation (isolation), persistence (durability)

Atomicity (autmic): The transaction is in execution, to do "either do it or do it all!" ", which means that the transactional part is not allowed to execute. Even if the transaction can not be completed because of the failure, also eliminate the impact on the database when rollback!

Consistency (consistency): Transactional operations should allow the database to transition from one consistent state to another consistent state! Take online shopping, you only have to let the goods out of the library, and let the goods into the customer's shopping basket to constitute a business!

Isolation (Isolation): If more than one transaction executes concurrently, it should be done independently of each transaction!

Persistence (Durability): A successful execution of a transaction has a lasting effect on the database and should be recoverable even if the database should fail in error!

There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Acknowledgement
Rollback and commit cannot be used side by side when you use both, only the previous one is valid, and the latter is invalid, that is, you or perform commit or execute rollback
2, directly with set to change the MySQL automatic submission mode
MySQL is automatically submitted by default, that is, you submit a query, it is executed directly! We can pass
Set autocommit=0 prohibit auto-commit
Set autocommit=1 turn on auto-commit
To implement transaction processing.

Note that when you use set autocommit=0, all of your later SQL will be transacted until you end it with commit confirmation or rollback, and notice that when you end the transaction, you also open a new transaction! Press the first method to only present the current as a transaction!

Only InnoDB and BDB types of data tables in MySQL can support transactional processing! Other types are not supported!
: General MySQL Database The default engine is MyISAM, this engine does not support transactions! If you want to allow MySQL to support transactions, you can manually modify them:
The method is as follows: 1. Modify the C:\appserv\mysql\my.ini file, find the Skip-innodb, add # to the front, save the file.
2. In the operation, enter: services.msc, restart the MySQL service.
3. To phpMyAdmin, mysql->show engines; (or execute mysql->show variables like ' have_% '; ), viewing InnoDB as yes means that the database supports InnoDB.
It also indicates that the support transaction is transaction.
4. When creating a table, you can select the InnoDB engine for storage. If it is a previously created table, you can use Mysql->alter table table_name Type=innodb;
or Mysql->alter table table_name Engine=innodb; Change the engine of the data table to support transactions.

Example One.

PHP code

2. $conn = mysql_connect (' localhost ',' root ',' root ') or Die ("Data connection Error!!!");
4. mysql_select_db (' test ',$conn);
6. mysql_query ("Set names ' GBK '");   //Use GBK Chinese code;
8. Start a transaction
10. mysql_query ("BEGIN");   //or mysql_query ("START TRANSACTION");
12. $sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";
14. $sql 2 = "INSERT into ' user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')"; I wrote it wrong on purpose .
16. $res = mysql_query ($sql);
18. $res 1 = mysql_query ($sql 2);
20. if ($res && $res 1) {
22. mysql_query ("COMMIT");
24. Echo ' submitted successfully.   ‘;
26. }else{
28. mysql_query ("ROLLBACK");
30. echo ' data rollback.   ‘;
32. }
34. mysql_query ("END");

Example Two

PHP code

2. mysql_query ("SET autocommit=0"); //Set MySQL not to submit automatically, you need to commit the commit statement by yourself
4. $sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";
6. $sql 2 = "INSERT into ' user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')"; I wrote it wrong on purpose .
8. $res = mysql_query ($sql);
10. $res 1 = mysql_query ($sql 2);
12. if ($res && $res 1) {
14. mysql_query ("COMMIT");
16. Echo ' submitted successfully.   ‘;
18. }else{
20. mysql_query ("ROLLBACK");
22. echo ' data rollback.   ‘;
24. }
26. mysql_query ("END"); //Do not forget mysql_query ("SET autocommit=1") when the transaction is finished; autocommit

For MyISAM engine databases that do not support transactions, you can use table locking methods

MyISAM & InnoDB all support,
/*
Lock tables can lock a table for the current thread. If the table is locked by another thread, it will clog until all locks can be obtained.
UNLOCK tables can release any locks held by the current thread. When a thread publishes another lock tables, or when the connection to the server is closed, all tables locked by the current thread are implicitly unlocked.
*/

PHP code

2. mysql_query ("LOCK TABLES ' user ' WRITE"); Lock the ' user ' table
4. $sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";
6. $res = mysql_query ($sql);
8. if ($res) {
10. Echo ' submitted successfully.! ';
12. }else{
14. echo ' failure! ';
16. }
18. mysql_query ("UNLOCK TABLES"); Release lock
    

PHP MySQL Transaction processing