Mysql transaction rollback usage and FAQs

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:

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: apervmysqlmy. ini file, find skip-InnoDB, add # in front, and save the file.
2. Enter services. msc in the running state to restart the mysql service.
3. 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. You can use

The Code is as follows:
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 transaction is rolled back in the transaction. Every correct atomic operation is executed sequentially until an incorrect atomic operation is encountered. At this time, the transaction rolls back the previous operation. Rollback means that if the previous operation is an insert operation, the inserted records will be deleted. If the previous operation is an update operation, the update operation will be executed to restore the previous records. Therefore, correct atomic operations are actually executed.

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!

MySQL transaction rollback using PHP

Create a test:

The Code is as follows:

Mysql> create database 'shop _ test' default charset = utf8 COLLATE = utf8_general_ci; Query OK, 1 row affected (0.00 sec) Mysql> use shop_test; Database changed Mysql> create table if not exists 'user _ account '( -> 'User' varchar (20) not null, -> 'Money' INT (10) not null, -> Primary key ('user ') ->) ENGINE = InnoDB default charset = utf8 COLLATE = utf8_general_ci; Query OK, 0 rows affected (0.51 sec) Mysql> create table if not exists 'user _ order '( -> 'Id' INT (10) not null, -> 'User' VARCHAR (20) not null, -> 'Price' INT (10) not null, -> 'Count' INT (10) not null, -> Primary key ('id ') ->) ENGINE = InnoDB default charset = utf8 COLLATE = utf8_general_ci AUTO_INCREMENT = 1; Query OK, 0 rows affected (0.33 sec) Mysql> insert into 'user _ account' VALUES ('luchanghong ', '123 '); Query OK, 1 row affected (0.00 sec) PHP test code: $ Conn = mysql_connect ('127. 0.0.1 ', 'root', 'root '); Mysql _ db ('shop _ test '); Mysql_query ('set NAMES utf8 '); # Start transaction Mysql_query ("start transaction "); $ SQL = "INSERT INTO 'user _ order' VALUES ('1', 'luchanghong ', '10', '2 ')"; Mysql_query ($ SQL ); $ SQL _2 = "UPDATE 'user _ account' SET 'money' = 'money'-10*2 WHERE 'user' = 'luchanghong '"; Mysql_query ($ SQL _2 ); If (mysql_errno ()){ Echo "error "; Mysql_query ("ROLLBACK "); } Else { Echo "OK "; Mysql_query ("COMMIT "); }

View the database after one execution:

The Code is as follows:

Mysql> SELECT * FROM 'user _ account '; + ------------- + ------- + | User | money | + ------------- + ------- + | Luchanghong | 80 | + ------------- + ------- + 1 row in set (0.00 sec) Mysql> SELECT * FROM 'user _ order '; + ---- + ------------- + ------- + | Id | user | price | count | + ---- + ------------- + ------- + | 1 | luchanghong | 10 | 2 | + ---- + ------------- + ------- + 1 row in set (0.00 sec)

Then, I add a condition to check whether the user's money is negative after each update of the user_account table. If the value is negative, we need to cancel the previous operation and execute transaction rollback.

The Code is as follows:

$ Conn = mysql_connect ('127. 0.0.1 ', 'root', 'root '); Mysql_select_db ('shop _ test '); Mysql_query ('set NAMES utf8 '); // Start transaction Mysql_query ("start transaction "); $ SQL = "INSERT INTO 'user _ order' ('user', 'price', 'Count') VALUES ('luchanghong ', '10', '2 ')"; Mysql_query ($ SQL ); $ SQL _2 = "UPDATE 'user _ account' SET 'money' = 'money'-10*2 WHERE 'user' = 'luchanghong '"; Mysql_query ($ SQL _2 ); If (mysql_errno ()){ Echo "error n "; Mysql_query ("ROLLBACK "); } Else { $ Money = check_remain_money ('luchanghong '); Echo $ money .""; If ($ money <0 ){ Echo "No enough money n "; Mysql_query ("ROLLBACK "); } Else { Echo "OK n "; Mysql_query ("COMMIT "); } } Function check_remain_money ($ user ){ $ SQL = "SELECT 'money' FROM 'user _ account' WHERE 'user' = '{$ user }'"; $ Result = mysql_fetch_assoc (mysql_query ($ SQL )); Return! Empty ($ result )? $ Result ['money']: 0; }

Next, execute the PHP file multiple times in shell (manually execute the PHP file several times in WIN), for example:

The Code is as follows:

Lch @ LCH :~ /Desktop $ for x in 'seq 6'; do php transaction. php; done 60 OK 40 OK 20 OK 0 OK -20 No enough money -20 No enough money Let's look at the database data again: Mysql> SELECT * FROM 'user _ account '; + ------------- + ------- + | User | money | + ------------- + ------- + | Luchanghong | 0 | + ------------- + ------- + 1 row in set (0.00 sec) Mysql> SELECT * FROM 'user _ order '; + ---- + ------------- + ------- + | Id | user | price | count | + ---- + ------------- + ------- + | 1 | luchanghong | 10 | 2 | | 2 | luchanghong | 10 | 2 | | 3 | luchanghong | 10 | 2 | | 4 | luchanghong | 10 | 2 | | 5 | luchanghong | 10 | 2 | + ---- + ------------- + ------- + 5 rows in set (0.00 sec)

1. Why is auto_increament not rolled back?
Because the current value of the innodb auto_increament counter record is saved in the memory instead of on the disk, when the mysql server is running, this Count value will only increase with the change of insert, and will not decrease with the decrease of delete. When mysql server is started, when we need to query the auto_increment Count value, mysql will automatically execute: select max (id) FROM table name for update; statement to obtain the maximum value of the current auto_increment column, and then put this value in the auto_increment counter. Therefore, even the Rollback MySQL auto_increament counter does not perform negative operations.

2. Are MySQL transactions physical operations performed on tables?
MySQL transactions have redo and undo operations, and all information of redo operations is recorded in redo_log. That is to say, when a transaction performs a commit operation, you need to write the transaction operation to redo_log first, and then flush these operations to the disk. When a fault occurs, you only need to read redo_log, then, flush the disk again.
However, it is troublesome for undo. when MySQL processes transactions, it will apply for a segment in the data sharing tablespace called the segment and save the undo information. When it is processing rollback, it is not a complete physical undo, but a logical undo, that is, the previous operations will be reversed, but these shared tablespace will not be recycled. The recovery of these tablespaces must be performed by the mysql master thread process.