詳解MySQL執行事務的文法和流程

摘要：MySQL 提供了多種存儲引擎來支援事務。

MySQL 提供了多種存儲引擎來支援事務。支援事務的存儲引擎有 InnoDB 和 BDB，其中，InnoDB 存儲引擎事務主要通過 UNDO 日志和 REDO 日志實作，MyISAM 存儲引擎不支援事務。

拓展：任何一種資料庫，都會擁有各種各樣的日志，用來記錄資料庫的運作情況、日常操作、錯誤資訊等，MySQL 也不例外。例如，當使用者 root 登入到 MySQL 伺服器，就會在日志檔案裡記錄該使用者的登入時間、執行操作等。

為了維護 MySQL 伺服器，經常需要在 MySQL 資料庫中進行日志操作：

• UNDO 日志：複制事務執行前的資料，用于在事務發生異常時復原資料。
• REDO 日志：記錄在事務執行中，每條對資料進行更新的操作，當事務送出時，該内容将被重新整理到磁盤。

預設設定下，每條 SQL 語句就是一個事務，即執行 SQL 語句後自動送出。為了達到将幾個操作做為一個整體的目的，需要使用 BEGIN 或 START TRANSACTION 開啟一個事務，或者禁止目前會話的自動送出。

執行事務的文法和流程

SQL 使用下列語句來管理事務。

1) 開始事務

BEGIN;

或

START TRANSACTION;

這個語句顯式地标記一個事務的起始點。

2) 送出事務

MySQL 使用下面的語句來送出事務：

COMMIT;

COMMIT 表示送出事務，即送出事務的所有操作，具體地說，就是将事務中所有對資料庫的更新都寫到磁盤上的實體資料庫中，事務正常結束。

送出事務，意味着将事務開始以來所執行的所有資料都修改成為資料庫的永久部分，是以也标志着一個事務的結束。一旦執行了該指令，将不能復原事務。隻有在所有修改都準備好送出給資料庫時，才執行這一操作。

3) 復原（撤銷）事務

MySQL 使用以下語句復原事務：

ROLLBACK;

ROLLBACK 表示撤銷事務，即在事務運作的過程中發生了某種故障，事務不能繼續執行，系統将事務中對資料庫的所有已完成的操作全部撤銷，復原到事務開始時的狀态。這裡的操作指對資料庫的更新操作。

當事務執行過程中遇到錯誤時，使用 ROLLBACK 語句使事務復原到起點或指定的保持點處。同時，系統将清除自事務起點或到某個儲存點所做的所有的資料修改，并且釋放由事務控制的資源。是以，這條語句也标志着事務的結束。

總結

BEGIN 或 START TRANSACTION 語句後面的 SQL 語句對資料庫資料的更新操作都将記錄在事務日志中，直至遇到 ROLLBACK 語句或 COMMIT 語句。如果事務中某一操作失敗且執行了 ROLLBACK 語句，那麼在開啟事務語句之後所有更新的資料都能復原到事務開始前的狀态。如果事務中的所有操作都全部正确完成，并且使用了 COMMIT 語句向資料庫送出更新資料，則此時的資料又處在新的一緻狀态。

執行個體示範

下面通過兩個例子來示範一下 MySQL 事務的具體用法。

例1

下面模拟在張三的賬戶減少 500 元後，李四的賬戶還未增加 500 時，有其他會話通路資料表的場景。由于代碼需要在兩個視窗中執行，為了友善閱讀，這裡我們稱為 A 視窗和 B 視窗。

1) 在 A 視窗中開啟一個事務，并更新 mybank 資料庫中 bank 表的資料，SQL 語句和運作結果如下：

mysql> USE mybank;
Database changed
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE bank SET currentMoney = currentMoney-500
    -> WHERE customerName='張三';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0      

2) 在 B 視窗中查詢 bank 資料表中的資料，SQL 語句和運作結果如下：

mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 張三         |      1000.00 |
| 李四         |         1.00 |
+--------------+--------------+
2 rows in set (0.00 sec)      

從結果可以看出，雖然 A 視窗中的事務已經更改了 bank 表中的資料，但沒有立即更新資料，這時其他會話讀取到的仍然是更新前的資料。

3) 在 A 視窗中繼續執行事務并送出事務，SQL 語句和運作結果如下：

mysql> UPDATE bank SET currentMoney = currentMoney+500
    -> WHERE customerName='李四';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)      

4) 在 B 視窗中再次查詢 bank 資料表的資料，SQL 語句和運作結果如下：

mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 張三         |       500.00 |
| 李四         |       501.00 |
+--------------+--------------+
2 rows in set (0.00 sec)      

在 A 視窗中執行 COMMIT 送出事務後，對資料所做的更新将一起送出，其他會話讀取到的是更新後的資料。從結果可以看出張三和李四的總賬戶餘額和轉賬前保持一緻，這樣資料從一個一緻性狀态更新到另一個一緻性狀态。

前面提到，當事務在執行中出現問題，也就是不能按正常的流程執行一個完整的事務時，可以使用 ROLLBACK 語句進行復原，使用資料恢複到初始狀态。

在例 1 中，張三的賬戶餘額已經減少到 500 元，如果再轉出 1000 元，将會出現餘額為負數，是以需要復原到原始狀态。如例 2 所示。

例2

将張三的賬戶餘額減少 1000 元，并讓事務復原，SQL 語句和運作結果如下所示：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql> UPDATE bank SET currentMoney = currentMoney-1000 WHERE customerName='張三';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)
 
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 張三         |       500.00 |
| 李四         |       501.00 |
+--------------+--------------+
2 rows in set (0.00 sec)      

從結果可以看出，執行事務復原後，賬戶資料恢複到初始狀态，即該事務執行之前的狀态。

拓展

在資料庫操作中，為了有效保證并發讀取資料的正确性，提出了事務的隔離級别。在例 1 和例 2 的示範中，事務的隔離級别為預設隔離級别。在 MySQL 中，事務的預設隔離級别是 REPEATABLE-READ （可重讀）隔離級别，即事務未結束時（未執行 COMMIT 或 ROLLBACK），其它會話隻能讀取到未送出資料。

注意事項

MySQL 事務是一項非常消耗資源的功能，大家在使用過程中要注意以下幾點。

1) 事務盡可能簡短

事務的開啟到結束會在資料庫管理系統中保留大量資源，以保證事務的原子性、一緻性、隔離性和持久性。如果在多使用者系統中，較大的事務将會占用系統的大量資源，使得系統不堪重負，會影響軟體的運作性能，甚至導緻系統崩潰。

2) 事務中通路的資料量盡量最少

當并發執行事務處理時，事務操作的資料量越少，事務之間對相同資料的操作就越少。

3) 查詢資料時盡量不要使用事務

對資料進行浏覽查詢操作并不會更新資料庫的資料，是以應盡量不使用事務查詢資料，避免占用過量的系統資源。

4) 在事務處理過程中盡量不要出現等待使用者輸入的操作

在處理事務的過程中，如果需要等待使用者輸入資料，那麼事務會長時間地占用資源，有可能造成系統阻塞。

本文分享自華為雲社群《MySQL執行事務的文法和流程》，原文作者：運氣男孩。

點選關注，第一時間了解華為雲新鮮技術~