MySQL transaction rollback usage and FAQ

The method is as follows:

1. Modify the C:appservmysqlmy.ini file, find the Skip-innodb, add the # in front, and save the file.
2. In operation input: services.msc, restart the MySQL service.
3. In phpMyAdmin, mysql->show engines; (or perform mysql->show variables like ' have_% '; To see InnoDB as yes, which means that the database supports InnoDB.
It also means that support services are transaction.
4. When you create a table, you can select the InnoDB engine for storage engine. If you have previously created a table, you can use the

The code is as follows	Copy Code
Mysql->alter table table_name Type=innodb; Or Mysql->alter table table_name Engine=innodb;

To change the engine of the datasheet to support the transaction.

Transaction rollback in a transaction, each correct atomic operation is executed sequentially until an incorrect atomic operation is encountered, at which point the transaction rolls back the previous operation. Rollback means that if the previous insert operation, then the record of the delete insert is performed, and if the previous update operation, an update operation is performed to restore the previous record. Therefore, the correct atomic operation is actually performed.

There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Confirmation
2, directly with the set to change the MySQL automatic submission mode
MySQL default is automatically submitted, that is, you submit a query, it directly executed! We can pass
Set autocommit=0 prohibit automatic submission
Set autocommit=1 turn on automatic submission
To implement transaction processing.
When you use Set autocommit=0, all of your later SQL will be transacted until you end with a commit or rollback.
Note that when you end this business, you start a new business! The first method will only present as a transaction!

PHP implementation MySQL transaction rollback

To create a test database:

The code is as follows

Copy Code

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 not NULL, -> ' Money ' INT (a) 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 (a) not NULL, -> ' user ' VARCHAR not NULL, -> ' Price ' INT (a) not NULL, -> ' Count ' INT (a) 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 ', ' 100 '); Query OK, 1 row Affected (0.00 sec) PHP Test Code: $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 ' VALUES (' 1 ', ' luchanghong ', ' 10 ', ' 2 ')"; mysql_query ($sql); $sql _2 = "UPDATE ' user_account ' SET '" = ' 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:

code is as follows

copy code

mysql> SELECT * from ' user_account '; +-------------+-------+ | user& nbsp;       | Money | +-------------+-------+ | Luchanghong |    | +-------------+-------+ 1 row in Set (0.00 SEC) mysql> SELECT * from ' user_order '; +----+-------------+-------+-------+ | id | user  &n bsp;     | Price | Count | +----+-------------+-------+-------+ |  1 | luchanghong |    |     2 | +----+-------------+-------+-------+ 1 row in Set (0.00 sec)

So, I add a condition that checks whether the user's money is negative after each update of the User_account table, and if it is negative, undo the previous action and perform a transaction rollback.

The code is as follows

Copy Code

$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 '-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 under the shell (win is executed several times manually), for example:

The code is as follows

Copy Code

Lch@lch:~/desktop $ for x in ' seq 6 '; do PHP transaction.php; Done OK OK OK 0 OK -20 No enough money -20 No enough money Then look at the database data: 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 |    |     2 |< br> |  2 | Luchanghong |    |     2 | |  3 | luchanghong |    |     2 | |  4 | luchanghong |    |     2 | |  5 | luchanghong |    |     2 | +----+-------------+-------+-------+ 5 rows in Set (0.00 sec)

1, why auto_increament did not roll back?
Because the current value of the InnoDB's auto_increament counter record is stored in memory, not on disk, and when the MySQL server is running, the count will only grow with the insert change and will not decrease with the delete. And when MySQL server starts, when we need to query the auto_increment count value, MySQL will automatically execute: SELECT MAX (ID) from table name for UPDATE; statement to get current auto_ Increment the maximum value of the column, and then place the value in the Auto_increment counter. So even Rollback MySQL's auto_increament counter does not make negative calculations.

2, MySQL transactions on the table when the operation is a physical operation?
MySQL transactions are redo and undo, and all information about redo operations is recorded in Redo_log, which means that when a transaction commits, it is necessary to write the operation of the transaction to the Redo_log and then flush the operation to disk, when In the case of a failure, you only need to read the Redo_log and then flush to disk again.
And for Undo is more trouble, MySQL in the processing of transactions, will be in the Data sharing table space in the application of a paragraph called segment paragraph, with the Save undo information, when processing rollback, not completely physical undo, but the logical undo, which means that will be Operation, but these shared tablespaces are not recycled. The recovery of these tablespace needs to be reclaimed by the MySQL master thread process.