對mysql事務提交、回滾的錯誤理解

一、起因

　　begin或者START TRANSACTION開始一個事務

　　rollback事務回滾
　　commit 事務確認

　人們對事務的解釋如下：事務由作為一個單獨單元的一個或多個SQL語句組成，如果其中一個語句不能完成，整個單元就會回滾（撤銷），所有影響到的數據將返回到事務開始以前的狀態。因而，只有事務中的所有語句都成功地執行才能說這個事務被成功地執行。

　 這句話本身沒有什麼問題，問題是我給理解錯了，我測試中問題描述為如下:

　mysql事務中有兩條insert語句，其中第二條語句是錯誤的，在運行完事務後，第一條仍然插進去了，代碼如下。

 //創建表：
CREATE TABLE `test_tab` (
`f1`  int(11) NOT NULL ,
`f2`  varchar(11)  DEFAULT NULL ,
PRIMARY KEY (`f1`)
)
ENGINE=InnoDB

//執行事務：
START TRANSACTION;
INSERT INTO test_tab VALUES    (1, '2');
INSERT INTO test_tab VALUES    (1, '3');
COMMIT;

（錯誤：這隻是我一開始的認為）一開始認為只要把事務寫出來，最後用commit提交一下，資料庫會自動判斷這些語句是否全執行成功，如果成功則把所有的數據插入到資料庫，如果有一條失敗就自動回滾至原始狀態！顯然我認為錯了。

我執行上面的語句後的結果是：

[SQL]START TRANSACTION;

受影響的行: 0

時間: 0.000s

[SQL]

INSERT INTO test_tab VALUES (1, '2');

受影響的行: 1

時間: 0.001s

[SQL]

INSERT INTO test_tab VALUES (1, '3');

[Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

我們看結果可以知道INSERT INTO test_tab VALUES (1, '3');這一句因為主鍵衝突運行失敗，從而這一條下麵的commit也沒有執行。

需要註意的是：這時已經開啟了一個事務，並且已經執行了一條正確的插入語句，雖然沒有體現在資料庫中，但如果以後在該連接中又執行了一條commit 或begin或start transaction（新開一個事務會將該鏈接中的其他未提交的事務提交，相當於commit！）你會發現已經將剛纔的INSERT INTO test_tab VALUES (1, '2');寫進了資料庫。

所以事務的回滾不是這麼理解的，正確的理解應該是，如果事務中所有sql語句執行正確則需要自己手動提交commit；否則有任何一條執行錯誤，需要自己提交一條rollback，這時會回滾所有操作，而不是commit會給你自動判斷和回滾。

二 解決辦法

1. C++調用方式：（簡單示例）

先定義一個變數標誌bool m_flag=true;

再執行事務和sql語句如：（execute為自己寫的函數，如下）

execute( m_sqlCon, “begin”);

execute(m_sqlCon,”INSERT INTO test_tab VALUES (1, '2')”);

execute(m_sqlCon,”INSERT INTO test_tab VALUES (1, '3')”);

如果執行過程中任意一語句出錯則將該標誌m_flag置為false。

這時不應該去提交commit，而是用一個函數去判斷標誌是否為false，如果為false說明執行的sql語句中有失敗的，就執行rollback，否則說明全部正確，執行commit。如下麵的 commit_transaction()方法。

代碼大致如下，如使用需要修改！

 1 privat void execute(MYSQL m_sqlCon, string sqlStatement)
 2 {
 3 r = mysql_real_query(m_sqlCon, sqlStatement, (unsigned long)strlen(sqlStatement));
 4     if (r)
 5         m_flag = false; // 出錯則標記一下
 6 }
 7 public bool commit_transaction()
 8 {
 9         int ret_error = 0;
10         if (!m_flag)
11         {
12             cancel_transaction();
13             return false;
14         }
15         else
16         {
17             if (!(ret_error = mysql_commit(m_sqlCon)))
18             {
19                 cancel_transaction();
20                 return true;
21             }
22         }
23         return true;
24 }

2.利用存儲過程：

 1 CREATE PROCEDURE PRO2()
 2 BEGIN
 3     DECLARE t_error INTEGER;
 4     DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
 5 
 6     START TRANSACTION;
 7         INSERT INTO test_tab VALUES    (1, '2');
 8         INSERT INTO test_tab VALUES    (1, '3');
 9         
10         IF t_error = 1 THEN
11             ROLLBACK;
12         ELSE
13             COMMIT;
14         END IF;
15 END

然後調用 CALL  PRO2()

這個直接可以利用mysql去決定他應該是回滾還是提交。