Analysis of Mysql data Rollback Error solution _ MySQL

Analysis of Mysql data Rollback Error solution bitsCN.com

Solutions to Mysql data rollback errors

This section describes how to solve Mysql data rollback errors. 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 * // **************** 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') "; // the error code $ res = mysql_query ($ SQL) is written on purpose ); $ res1 = mysql_query ($ sql2); if ($ res & $ res1) {mysql_query ("COMMIT"); echo 'submitted successfully. ';} Else {mysql_query ("ROLLBACK"); echo' data ROLLBACK. ';} Mysql_query ("END "); /* method 2 ************** * ***/mysql_query ("set autocommit = 0 "); // If mysql is not automatically submitted, you must use the commit statement to submit $ SQL = "INSERT INTO 'user' ('id', 'username', 'sex') VALUES (NULL, 'test1', '0') "; $ sql2 =" insert into 'user' ('did', 'username', 'sex') VALUES (NULL, 'test1 ', '0') "; // The Error $ res = mysql_query ($ SQL); $ res1 = mysql_query ($ sql2); if ($ res & $ res1) {mysql_query ("COMMIT"); echo 'submitted successfully. ';} Else {mysql_query ("ROLLBACK"); echo' data ROLLBACK. ';} Mysql_query ("END"); // do not forget mysql_query ("set autocommit = 1") after the transaction is processed; submit automatically

BitsCN.com