PHP MySQL Transaction processing

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

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.

When you use Set autocommit=0, all of your later SQL will be transacted until you end with commit confirmation or rollback.

Note that when you end this transaction, you also open a new transaction! Press the first method to only present the current as a transaction!

The first method of personal recommendation!

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, and 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; To change the engine of the data table to support transactions.

Here is a sample code for my test:

/*************** transaction--1 ***************/

/* Method One */

mysql_query ("BEGIN");//or mysql_query ("START TRANSACTION"); //If you do not use the mission, $sql execute the success, $sql 1 failed to perform$sql="INSERT into test values (' One ', ' # ')"; $sql 1="INSERT into test values (' One ', ' a ', ' 444 ')"; $res=mysql_query ($sql); $res 1=mysql_query ($sql 1); //because of the use of the task, both inserts fail.       if($res &&$res 1) {mysql_query ("COMMIT"); }       Else{mysql_query ("ROLLBACK"); } mysql_query ("END");

/**************** transaction--2 *******************/

/* Method Two */

       mysql_query("SET autocommit=0");//setup MySQL does not self-commit, it should be submitted by itself in a commit language       $sql= "INSERT into test values (' 11 ', ' 88 ')"; $sql 1= "INSERT into test values (' 11 ', ' 88 ', ' 444 ')"; $res=mysql_query($sql); $res 1=mysql_query($sql 1); //because of the use of the task, both inserts fail.       if($res&&$res 1){          mysql_query("COMMIT"); }       Else{          mysql_query("ROLLBACK"); }       mysql_query("END");

/*************************** END ***************/

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

The code is as follows:

MyISAM & InnoDB all support,
Notes:query words cannot be written together such as: mysql_query ("SELECT * from A;select * from B;");

$sql _1= "LOCK TABLES test WRITE";mysql_query($sql _1);$sql _2= "INSERT into Test VALUES ('".$a."‘,‘".$b."‘) ";if(mysql_query($sql _2)){        Echo' successful! '; }Else{        Echo' unsuccessful! '; }    $sql _3= "UNLOCK TABLES"; mysql_query($sql _3);

/*************************** END ***************/

PHP MySQL Transaction processing