對MySQL事務提交和復原的錯誤理解

對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；否則有任何一條執行錯誤，需要自己提交一條roolback，這時會復原所有操作，而不是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語句中有失敗的，就執行roolback，否則說明全部正確，執行commit。如下面的 commit_transaction()方法。

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

privat void execute(MYSQL m_sqlCon, string sqlStatement)
{
r = mysql_real_query(m_sqlCon, sqlStatement, (unsigned long)strlen(sqlStatement));
    if (r)
        m_flag = false; // 出錯則標記一下
}
public bool commit_transaction()
{
        int ret_error = 0;
        if (!m_flag)
        {
            cancel_transaction();
            return false;
        }
        else
        {
            if (!(ret_error = mysql_commit(m_sqlCon)))
            {
                cancel_transaction();
                return true;
            }
        }
        return true;
}

2.利用預存程序：

CREATE PROCEDURE PRO2()
BEGIN
    DECLARE t_error INTEGER;
    DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

    START TRANSACTION;
        INSERT INTO test_tab VALUES    (1, '2');
        INSERT INTO test_tab VALUES    (1, '3');
       
        IF t_error = 1 THEN
            ROLLBACK;
        ELSE
            COMMIT;
        END IF;
END

然後調用 CALL  PRO2()

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

本文永久更新連結地址：