MySQL 事務(wù)autocommit自動提交操作

MySQL默認(rèn)操作模式就是autocommit自動提交模式。這就表示除非顯式地開始一個事務(wù)，否則每個查詢都被當(dāng)做一個單獨的事務(wù)自動執(zhí)行。我們可以通過設(shè)置autocommit的值改變是否是自動提交autocommit模式。

通過以下命令可以查看當(dāng)前autocommit模式

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.04 sec)

從查詢結(jié)果中，我們發(fā)現(xiàn)Value的值是ON，表示autocommit開啟。我們可以通過以下SQL語句改變這個模式

mysql> set autocommit = 0;

值0和OFF都是一樣的，當(dāng)然，1也就表示ON。通過以上設(shè)置autocommit=0，則用戶將一直處于某個事務(wù)中，直到執(zhí)行一條commit提交或rollback語句才會結(jié)束當(dāng)前事務(wù)重新開始一個新的事務(wù)。

舉個例子，張三給李四轉(zhuǎn)賬500元。那么在數(shù)據(jù)庫中應(yīng)該是以下操作：

1，先查詢張三的賬戶余額是否足夠

2，張三的賬戶上減去500元

3，李四的賬戶上加上500元

以上三個步驟就可以放在一個事務(wù)中執(zhí)行提交，要么全部執(zhí)行要么全部不執(zhí)行，如果一切都OK就commit提交永久性更改數(shù)據(jù)；如果出錯則rollback回滾到更改前的狀態(tài)。利用事務(wù)處理就不會出現(xiàn)張三的錢少了李四的賬戶卻沒有增加500元或者張三的錢沒有減去李四的賬戶卻加了500元。

MySQL默認(rèn)的存儲引擎是MyISAM，MyISAM存儲引擎不支持事務(wù)處理，所以改變autocommit沒有什么作用。但不會報錯，所以要使用事務(wù)處理的童鞋一定要確定你所操作的表示支持事務(wù)處理的，如InnoDB。如果不知道表的存儲引擎可以通過查看建表語句查看建表的時候有沒有指定事務(wù)類型的存儲引擎，如果沒有指定存儲引擎默認(rèn)則是MyISAM不支持事務(wù)的存儲引擎。

當(dāng)然，事務(wù)處理是為了保障表數(shù)據(jù)原子性、一致性、隔離性、持久性。

這些都是要消耗系統(tǒng)資源的，要謹(jǐn)慎選擇。

補(bǔ)充：MySQL的事務(wù)處理(Transation)和自動執(zhí)行(AutoCommit)與提交類型(Completion)

1、事務(wù)（transaction）

事務(wù)在計算機(jī)術(shù)語中是指訪問并可能更新數(shù)據(jù)庫中各種數(shù)據(jù)項的一個程序執(zhí)行單元(unit)。事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。如果你要刪除一條主表信息，而主表有多個從表的時候，你需要有步驟地刪除明細(xì)后再刪除主表信息，這個過程極其容易出錯，那么這個時候就是用事務(wù)才處理是最合適的了。

2、事務(wù)（Transcation）的用法

事務(wù)開始(start transaction or begin)

提交(commit)

回滾(rollback)

3、MYSQL支持的引擎(InnoDB)

show engines;

4、自動執(zhí)行(AutoCommit)與提交類型(Completion)

使用事務(wù)有兩種方式，分別為隱式事務(wù)和顯式事務(wù)。隱式事務(wù)實際上就是自動提交，Oracle 默認(rèn)不自動提交，需要手寫 COMMIT。在MySQL中，自動提交（autocommit）在支持事務(wù)（transaction）的引擎中，若autocommit=true,則不需要commit的情況下直接提交語句形成永久性修改，Mysql默認(rèn)打開autocommit,也可以通過配置設(shè)置。

set autocommit=0;(AutoCommit Off)
set autocommit=1;(AutoCommit On)
set completion_type=0;(No Chain)
set completion_type=1;(Chain)
set completion_type=2;(Release)

也可以通過語句查詢當(dāng)前配置

show variables like '%autocommit%';
show variables like '%completion%';

5、驗證例子

mysql> BEGIN;
 -> INSERT INTO test SELECT '關(guān)羽';
 -> COMMIT;
 -> BEGIN;
 -> INSERT INTO test SELECT '張飛';
 -> INSERT INTO test SELECT '張飛';
 -> ROLLBACK;
 -> SELECT * FROM test;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1062 (23000): Duplicate entry '張飛' for key 'PRIMARY'

mysql> select * from test;//

當(dāng)前窗口結(jié)果：

新建窗口結(jié)果：

結(jié)論：由于test表中name的唯一性約束,看代碼的話，兩個"張飛"執(zhí)行后（無論有無觸發(fā)主鍵約束），實際由于第二個"張飛"觸發(fā)了唯一性約束異常，所以我認(rèn)為事務(wù)會跳出,所以在當(dāng)前連接中，test表中可以看到一個張飛,實際上第二個事務(wù)，并沒有提交成功。

接下來，我們試試正常提交的數(shù)據(jù)。

mysql> BEGIN;
 -> INSERT INTO test SELECT '關(guān)羽';
 -> COMMIT;
 -> BEGIN;
 -> INSERT INTO test SELECT '張飛';
 -> INSERT INTO test SELECT '劉備';
 -> ROLLBACK;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.04 sec)


mysql> select * from test;//

當(dāng)前窗口結(jié)果：

新建窗口結(jié)果：

結(jié)論：對比這次的測試結(jié)果，所以我認(rèn)為第一個測試，并沒有執(zhí)行rollback,而是跳出事務(wù)處理異常機(jī)制了。

因為mysql默認(rèn)打開了autocommit,那么我想驗證下在無顯示事務(wù)的情況下(即無begin開頭)，兩個"張飛"是如何自處的？

mysql> BEGIN;
 -> INSERT INTO test SELECT '關(guān)羽';
 -> COMMIT;
 -> INSERT INTO test SELECT '張飛';
 -> INSERT INTO test SELECT '張飛';
 -> ROLLBACK;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

ERROR 1062 (23000): Duplicate entry '張飛' for key 'PRIMARY'
mysql> select *from test;//

當(dāng)前窗口結(jié)果：

新建窗口結(jié)果：

結(jié)論：其實看執(zhí)行結(jié)果就知道了，第1個"張飛"作為單獨的事務(wù)已經(jīng)執(zhí)行成功了，第二個"張飛"由于觸發(fā)了主鍵約束所以執(zhí)行失敗了。除了顯示事務(wù)外，有沒有辦法在沒結(jié)束事務(wù)前(無Commit)前，可以讓兩個"張飛"合并成一個事務(wù)呢？

可以設(shè)置set completion_type=1;(chain)，也就是說無論幾個"張飛"在還沒提交之前，都是一個事務(wù)。

代碼與上面的一樣。

mysql> SET @@completion_type = 1;
 -> BEGIN;
 -> INSERT INTO test SELECT '關(guān)羽';
 -> COMMIT;
 -> INSERT INTO test SELECT '張飛';
 -> INSERT INTO test SELECT '張飛';
 -> ROLLBACK;
 -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1062 (23000): Duplicate entry '張飛' for key 'PRIMARY'

當(dāng)前窗口結(jié)果：

新建窗口結(jié)果：

結(jié)論：因為我設(shè)置了set completion_type=1(chain),相當(dāng)與在第一個"張飛"前加了begin,所以第二個"張飛"由于觸發(fā)了主鍵約束所以導(dǎo)致事務(wù)失敗，插入數(shù)據(jù)失敗。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。