MySQL的事務與事務隔離

標籤：mysql   innodb   transactions   事務隔離   

   MySQL中自從引入InnoDB引擎後，在MySQL中就支援事務，事務就是一組原子性的查詢語句，也即將多個查詢當作一個獨立的工作單元，平時通過提交工作單元來完成在事務中的相應的查詢或修改，在能支援事務的資料庫中必須要滿足ACID測試，即事務的四個特性：

    A：Atomicity，原子性（都執行或者都不執行）

    C：Consistency，一致性（從一個一致性狀態轉到另外一個一致性狀態）

    I：Isolaction，隔離性（一個事務的所有修改操作在提交前對其他事務時不可見的）

    D: Durability，持久性（旦事務得到提交，其所做的修改會永久有效）

而在早期預設的引擎MyISAM是不支援事務的，所以如果是在MyISAM表中是不支援事物的，想要知道資料中具體支援哪些表引擎可以通過”SHOW ENGINES;”查看在所使用版本中MySQL所支援的所有引擎。在這裡先建立一張表transaction_tbl用於測試：

CREATE TABLE transaction_tbl (id int(4)) ENGINE=InnoDB; INSERT INTO transaction_tbl VALUE (1);INSERT INTO transaction_tbl VALUE (2);INSERT INTO transaction_tbl VALUE (3);INSERT INTO transaction_tbl VALUE (4);INSERT INTO transaction_tbl VALUE (5);

在正常的使用過程中需要關閉自動認可的功能預設系統下是開啟的

mysql> SHOW GLOBAL VARIABLES LIKE ‘autocommit‘;                       +---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+1 row in set (0.00 sec)

在使用事務之前需要關閉，在平時使用事務之前需要先檢查是否有開啟autocommit，當然如果對事務的依賴比較大建議可以永久關閉全域的自動認可，但是在平時使用的過程中只要在使用時關閉自動認可，而使用手動啟動事務，這樣在事務中需要復原時才能根據相關的交易隔離等級得到想要的效果，可以在使用事務時關閉autocommit，在所有的事務結束後再開啟autocommit

mysql> SET GLOBAL AUTOCOMMIT=off;#當然這裡也可以使用布爾值的0和1Query OK, 0 rows affected (0.00 sec)

其中事務的控制語句也很簡單，如下：

BEGIN; 或 START TRANSACTION;顯式地開啟一個事務COMMIT;提交事務即結束事務，並使已對資料庫進行的所有修改為持久性的ROLLBACK;交易回復，會結束使用者的事務並撤銷進行中的所有未提交的修改SAVEPOINT identifier;允許在事務中建立一個儲存點，一個事務中可以有多個SAVEPOINTRELEASE SAVEPOINT identifier;刪除一個事務的儲存點，當沒有指定的儲存點時，執行該SQL語句會報異常ROLLBACK TO identifier;把交易回復到標記點

而在事務中的隔離等級不同，則事物的安全性就不同，但是事物得安全性越高，並發性越低，當然需要根據實際情況選擇，在MySQL中事務的隔離等級有四種，安全級分別由低至高：

    READ UNCOMMITTEND：讀未提交

    READ COMMITTEND：讀提交

    REPEATABLE READ ：可重讀

    SERIALIZABLE：可序列化

查看當前使用的預設的交易隔離等級：

mysql> SHOW GLOBAL VARIABLES LIKE ‘tx_isolation‘;+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.00 sec)

而在使用四個隔離等級中，所帶來效果都是不相同的，此時測試需要開啟2個session更為直觀，在這裡就用A、B來代表兩個session中開啟事務A、B：

mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.01 sec)

    一、READ UNCOMMITTEND：讀未提交，顧名思義即所有的事務都可以讀取到其他事務中未提交的內容，該隔離模式在平時一般都不使用，因為使用READ UNCOMMITTEND會帶來髒讀問題，下面就用transaction_tbl舉一個簡單例子說明下：

mysql> SET GLOBAL tx_isolation=‘READ-UNCOMMITTED‘;Query OK, 0 rows affected (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE ‘tx_isolation‘;+---------------+------------------+| Variable_name | Value            |+---------------+------------------+| tx_isolation  | READ-UNCOMMITTED |+---------------+------------------+1 row in set (0.00 sec)

事務A中做了操作，但不提交：

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> UPDATE transaction_tbl SET id = ‘6‘ WHERE id=‘1‘;Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)

但是此時事務B是可以看見事務A中資料

事務B:

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.01 sec)

此時事務A復原：

mysql> ROLLBACK;Query OK, 0 rows affected (0.01 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)

事務B中查出來的是事務A中未提交的資料：

mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)

