Solutions to Mysql data rollback errors

The following section describes how to solve the Mysql data rollback error. For more information, see 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 manually modify them:

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.
/* Method 1 */

The code is as follows:

/************** 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 encoding;
// Start a transaction
Mysql_query ("BEGIN"); // or mysql_query ("start transaction ");
$ SQL = "INSERT INTO 'user' ('id', 'username', 'sex') VALUES (NULL, 'test1', '0 ')";
$ Sql2 = "insert into 'user' ('did', 'username', 'sex') VALUES (NULL, 'test1', '0 ')"; // I intentionally wrote an error.
$ Res = mysql_query ($ SQL );
$ Res1 = mysql_query ($ sql2 );
If ($ res & $ res1 ){
Mysql_query ("COMMIT ");
Echo 'submission successful. ';
} Else {
Mysql_query ("ROLLBACK ");
Echo 'data rollback. ';
}
Mysql_query ("END ");

/* Method 2 */

The code is as follows:

/*************** Transaction -- 2 *******************/
Mysql_query ("set autocommit = 0"); // sets that mysql is not automatically submitted and must be submitted using the commit statement.
$ SQL = "INSERT INTO 'user' ('id', 'username', 'sex') VALUES (NULL, 'test1', '0 ')";
$ Sql2 = "insert into 'user' ('did', 'username', 'sex') VALUES (NULL, 'test1', '0 ')"; // I intentionally wrote an error.
$ Res = mysql_query ($ SQL );
$ Res1 = mysql_query ($ sql2 );
If ($ res & $ res1 ){
Mysql_query ("COMMIT ");
Echo 'submission successful. ';
} Else {
Mysql_query ("ROLLBACK ");
Echo 'data rollback. ';
}
Mysql_query ("END"); // do not forget mysql_query ("set autocommit = 1") when the transaction is finished; submit automatically