mysql事務回滾使用與常見問題介紹

MYSQL中只有INNODB和BDB類型的數據表才能支持事務處理！其他的類型是不支持的！ ***：一般MYSQL數據庫默認的引擎是MyISAM,這種引擎不支持事務！如果要讓MYSQL支持事務，可以自己手動修改:

方法如下：

1.修改c:appservmysqlmy.ini文件，找到skip-InnoDB,在前面加上#，後保存文件。
2.在運行中輸入：services.msc,重啟mysql服務。
3.到phpmyadmin中，mysql->show engines;(或執行mysql->show variables like 'have_%'; ),查看InnoDB為YES,即表示數據庫支持InnoDB了。
也就說明支持事務transaction了。
4.在創建表時，就可以為Storage Engine選擇InnoDB引擎了。如果是以前創建的表，可以使用

 代碼如下 復制代碼 mysql->alter table table_name type=InnoDB;
或
mysql->alter table table_name engine=InnoDB;

來改變數據表的引擎以支持事務。

事務回滾在事務中，每個正確的原子操作都會被順序執行，直到遇到錯誤的原子操作，此時事務會將之前的操作進行回滾。回滾的意思是如果之前是插入操作，那麼會執行刪除插入的記錄，如果之前是update操作，也會執行update操作將之前的記錄還原。因此，正確的原子操作是真正被執行過的。

MYSQL的事務處理主要有兩種方法。
1、用begin,rollback,commit來實現
begin 開始一個事務
rollback 事務回滾
commit 事務確認
2、直接用set來改變mysql的自動提交模式
MYSQL默認是自動提交的，也就是你提交一個QUERY，它就直接執行！我們可以通過
set autocommit=0 禁止自動提交
set autocommit=1 開啟自動提交
來實現事務的處理。
當你用 set autocommit=0 的時候，你以後所有的SQL都將做為事務處理，直到你用commit確認或rollback結束。
注意當你結束這個事務的同時也開啟了個新的事務！按第一種方法只將當前的作為一個事務！

PHP實現MySQL事務回滾

創建一個測試的數據庫：

 代碼如下 復制代碼

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', '100');
Query OK, 1 row affected (0.00 sec)
PHP測試代碼：

$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` = `money` - 10*2 WHERE `user` = 'luchanghong'";
mysql_query($sql_2);

if (mysql_errno()){
    echo "error";
    mysql_query("ROLLBACK");
}else{
    echo "OK";
    mysql_query("COMMIT");
}

執行一次後查看數據庫：

 代碼如下 復制代碼

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)

那麼，我添加一個條件，就是每次更新完 user_account 表後檢查用戶的 money 是否為負值，如果為負值那麼就要撤銷之前的操作，執行事務回滾。

 代碼如下 復制代碼

$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;
}

接著，在shell下多次執行這php文件（WIN下就手動執行幾次吧），例如：

 代碼如下 復制代碼

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
再看數據庫數據：

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、為什麼auto_increament沒有回滾？
因為innodb的auto_increament的計數器記錄的當前值是保存在存內 存中的，並不是存在於磁盤上，當mysql server處於運行的時候，這個計數值只會隨著insert改增長，不會隨著delete而減少。而當mysql server啟動時，當我們需要去查詢auto_increment計數值時，mysql便會自動執行：SELECT MAX(id) FROM 表名 FOR UPDATE;語句來獲得當前auto_increment列的最大值，然後將這個值放到auto_increment計數器中。所以就算 Rollback MySQL的auto_increament計數器也不會作負運算。

2、MySQL的事務對表操作的時候是否是物理操作？
MySQL的事務是有redo和undo的，redo操作的所有信息都是記錄到 redo_log中，也就是說當一個事務做commit操作時，需要先把這個事務的操作寫到redo_log中，然後再把這些操作flush到磁盤上，當 出現故障時，只需要讀取redo_log,然後再重新flush到磁盤就行了。
而對於undo就比較麻煩，MySQL在處理事務時，會在數據共享 表空間裡申請一個段叫做segment段，用保存undo信息，當在處理rollback，不是完完全全的物理undo，而是邏輯undo,就是說會對之 前的操作進行反操作，但是這些共享表空間是不進行回收的。這些表空間的回收需要由mysql的master thread進程來進行回收。