這樣就是由READ UNCOMMITTEND所帶來的髒讀，一般資料庫生產環境中都不用這種交易隔離等級。

    二、READ COMMITTEND，讀提交，同理根據名字可知事物的隔離等級會比讀未提交高一個交易隔離等級更高，從而解決了髒讀的問題，這也是大多數資料庫中所用的預設交易隔離等級，但並不是MySQL的預設交易隔離等級，該交易隔離等級雖然解決了髒讀問題，但是帶來新的問題是不可重讀,如果此時恰好有2個事務對相同的一張表做操作時，在一個事務中執行相同的查詢時會查出不同的結果：

mysql> SET GLOBAL tx_isolation=‘READ-COMMITTED‘;Query OK, 0 rows affected (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE ‘tx_isolation‘;       +---------------+----------------+| Variable_name | Value          |+---------------+----------------+| tx_isolation  | READ-COMMITTED |+---------------+----------------+1 row in set (0.00 sec)

在事務A中：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)

此時事務B中也開啟事務做了一個操作：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> UPDATE transaction_tbl SET id = ‘6‘ WHERE id=‘1‘;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.01 sec)

此時看下事務Ａ中的查詢：

mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)#但此時事務B中COMMIT提交後，事務A中mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)

這樣就是由READ COMMITTEND所帶來的不可重讀問題，所以在一般資料庫生產環境中也不建議採用這種交易隔離等級。

    三、REPEATABLE READ，可重讀，同理該交易隔離等級解決了不可重讀的問題，在REPEATABLE READ中使用MVCC（多版本並發控制）在每個事務啟動時，InnoDB會為每個啟動的事務提供一個當下時刻的快照，為實現此功能，InnoDB會為每個表提供兩隱藏的欄位，一個用於儲存行的建立時間，一個用於儲存行的失效時間，裡面儲存的系統版本號碼，MVCC旨在READ COMMITTEND和REPEATABLE READ兩個事務隔離中生效，但是在REPEATABLE READ同以上交易隔離等級一樣，在解決了不可重讀的問題同時也帶來新的問題幻讀，此時恰好有2個事務對相同的一張表做操作時，在一個事務中提交之前其中一個事務在另一事務提交前後查詢的結果不一樣：

mysql> SET GLOBAL tx_isolation=‘REPEATABLE-READ‘; Query OK, 0 rows affected (0.01 sec)mysql> SHOW GLOBAL VARIABLES LIKE ‘tx_isolation‘;+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.01 sec)

事務A：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.02 sec)

事務B中做相關操作並提交：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)mysql> UPDATE transaction_tbl SET id = ‘1‘ WHERE id=‘6‘;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.01 sec)mysql> COMMIT;Query OK, 0 rows affected (0.02 sec)

此時再來看下事務A中：

mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.02 sec)mysql> COMMIT;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  6 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)

這樣就是REPEATABLE READ帶來的幻讀問題，當然在實際生產中這麼恰好的事比較少，所以一般都做為MySQL的預設交易隔離等級。

    四、SERIALIZABLE，可序列化，強事務排序也是最進階別的事務隔離，所有的事務都有使用共用鎖定，這樣就解決相應的幻讀問題，但是因為共用鎖定的原因從而使寫入的效能降低，從而降低了MySQL的效能:

mysql> SET GLOBAL tx_isolation=‘SERIALIZABLE‘;Query OK, 0 rows affected (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE ‘tx_isolation‘;+---------------+--------------+| Variable_name | Value        |+---------------+--------------+| tx_isolation  | SERIALIZABLE |+---------------+--------------+1 row in set (0.00 sec)

在事務A中插入一條資料不提交：

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO transaction_tbl VALUE (‘7‘);Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 ||  7 |+----+6 rows in set (0.00 sec)

此時在事務B中，在事務A未提交前是無法寫入提交的

mysql> SHOW GLOBAL VARIABLES LIKE ‘tx_isolation‘;+---------------+--------------+| Variable_name | Value        |+---------------+--------------+| tx_isolation  | SERIALIZABLE |+---------------+--------------+1 row in set (0.00 sec)mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM transaction_tbl;+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 |+----+5 rows in set (0.00 sec)mysql> UPDATE transaction_tbl SET id = ‘6‘ WHERE id=‘1‘;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

只有在事務A中COMMIT提交後才能在事務B中提交，但是在此需要注意的是在MySQL 5.7開始經過大量的代碼重構最佳化後事務A、B在使用前三種隔離等級都是預設為REPEATABLE READ的交易隔離等級，而在平時利用事務時多用於預存程序中大量使用，而不同資料庫（包括MySQL的不同版本都存在差異），文法差別很大，移植困難，換了資料庫，需要重新編寫，所以把過多商務邏輯寫在預存程序不好維護，不利於分層管理，容易混亂，一般預存程序適用於個別對效能要求較高的業務，其它的必要性不是很大，在平時使用需要根據實際情況而定。

本文出自 “Jim的技術隨筆” 部落格，謝絕轉載！

MySQL的事務與事務隔離