MySQL 中的事務 · ZetCode 中文系列教程

# MySQL 中的事務 > 原文： [http://zetcode.com/databases/mysqltutorial/transactions/](http://zetcode.com/databases/mysqltutorial/transactions/) 在 MySQL 教程的這一部分中，我們將提到事務。 ## 事務的定義事務是針對一個或多個數據庫中數據的數據庫操作的基本單位。事務中所有 SQL 語句的影響可以全部提交給數據庫，也可以全部回滾。 MySQL 支持多種存儲引擎。 InnoDB 完全符合 ACID。 ACID 代表原子性，一致性，隔離性和耐久性。可靠的事務必須支持所有這四個屬性。事務內的操作必須是原子的。這意味著所有操作都將成功或失敗。這是全有或全無的規則。一致性屬性可確保事務完成后數據庫處于一致狀態。數據有效，沒有半完成的記錄。例如，沒有沒有付款記錄的客戶，或者沒有客戶的付款記錄。隔離是其他操作無法訪問在尚未完成的事務期間修改的數據的要求。在并發事務的情況下會出現隔離問題。如果沒有隔離，則數據可能最終處于不一致狀態。持久性是數據庫系統針對任何類型的系統故障恢復已提交事務更新的能力。 ## 隔離等級在高度并發的環境中，高度隔離的事務可能導致死鎖。僵局是一種情況，其中事務爭用資源并有效阻止彼此訪問資源。此外，在隔離級別和數據庫性能之間需要權衡。因此，數據庫系統為事務提供了幾個隔離級別。 MySQL 提供了四個級別的事務隔離： * 可序列化 * 可重復讀 * 已提交讀 * 未提交讀在可序列化的隔離級別中，所有事務都以完全隔離的方式發生。所有事務都一個接一個地執行。在可重復讀取的隔離級別中，語句無法讀取已被其他事務修改但尚未提交的數據。在當前事務完成之前，沒有其他事務可以修改當前事務已讀取的數據。這是 InnoDB 的默認隔離級別。其中，已提交讀隔離級別的語句無法讀取已被其他事務修改但未提交的數據。語句等待直到被其他事務寫鎖定的數據行被解鎖，然后才能獲取自己的鎖。這樣可以防止他們讀取臟數據。在未提交讀的隔離級別中，語句可以讀取已被其他事務修改但尚未提交的行。當事務未完全分離時，可能會遇到幻影讀取，不可重復讀取和臟讀取的問題。當事務重新執行返回返回滿足搜索條件的行的集合的查詢，并發現滿足條件的行的集合由于另一個最近提交的事務而發生更改時，會發生幻像讀取。不可重復讀取是在事務重新讀取其先前已讀取的數據并發現該數據已被另一個事務修改后發生的。自從初次閱讀以來就實現了。當事務從已被另一個事務修改但尚未提交的行中讀取數據時，會發生臟讀。下表顯示了所有隔離級別以及它們遇到的可能的問題。 | 隔離級別 | 幻讀 | 不可重復讀 | 臟讀 | | --- | --- | --- | --- | | 可序列化 | 不可能 | 不可能 | 不可能 | | 可重復讀 | 可能 | 不可能 | 不可能 | | 已提交讀 | 可能 | 可能 | 不可能 | | 未提交讀 | 可能 | 可能 | 可能 | MySQL 的默認事務隔離級別是可重復讀取。 ```sql mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ ``` 當前隔離級別存儲在`tx_isolation`服務器變量中。 ```sql mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; mysql> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE | +----------------+ ``` 我們可以使用`SET TRANSACTION ISOLATION LEVEL`語句更改隔離級別。 ## 自動提交 MySQL 還自動提交不屬于事務的語句。任何`UPDATE`或`INSERT`語句之前沒有`START`的結果將立即對所有連接可見。 ```sql mysql> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ ``` 默認情況下設置自動提交變量。 ```sql mysql> SET autocommit=0; mysql> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ ``` 自動提交可以關閉。現在，我們將演示`autocommint`變量。 ```sql mysql> SELECT @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ CREATE TABLE Test(Num INTEGER NOT NULL) engine=InnoDB; ``` 自動提交已設置。我們使用支持事務的 InnoDB 存儲引擎創建一個簡單的`Test`表。 ```sql mysql> INSERT INTO Test VALUES (1), (2), (3); mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | +-----+ ``` 我們將三行插入到表的列中。這些值將立即提交。 ```sql mysql> SET autocommit=0; mysql> INSERT INTO Test VALUES (4), (5); mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +-----+ ``` 現在，我們將`autocommit`變量設置為`false`。我們插入兩個值，然后從表中選擇所有數據。現在表中有 5 行。 ```sql mysql> ROLLBACK; mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | +-----+ ``` 但是，數據不會永久寫入表中。使用`ROLLBACK`語句，我們將其收回。 ```sql mysql> INSERT INTO Test VALUES (4), (5); mysql> COMMIT; mysql> ROLLBACK; mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | +-----+ ``` 現在我們再次插入值 4、5。這次，使用`COMMIT`語句提交行。后續回滾語句無效。 ## 啟用事務啟用自動提交功能后，每個單個 SQL 語句都會自動包裝在其自己的事務中。要開始我們自己的事務，我們發出`START TRANSACTION`語句。稍后使用`COMMIT`或`ROLLBACK`語句完成事務。事務主體中可能會發布多個語句。全部作為一個單元提交或回滾。 ```sql mysql> TRUNCATE Test; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM Test; Empty set (0.00 sec) ``` 我們將使用相同的`Test`表。我們截斷表中的數據。 ```sql mysql> START TRANSACTION; mysql> INSERT INTO Test VALUES (1), (2); mysql> INSERT INTO Test VALUES (3), (4); mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+ ``` 在上面的代碼中，我們開始一個事務并將四行插入到表中。這些值尚未提交。從當前連接中，行可見。 ```sql $ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 65 Server version: 5.1.41-3ubuntu12.9 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM mydb.Test; Empty set (0.00 sec) ``` 但是，從另一個連接，測試表為空。我們啟動`mysql`客戶端程序的新實例。這是與 MySQL 數據庫的不同連接。從此連接，這些值尚不可見。 ```sql mysql> COMMIT; ``` 最后，`COMMIT`語句將數據提交到表中。這些行在兩個連接中均可見。我們開始另一筆事務。這次將回滾數據。 ```sql mysql> START TRANSACTION; mysql> INSERT INTO Test VALUES (5), (6); mysql> INSERT INTO Test VALUES (7), (8); mysql> ROLLBACK; mysql> SELECT * FROM Test; +-----+ | Num | +-----+ | 1 | | 2 | | 3 | | 4 | +-----+ ``` 在上面的 SQL 代碼中，我們開始一個新事務。我們在測試表中插入四個值。我們使用`ROLLBACK`語句回滾更改。從表中進行的后續選擇顯示數據未提交到表。在 MySQL 教程的這一部分中，我們處理了事務。