In-depth analysis of mysql transaction processing and table lock

There are two main methods to process MYSQL transactions.
1. Use begin, rollback, and commit to implement
Start a transaction
Rollback transaction rollback
Commit transaction validation
2. directly use set to change the mysql automatic submission Mode
MYSQL is automatically submitted by default, that is, when you submit a QUERY, It will be executed directly! We can use
Set autocommit = 0 disable automatic submission
Set autocommit = 1 enable automatic submission
To process the transaction.
When you use set autocommit = 0, all your SQL statements will be processed as transactions until you use commit to confirm or roll back.
Note that a new transaction is also started when you end the transaction! In the first method, only the current transaction is used!
The first method is recommended!

In MYSQL, only INNODB and BDB Data Tables support transaction processing! Other types are not supported!
* **: The default MYSQL database engine is MyISAM, which does not support transactions! If you want MYSQL to support transactions, You can manuallyModify:
The method is as follows:
1. Modify the c: \ appserv \ mysql \ my. ini file, find skip-InnoDB, add # to the front, and save the file.
2. Enter services. msc in the running state to restart the mysql service.
3. in phpmyadmin, mysql-> show engines; (or execute mysql-> show variables like 'have _ % ';). If InnoDB is YES, InnoDB is supported.
This means that transaction is supported.
4. When creating a table, you can select the InnoDB Engine for the Storage Engine. For 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 data table engine to support transactions.
The following is the sample code for testing.Copy codeThe Code is as follows: mysql_query ("BEGIN"); // or mysql_query ("start transaction ");
// If no transaction is used, $ SQL is successfully executed and $ sql1 fails to be executed.
$ SQL = "insert into test values ('11', '88 ')";
$ Sql1 = "insert into test values ('11', '88 ', '123 ')";
$ Res = mysql_query ($ SQL );
$ Res1 = mysql_query ($ sql1 );
// Because the transaction is used, both insert operations fail.
If ($ res & $ res1 ){
Mysql_query ("COMMIT ");
}
Else {
Mysql_query ("ROLLBACK ");
}
Mysql_query ("END ");
Mysql_query ("set autocommit = 0"); // If mysql is SET to not automatically submitted, you must submit it using the commit statement.
$ SQL = "insert into test values ('11', '88 ')";
$ Sql1 = "insert into test values ('11', '88 ', '123 ')";
$ Res = mysql_query ($ SQL );
$ Res1 = mysql_query ($ sql1 );
// Because transactions are used, both insert operations fail.
If ($ res & $ res1 ){
Mysql_query ("COMMIT ");
}
Else {
Mysql_query ("ROLLBACK ");
}
Mysql_query ("END ");

For MyISAM engine databases that do not support transactions, you can use the table locking method:
The Code is as follows:Copy codeThe Code is as follows: // MyISAM & InnoDB support,
// Notes: The query statement cannot be written together, for example, 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 );