MySQL 的 Transaction Commit/Rollback

 

• 不能在  Function/Trigger 裡面指定使用 Commit/Rollback
• 使用 stored procedure 裡面要處理 error handling, rollback等問題

• SET autocommit=1; 
• 這是一個 SESSION Variable
• 如果 有 明顯的：BEGIN...END/ START TRANSACTION  COMMIT/ROLLBACK，則在這個區間則是 會自動暫時設 SET AUTOCOMMIT=0，執行完畢 COMMIT / ROLLBACK 後就自動回復 on！
• 配合 ERROR HANDLER 做 TRANSACTION 控制。

• SAMPLE PROCEDURE:

proc_label: BEGIN

-- 輸入參數

-- 輸出參數： OUT out_ret_code VARCHAR(200);

DECLARE v_day_amount_limit INT(11);

DECLARE v_today_in_amount_accu INT(11);

DECLARE EXIT handler for sqlexception

BEGIN

GET DIAGNOSTICS CONDITION 1

@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;

ROLLBACK;

-- SET out_ret_code = "ERROR:sqlexception";

SET out_ret_code = (SELECT concat("RETURNED_SQLSTATE：",@p1 ," MESSAGE_TEXT：" ,@p2 ));

CALL system_log(CONCAT("sqlexception:",out_ret_code));

END;

DECLARE EXIT handler for sqlwarning

BEGIN

GET DIAGNOSTICS CONDITION 1

@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;

ROLLBACK;

SET out_ret_code = (SELECT concat("RETURNED_SQLSTATE：",@p1 ," MESSAGE_TEXT：" ,@p2 ));

CALL system_log(CONCAT("sqlwarning:",out_ret_code));

END;

-- 檢查輸入參數的程式碼。所有可能傳進來錯誤的參數情形，先做檢查、排除、紀錄，產生錯誤代碼、記錄到LOG裡面、停止程式、傳回錯誤。

-- 最後，才做 Transaction 寫入資料庫。

-- 也就是Transaction 裡面，只要有純粹的資料庫寫入更新的動作就好。

-- 如果萬一有資料庫寫入更新本身的錯誤，才產生 Rollback，沒有錯誤就直接 Commit

START TRANSACTION;

-- Program.....

COMMIT;

SET out_ret_code="SUCCESS";

END