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

通過(guò)以下命令可以查看當(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開啟。我們可以通過(guò)以下SQL語(yǔ)句改變這個(gè)模式

mysql> set autocommit = 0;

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

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

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

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

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

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

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

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

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

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

1、事務(wù)（transaction）

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

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

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

提交(commit)

回滾(rollback)

3、MYSQL支持的引擎(InnoDB)

show engines;

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

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

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

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

5、驗(yàn)證例子

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

接下來(lái)，我們?cè)囋囌Ｌ峤坏臄?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é)論：對(duì)比這次的測(cè)試結(jié)果，所以我認(rèn)為第一個(gè)測(cè)試，并沒有執(zhí)行rollback,而是跳出事務(wù)處理異常機(jī)制了。

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

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

可以設(shè)置set completion_type=1;(chain)，也就是說(shuō)無(wú)論幾個(gè)"張飛"在還沒提交之前，都是一個(gè)事務(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é)論：因?yàn)槲以O(shè)置了set completion_type=1(chain),相當(dāng)與在第一個(gè)"張飛"前加了begin,所以第二個(gè)"張飛"由于觸發(fā)了主鍵約束所以導(dǎo)致事務(wù)失敗，插入數(shù)據(jù)失敗。

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

最新評(píng)論