A brief analysis of MySQL transaction processing and table locking in depth _mysql

There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Confirmation
2, directly with the set to change the MySQL automatic submission mode
MySQL default is automatically submitted, that is, you submit a query, it directly executed! We can pass
Set autocommit=0 prohibit automatic submission
Set autocommit=1 turn on automatic submission
To implement transaction processing.
When you use Set autocommit=0, all of your later SQL will be transacted until you end with a commit or rollback.
Note that when you end this business, you start a new business! The first method will only present as a transaction!
The first method of personal recommendation!

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

Copy Code code as follows:

mysql_query ("BEGIN"); or mysql_query ("START TRANSACTION");
If you do not use a transaction, $sql execution succeeds, $sql 1 execution fails
$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 the transaction was used, both inserts failed
if ($res && $res 1) {
mysql_query ("COMMIT");
}
else{
mysql_query ("ROLLBACK");
}
mysql_query ("End");
mysql_query ("SET autocommit=0"); Setup MySQL does not self submit, you need to use the commit language to submit
$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);
Two inserts failed because of the use of the object
if ($res && $res 1) {
mysql_query ("COMMIT");
}
else{
mysql_query ("ROLLBACK");
}
mysql_query ("End");

Table locking methods are available for MyISAM engine databases that do not support transactions:
The code is as follows:

Copy Code code as follows:

MyISAM & InnoDB are all supported,
Notes:query statements 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);