PHP--PHP 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, 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.

*/

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

$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!");

mysql_select_db (' Test ', $conn);

mysql_query ("Set names ' GBK '"); Use GBK Chinese code;

Start a transaction

mysql_query ("BEGIN"); or mysql_query ("START TRANSACTION");

$sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";

$sql 2 = "INSERT into ' user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";//This one I deliberately wrote wrong

$res = mysql_query ($sql);

$res 1 = mysql_query ($sql 2);

if ($res && $res 1) {

mysql_query ("COMMIT");

Echo ' submitted successfully. ‘;

}else{

mysql_query ("ROLLBACK");

echo ' data rollback. ‘;

}

mysql_query ("END");

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

/* Method Two */

mysql_query ("SET autocommit=0"); Set MySQL not to submit automatically, you need to commit the commit statement by itself

$sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";

$sql 2 = "INSERT into ' user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";//This one I deliberately wrote wrong

$res = mysql_query ($sql);

$res 1 = mysql_query ($sql 2);

if ($res && $res 1) {

mysql_query ("COMMIT");

Echo ' submitted successfully. ‘;

}else{

mysql_query ("ROLLBACK");

echo ' data rollback. ‘;

}

mysql_query ("END"); Don't forget mysql_query ("SET autocommit=1") when the transaction is finished; autocommit

/****************** table locking methods are available for MyISAM engine databases that do not support transactions: ********************/

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.

*/

mysql_query ("Lock TABLES ' user ' WRITE");//Lock ' user ' table

$sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";

$res = mysql_query ($sql);

if ($res) {

Echo ' submitted successfully.! ';

}else{

echo ' Failure! ';

}

mysql_query ("UNLOCK TABLES");//Unlocked

PHP--PHP transaction processing