史上最簡單的 MySQL 教程（三十九）「事務（下）」，

史上最簡單的 MySQL 教程（三十九）「事務（下）」，

溫馨提示：本系列博文已經同步到 GitHub，地址為「mysql-tutorial」，歡迎感興趣的童鞋Star、Fork，錯誤修正。

溫馨提示：在「事務（上）」中，我們已經瞭解了如何在 MySQL 中開啟事務，以及事務的一些基本操作。在本文中，我們將進一步學習事務的知識，包括事務原理、自動事務、復原點和事務特性等。

事務原理

事務原理：在事務開啟之後，所有的操作都會被臨時儲存到交易記錄，交易記錄只有在收到commit命令之後，才會將操作同步到資料表，其他任何情況都會清空交易記錄，例如突然中斷連線、收到rollback命令等。

接下來，我們簡單分析一下 MySQL 的操作過程：

• Step 1：用戶端與服務端建立串連，同時開啟一個臨時的交易記錄，此交易記錄只作用於目前使用者的當次串連；
• Step 2：在用戶端用 SQL 陳述式執行寫操作，用戶端收到 SQL 陳述式，執行，將結果直接寫入到資料表，並將資料表同步到資料庫；
• Step 3：我們在用戶端開啟事務，則服務端原來的操作機制被改變，後續所有操作都會被先寫入到臨時記錄檔；
• Step 4：在用戶端執行 SQL 陳述式（例如寫操作），服務端收到 SQL 陳述式，執行，將結果寫入到臨時記錄檔，並不將結果同步到資料庫；
• Step 5：在用戶端執行查詢操作，服務端直接從臨時記錄檔中撈取資料，返回給用戶端；
• Step 6：在用戶端執行commit或者rollback命令，清空臨時記錄檔，如果是commit命令，則將結果同步到資料庫；如果是rollback命令，則不同步。

通過上面的分析，我們就知道了為什麼在我們同時開啟兩個 MySQL 用戶端視窗（兩次串連）時，當一個視窗開啟事務並執行 SQL 操作之後，另一個視窗在查詢時並不會收到同步資料。原因就在於，當我們開啟事務之後，服務端會將後續的操作都寫入到臨時記錄檔，而另一個視窗在查詢的時候，則是直接從資料庫撈取資料，並會不走前一個的臨時記錄檔。

復原點

復原點：在某個操作成功完成之後，後續的操作有可能成功也有可能失敗，但無論後續操作的結果如何，前一次操作都已經成功了，因此我們可以在當前成功的位置，設定一個操作點，其可以供後續操作返回該位置，而不是返回所有操作，這個點稱之為復原點。關於復原點的基本文法為，

• 設定復原點：savepoint + 復原點名稱;
• 返回復原點：rollback to + 復原點名稱;

執行如下 SQL 陳述式，進行測試：

-- 測試復原點-- 查詢 bank_account 表資料select * from bank_account;-- 開啟事務start transaction;-- 事務操作 1：給 Charies 發工資 1000 元update bank_account set money = money + 10000 where id = 1;-- 設定復原點savepoint spone;-- 銀行扣稅：錯誤update bank_account set money = money - 10000 * 0.05 where id = 2;-- 查詢 bank_account 表資料select * from bank_account;

執行如下 SQL 陳述式，繼續進行測試：

-- 測試復原點-- 返回復原點rollback to spone;-- 銀行扣稅：正確update bank_account set money = money - 10000 * 0.05 where id = 1;-- 查詢 bank_account 表資料select * from bank_account;-- 提交事務commit;

如所示，顯然在執行返回復原點的操作之後，我們之前的錯誤操作得到了修正。

自動事務

在 MySQL 中，預設的都是自動交易處理，即使用者在操作完成之後，其操作結果會立即被同步到資料庫中。

自動事務是通過autocommit變數控制的，我們可以通過如下 SQL 陳述式，進行查看：

-- 查詢自動事務show variables like 'autocommit';

如所示，此為 MySQL 的預設設定。實際上，我們可以自己選擇是否開啟自動交易處理，其基本文法為，

• 開啟自動交易處理：set autocommit = on / 1;
• 關閉自動交易處理：set autocommit = off / 0;

在此，我們以關閉自動交易處理為例，進行示範：

-- 關閉自動交易處理set autocommit = 0;-- 查看自動交易處理show variables like 'autocommit';-- 查看 bank_account 表資料select * from bank_account;-- 修改 bank_account 表資料update bank_account set money = money + 1000 where id = 1;-- 查看 bank_account 表資料select * from bank_account;

