爽?好大?快?深点动态图,美丽的女秘书

主頁 > 知識庫 > MySQL 事務autocommit自動提交操作

MySQL 事務autocommit自動提交操作

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

通過以下命令可以查看當前autocommit模式

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

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

mysql> set autocommit = 0;

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

舉個例子，張三給李四轉賬500元。那么在數據庫中應該是以下操作：

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

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

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

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

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

當然，事務處理是為了保障表數據原子性、一致性、隔離性、持久性。

這些都是要消耗系統資源的，要謹慎選擇。

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

1、事務（transaction）

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

2、事務（Transcation）的用法

事務開始(start transaction or begin)

提交(commit)

回滾(rollback)

3、MYSQL支持的引擎(InnoDB)

show engines;

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

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

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)

也可以通過語句查詢當前配置

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

5、驗證例子

mysql> BEGIN;
 -> INSERT INTO test SELECT '關羽';
 -> 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;//

當前窗口結果：

新建窗口結果：

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

接下來，我們試試正常提交的數據。

mysql> BEGIN;
 -> INSERT INTO test SELECT '關羽';
 -> 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;//

當前窗口結果：

新建窗口結果：

結論：對比這次的測試結果，所以我認為第一個測試，并沒有執行rollback,而是跳出事務處理異常機制了。

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

mysql> BEGIN;
 -> INSERT INTO test SELECT '關羽';
 -> 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;//

當前窗口結果：

新建窗口結果：

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

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

代碼與上面的一樣。

mysql> SET @@completion_type = 1;
 -> BEGIN;
 -> INSERT INTO test SELECT '關羽';
 -> 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'

當前窗口結果：