Solutions to Mysql data rollback errors

Source: Internet
Author: User
Welcome to the Linux community forum and interact with 2 million technical staff to introduce how to solve Mysql data rollback errors. You can refer to the following two methods for MYSQL transaction processing. 1. Use begin, rollback, and commit to start a transaction. rollback the transaction and roll back the commit transaction. 2

Welcome to the Linux community forum, and interact with 2 million technical staff> here, we will introduce how to solve the Mysql data rollback error. You can refer to the following two methods for MYSQL transaction processing. 1. Use begin, rollback, and commit to start a transaction. rollback the transaction and roll back the commit transaction. 2

Welcome to the Linux community forum and interact with 2 million technicians>

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 ')"; // 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 */

/*************** 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.