如所示，我們並沒有開啟事務，僅是關閉了自動交易處理，但是我們發現，在我們修改了bank_account表中資料之後，其結果並不會立即同步到資料庫。實際上，這就是關閉了自動交易處理的正常現象。在我們執行commit命令之後，上述操作的結果即可進行同步：

-- 提交commit;-- 查看 bank_account 表資料select * from bank_account;

當然，如果我們不執行commit命令，而是執行rollback命令，那麼之前的所用操作都會復原到初始的狀態。在此，我們需要注意的是：通常情況下，我們是應該開啟自動交易處理的，否則的話，每次操作完成之後都需要我們手動提交，那豈不是要被累死了？

事務特性

事務的特性，可以簡單的概括為ACID，具體為：

• 原子性：Atomic，表示事務的整個操作是一個整體，是不可分割的，要麼全部成功，要麼全部失敗；
• 一致性：Consistency，表示事務操作的前後，資料表中的資料處於一致狀態；
• 隔離性：Isolation，表示不同的事務操作之間是相互隔離的，互不影響；
• 持久性：Durability，表示事務一旦提交，將不可修改，永久性的改變資料表中的資料。

對於上述事務的四個特性，其中原子性、一致性、持久性比較容易理解，但是隔離性卻需要格外注意。例如，開啟兩個用戶端視窗，分別執行如下 SQL 陳述式，進行測試：

-- 示範隔離性操作：視窗 1-- 開始事務start transaction;-- 修改 id 為 1 的資料update bank_account set money = money + 666 where id = 1;-- 查看 bank_account 表資料select * from bank_account;---------   萬人迷分割線   ----------- 示範隔離性操作：視窗 2-- 開始事務start transaction;-- 修改 id 為 2 的資料update bank_account set money = money + 666 where id = 2;-- 查看 bank_account 表資料select * from bank_account;

如所示，其完美的展示了事務隔離性的效果，即視窗 1 的中的事務操作，沒有影響到視窗 2 的事務操作；視窗 2 的中的事務操作，也沒有影響到視窗 1 的事務操作。But，在我們執行下面的 SQL 陳述式之後，我們將會看到不同的效果：

-- 示範隔離性操作：視窗 1-- 開始事務start transaction;-- 修改 name 為 Charies 的資料update bank_account set money = money + 666 where name = 'Charies';-- 查看 bank_account 表資料select * from bank_account;---------   萬人迷分割線   ----------- 示範隔離性操作：視窗 2-- 開始事務start transaction;-- 修改 name 為 Gavin 的資料update bank_account set money = money + 666 where name = 'Gavin';-- 查看 bank_account 表資料select * from bank_account;

如所示，視窗 1 的事務可以正常執行，但是視窗 2 的事務開啟成功，但是在修改資料的時候被“卡”住了，並且在持續一段時間之後，報出了一個 Lock wait timeout exceeded的錯誤：

那麼到底是什麼原因導致了上述錯誤的發生呢？這就是涉及到了資料庫的另外一個知識點 鎖機制 啦！

實際上，MySQL 使用的預設儲存引擎是 InnoDB，而 InnoDB 預設使用的鎖機制是 行鎖（鎖住操作的當前行），但是如果在事務操作的過程中，我們沒有使用索引欄位，那麼系統就會自動進行全表檢索，也就是其自動將行鎖定擴大為 表鎖（鎖住操作的當前表）。

現在回想一下，我們在第一次測試的時候，使用的條件id為主鍵索引，所以兩個事務可以表示出很好的隔離性，互不影響；在第二次測試的時候，我們將條件換為name，而name並不是索引欄位，因此在第二次測試的時候，視窗 1 的事務使用了表鎖，鎖住了整張表，而在事務提交或復原之前，其並不釋放鎖，所以所有試圖修改被鎖住表的資料的操作，都會陷入等待狀態。等待逾時，自然就報錯啦！

對於鎖機制，在「基礎教程」篇，我們並不做過多的介紹，在後續的「效能最佳化」篇中在詳細的進行討論。

溫馨提示：符號[]括起來的內容，表示可選項；符號+，則表示串連的意思。

———— ☆☆☆ —— 返回 -> 史上最簡單的 MySQL 教程 <- 目錄 —— ☆☆☆ ————

   

頂

11

踩

2

查看評論