In depth analysis of mysql transaction processing and table locking

There are two main approaches to transaction processing in MYSQL.
1, begin,rollback,commit
begin starts a transaction
rollback transaction rollback
commit transaction validation
2. Directly use set to change the auto-submit mode of mysql
MYSQL is automatically committed by default, which means you submit an QUERY and it executes! We can go through
set autocommit=0 disables automatic submission
set autocommit=1 enables automatic submission
To implement transaction processing.
When you use set autocommit=0, all of your SQL will be treated as transactions until you confirm with commit or rollback.
Note that when you end this transaction, you also start a new one! Use method 1 to treat the current as one transaction only!
Personal recommendation to use the first method!

Only tables of type INNODB and BDB in MYSQL support transactions! Other types are not supported!
*** : 1 general MYSQL database default engine is MyISAM, this engine does not support transactions! If you want MYSQL to support transactions, you can manually modify it yourself:
The method is as follows:
1. Modify the c:\appserv\mysql\ my.ini file, find the skip-InnoDB, put the # in front, and save the file.
2. Enter services.msc at run time and restart the mysql service.
3. Go to phpmyadmin, mysql- > show engines; (or perform mysql- > show variables like 'have_ %'; ), see InnoDB as YES, which means the database supports InnoDB.
This means that transaction transaction is supported.
4. When you create the table, you can select the InnoDB engine for Storage Engine. If you created a table before, 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 the sample code for the test

 
mysql_query("BEGIN"); // or mysql_query("START TRANSACTION"); 
// If you do not use the transaction, the $sql Successful execution, $sql1 On failure  
$sql = " insert into test values('11','88') "; 
$sql1 = " insert into test values('11','88','444') "; 
$res = mysql_query($sql); 
$res1 = mysql_query($sql1); 
// Because of the use of transactions, the insert All failed to execute  
if($res && $res1){ 
mysql_query("COMMIT"); 
} 
else{ 
mysql_query("ROLLBACK"); 
} 
mysql_query("END"); 
mysql_query("SET AUTOCOMMIT=0"); // � buy mysql Do not submit from the copyright. Use it by yourself commit � is submitted  
$sql = " insert into test values('11','88') "; 
$sql1 = " insert into test values('11','88','444') "; 
$res = mysql_query($sql); 
$res1 = mysql_query($sql1); 
// Because you use things, you have two insert All failed to execute  
if($res && $res1){ 
mysql_query("COMMIT"); 
} 
else{ 
mysql_query("ROLLBACK"); 
} 
mysql_query("END"); 

Table locking methods can be used for MyISAM engine databases that do not support transactions:
The code is as follows:

 
//MyISAM & InnoDB  All support , 
//Notes:query The statement cannot be written in 1 Since 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);