1.在循環(huán)中提交的問題
很多開發(fā)人員非常喜歡在循環(huán)中進行事務提交，下面演示一個他們經(jīng)常寫的一個存儲過程示例，如下所示：
DROP?PROCEDURE?IF?EXISTS?load1;CREATE?PROCEDURE?load1(count?INT?UNSIGNED)BEGIN ???DECLARE?s?INT?UNSIGNED?DEFAULT?1; ???DECLARE?c?CHAR(80)?DEFAULT?REPEAT('a',80);???WHILE?s?<p>在上面的例子中，是否加上commit命令并不是關(guān)鍵。由于MySQL innodb的存儲引擎默認為自動提交，因此去掉存儲過程中的commit結(jié)果是一樣的。如下所示，下面也是另一個容易被開發(fā)人員忽視的問題：</p><pre class="brush:php;toolbar:false">DROP?PROCEDURE?IF?EXISTS?load2;?CREATE?PROCEDURE?load2(count?INT?UNSIGNED)BEGIN ???DECLARE?s?INT?UNSIGNED?DEFAULT?1; ???DECLARE?c?CHAR(80)?DEFAULT?REPEAT('a',80);???WHILE?s?<p>不論上面哪個存儲過程，當發(fā)生錯誤時，數(shù)據(jù)庫會停留在一個未知的位置。例如我們要插入10000條數(shù)據(jù)，但是在插入5000條時發(fā)生了錯誤，然而這5000條已經(jīng)存放在了數(shù)據(jù)庫中，我們?nèi)绾翁幚恚苛硗庖粋€是性能問題，上面的兩個存儲過程都不會比下面的這個存儲過程快，因為下面這個是將insert放在了一個事務中：</p><pre class="brush:php;toolbar:false">DROP?PROCEDURE?IF?EXISTS?load3;?CREATE?PROCEDURE?load3(count?INT?UNSIGNED)BEGIN ???DECLARE?s?INT?UNSIGNED?DEFAULT?1; ???DECLARE?c?CHAR(80)?DEFAULT?REPEAT('a',80); ???START?TRANSACTION;???WHILE?s?<p>對于上面三個存儲過程，我們分別插入100萬數(shù)據(jù)來比較執(zhí)行時間，如下所示，顯然可以看到第三種方法要快很多，這是因為每次提及都要寫一次重做日志，所以load1和load2實際寫了100萬次重做日志。對于存儲過程load3,我們只寫了1次重做日志。</p><p>先準備一個測試表</p><pre class="brush:php;toolbar:false">CREATE?TABLE?`t1`?(`id`?int?NOT?NULL?AUTO_INCREMENT?,`name`?varchar(500)?NULL?, PRIMARY?KEY?(`id`) )?;
執(zhí)行測試
09:50:44?test>?call?load1(1000000); Query?OK,?0?rows?affected?(1?min?4.90?sec)09:54:23?test>?truncate?table?t1; Query?OK,?0?rows?affected?(0.05?sec)09:54:25?test>?call?load2(1000000); Query?OK,?1?row?affected?(1?min?3.38?sec)09:55:32?test>?truncate?table?t1; Query?OK,?0?rows?affected?(0.20?sec)09:55:58?test>?call?load3(1000000); Query?OK,?0?rows?affected?(33.90?sec)
對于第二個存儲過程load2,我們也可以人為的開啟下事務，同樣可以達到存儲過程load3的效果，執(zhí)行時間如下所示：
09:57:42?test>?begin; Query?OK,?0?rows?affected?(0.00?sec)09:57:46?test>?call?load2(1000000); Query?OK,?1?row?affected?(34.08?sec)09:58:26?test>?commit; Query?OK,?0?rows?affected?(0.76?sec)
2.關(guān)于使用自動提交
在一些特殊場景下，有時候自動提交不一定是個好的事情， 如我們上面講到的循環(huán)提交的問題，MySQL數(shù)據(jù)庫默認是自動提交（autocommit)?？梢酝ㄟ^如下方式來改變MySQL的提交方式：
10:35:34?test>?SET?AUTOCOMMIT=0; Query?OK,?0?rows?affected?(0.00?sec)
也可以使用START TRANSATION或者BEGIN顯示的開啟一個事務。MySQL會自動執(zhí)行
SET AUTOCOMMIT=0，并在COMMIT或ROLLBACK結(jié)束一個事務后執(zhí)行SET AUTOCOMMIT=1 。
3.使用自動回滾處理異常 當存儲過程發(fā)生異常的時候怎么辦，Innodb存儲引擎支持通過一個HANDLER來進行事務的自動回滾操作。如在存儲過程中發(fā)生錯誤會自動進行回滾操作。如下面一個示例：
CREATE?PROCEDURE?sp_auto_rollback_demo()BEGIN ???DECLARE?EXIT?HANDLER?FOR?SQLEXCEPTION?ROLLBACK; ???START?TRANSACTION; ???INSERT?INTO?b?select?1; ???INSERT?INTO?b?select?2; ???INSERT?INTO?b?select?1; ???INSERT?INTO?b?select?3; ???COMMIT;END;
測試表如下
CREATE?TABLE?`b`?(??`a`?int(11)?NOT?NULL?DEFAULT?'0', ??PRIMARY?KEY?(`a`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;
執(zhí)行上面的存儲過程，因此會在插入第二個記錄1時發(fā)生錯誤，但是因為啟用了自動回滾操作，這個存儲過程執(zhí)行結(jié)果如下：
10:09:46?test>?call?sp_auto_rollback_demo; Query?OK,?0?rows?affected?(0.01?sec)10:10:04?test>?select?*?from?b;Empty?set?(0.00?sec)
看起來沒有問題，運行比較正常，但是在執(zhí)行sp_auto_rollback_demo的時候是執(zhí)行成功了還是失敗了？對此，我們可以進行如下處理，示例如下：
DROP?PROCEDURE?IF?EXISTS?sp_auto_rollback_demo;CREATE?PROCEDURE?sp_auto_rollback_demo()BEGIN ???DECLARE?EXIT?HANDLER?FOR?SQLEXCEPTION?BEGIN?ROLLBACK;?SELECT?-1;?END; ???START?TRANSACTION; ???INSERT?INTO?b?select?1; ???INSERT?INTO?b?select?2; ???INSERT?INTO?b?select?1; ???INSERT?INTO?b?select?3; ???COMMIT; ???SELECT?1;END;
當發(fā)生錯誤時，先回滾然后返回-1,表示運行發(fā)生了錯誤。返回1表示運行正常。運行結(jié)果如下：
10:16:19?test>?call?sp_auto_rollback_demoG***************************?1.?row?***************************-1:?-1 1?row?in?set?(0.00?sec) Query?OK,?0?rows?affected?(0.01?sec) 10:16:35?test>?select?*?from?b; Empty?set?(0.00?sec)
以上就是MySQL Innodb事務編程問題和處理的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！
