事務隔離級别-Transaction Isolation Levels

本文是對MySQL官方文檔：《 14.5.2.1 Transaction Isolation Levels》的翻譯，其中備注、一~五标題和其它小标題是部落客根據了解所加。

一、事務的隔離性

事務隔離性（ Isolation）是資料庫處理資料的基礎性能之一。隔離性（ Isolation）與原子性（ Atomicity）、一緻性（Consistency）、持久性（Durability）合稱為資料庫事務的四大特性，簡稱為ACID。隔離級别則在事務中起到一個微調設定的作用，它用于在資料的性能與可靠性、一緻性和再現性之間做出平衡，而這些資料可能是多個事務同時修改、查詢的結果。

二、InnoDB for Isolation

MySQL資料庫支援的存儲引擎有很多，其中使用場景中比較常見的InnoDB和MyISAM，但是後者不支援事務。InnoDB存儲引擎支援《SQL:1992标準》所描述的所有四種事務隔離級别：讀未送出（ READ UNCOMMITTED ），讀已送出（ READ COMMITTED ），可重複讀（ REPEATABLE READ ）和序列化（ SERIALIZABLE ），其中可重複讀（ REPEATABLE READ ）是InnoDB的預設隔離級别。

三、隔離級别修改

使用者可以使用sql語句： SET TRANSACTION 來改變隔離級别，不僅可以為單獨一次會話改變，而且可以全局的改變。如果想為所有資料庫連接配接設定預設的隔離級别，可以通過使用參數 transaction-isolation 在命 令行或配置檔案中修改。關于更詳細的設定、操作隔離級别文法參考 Section 13.3.6, “SET TRANSACTION Syntax” 。 【備注】

1、設定隔離級别：

SET SESSION TRANSACTION ISOLATION LEVEL isolation_level_name;
    SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level_name;
           

其中isolation_level_name包括： READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE ； 或者使用以下指令：

SET GLOBAL tx_isolation='isolation_level_name';       
SET SESSION tx_isolation='isolation_level_name';
           

其中 isolation_level_name包括： READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ和 SERIALIZABLE；

2、設定預設隔離級别：

①cd到mysql安裝目錄，例如我的是 /usr/local/opt/[email protected]； ②編輯 my.cnf 檔案，在 [mysqld] 增加修改參數： transaction-isolation=isolation_level_name 其中 isolation_level_name 包括： READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ 和 SERIALIZABLE ；

3、查詢隔離級别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
           

四、隔離級别使用簡要

InnoDB存儲引擎是通過使用不同的鎖（locking）政策，來做到對這裡描述的每一個隔離級别都支援的。 1、在視ACID合規性非常重要的核心資料進行操作的時候，您可以通過使用預設的級别： REPEATABLE READ來強制執行高度一緻性； 2、在有些情況，比如批量報告（ bulk reporting），資料的高度一緻性和可重複讀與減少大量的鎖開銷相比沒有那麼重要時，您可以使用 READ COMMITTED甚至用 READ UNCOMMITTED來放寬資料的一緻性規則； 3、 序列化（ SERIALIZABLE ）隔離級别比可重複讀（ REPEATABLE READ）使用更嚴格的規則；在一些比較特殊的場景才會使用該隔離級别，比如說XA事務（ XA transactions）或者需要解決一些并發、死鎖（ deadlock）問題的場景。 【備注】 XA官方文檔是這樣寫的： A standard interface for coordinating distributed transactions, allowing multiple databases to participate in a transaction while maintaining ACID compliance .  意思是：它是一個協調分布式事務的MySQL标準接口，這個可以接口在保證ACID合規性的前提下，允許叢集資料庫（ multiple databases）參與到同一個事務當中去。

五、事務隔離級别

接下來的内容描述了MySQL是如何支援不同僚務隔離級别的。列舉了四種隔離級别，不僅有很常用的，也有平時還少使用的。

1、REPEATABLE READ

①這是InnoDB的預設隔離級别。一緻性讀（ consistent read）在同一個事務中讀取都是第一次讀取時建立的快照（ snapshot）。這就意味着，在同一個事務中假如您執行了多個非鎖定的查詢語句，查詢到的結果都是彼此一緻的。關于非鎖定的一緻性讀（ consistent read）可以參考 Section 14.5.2.3, “Consistent Nonlocking Reads” 。 ②對于加鎖讀（ locking read ）操作（ SELECT  with  FOR UPDATE  or  LOCK IN SHARE MODE），更新操作（UPDATE）和删除操作（DELETE）這幾種操作，資料庫選擇對資料的加鎖政策取決于，以上執行的資料表是否有唯一索引（ unique index ），而且查詢條件中是否涉及到索引字段或範圍查詢。 -對于查詢條件中有唯一索引的，InnoDB值鎖定查詢到的資料，而不鎖定資料前面的間隙（gap）。 -對于其他的查詢情況，InnoDB會鎖定索引掃描的範圍，InnoDB通過使用間隙鎖（gap lock）或者next-key lock來阻塞其他會話向掃描範圍内的gap插入資料。對于gap lock和next-key lock的詳細介紹參考 Section 14.5.1, “InnoDB Locking” 。

2、READ COMMITTED

每次一緻性讀（ consistent read），即使在同一事務中，它都會設定并讀取自己的最新的快照（ snapshot）。關于一緻性讀可以參考 Section 14.5.2.3, “Consistent Nonlocking Reads” 。（ 【備注】 其實這個連結和五、1、①中的是相同的。） 對于加鎖讀（ locking read）操作 （ SELECT  with  FOR UPDATE  or  LOCK IN SHARE MODE），更新操作 （UPDATE）和删除操作 （DELETE）這幾種操作， InnoDB僅僅對涉及索引的記錄加鎖，而不鎖定它們前面的間隙 （gap）， next-key lock允許在鎖定記錄旁邊自由插入新記錄。在這種隔離級别，間隙鎖定（ gap lock）僅用于外鍵限制校驗和重複鍵校驗 。 因為禁用了間隙鎖定，是以可能會出現幻象（ phantom ）問題，因為其他會話可以将新行插入到間隙中（【備注】比如 phantom read：幻讀 ）。對于幻象（ phantom ）更詳細介紹可參考 Section 14.5.4, “Phantom Rows” 。 假如您使用READ COMMITTED隔離級别，則必須使用基于行的二進制日志記錄。 使用READ COMMITTED還有一些額外的效果： -對于 UPDATE 和 DELETE 操作，InnoDB隻會為它所操作的行持有鎖，而其他不比對的鎖定行會在MySQL通過 where 條件評估後被釋放掉。這一做法極大地減少了死鎖發生的可能性，但是依然會發生。 -對于 UPDATE 操作，假如一條資料行已經被加鎖，InnoDB會執行一個“半一緻性”（“semi-consistent”）讀取，給MySQL傳回一個最新送出（commit）的版本号，以至于MySQL可以判斷是否這行資料與要 UPDATE 的行所比對。假如比對（則必定會被 UPDATE ），MySQL會再次讀取它，而且這次InnoDB會要麼為它加鎖要麼等待一個鎖給它。 舉個例子，使用下面的sql語句：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
           

在這種情況下，該表沒有索引，是以搜尋和索引掃描使用的是隐式聚簇索引進行記錄鎖定 (參考  Section 14.8.2.1, “Clustered and Secondary Indexes” )而不是索引列。 設想一下，一個會話對上表執行了如下 UPDATE 語句：

# Session A
START TRANSACTION;UPDATE t SET b = 5 WHERE b = 3;
           

然後，另一個會話在Session A之後執行了如下 UPDATE 語句：

# Session B
UPDATE t SET b = 4 WHERE b = 2;
           

當InnoDB執行每一次 UPDATE 時，首先它會為讀取的每一行資料請求一個排它鎖（ exclusive lock ），然後決定是否去修改它。假如InnoDB沒有修改這行資料，會釋放這個鎖；否則，InnoDB會保留這個鎖直到事務結束。這會影響到事務的處理，如下所示： 當使用預設的隔離級别可重複讀（ REPEATABLE READ ）時， -在①中的操作，它為讀取的每一行資料請求了一個x-lock（排它鎖exclusive lock的簡稱），而且一個都沒有釋放：

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
           

-當②中的 UPDATE 語句執行時，一為讀取的資料請求鎖就被阻塞了（因為①中的 UPDATE為所有資料保留了鎖 ），而且無法繼續執行直到①中的 UPDATE 語句送出（commit）或復原（rollback）：

x-lock(1,2); block and wait for first UPDATE to commit or roll back
           

當使用隔離級别的為讀已送出（ READ COMMITTED ）時， 在①中的操作，它為讀取的每一行資料請求了一個x-lock（排它鎖exclusive lock的簡稱），而且為它不修改的資料釋放了鎖：

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
           

-當②中的 UPDATE 語句執行時，InnoDB執行了一個“半一緻性”（“semi-consistent”）讀取，給MySQL傳回一個最新送出（commit）的版本号，以至于MySQL可以判斷是否每行資料與要 UPDATE 的行所比對：

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock
           

舉另一個例子。假如在 WHERE 條件中包含了索引列，而且InnoDB使用了這個索引，在請求和保留行級鎖時隻會考慮索引列。在接下來的例子中，第一次 UPDATE為where中b=2的每一行資料請求到并保留了x-lock；第二次UPDATE當它試圖為同樣的資料去請求x-lock時便阻塞了，因為它也使用到了定義在資料列b上的索引。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;
           

使用讀已送出（ READ COMMITTED ）隔離級别能産生的效果和啟用 innodb_locks_unsafe_for_binlog參數是一樣的，但是也有如下差別： - 啟用 innodb_locks_unsafe_for_binlog參數是一個全局設定，會影響所有會話；而隔離級别可以設定全局，也為每次會話單獨設定； -啟用innodb_locks_unsafe_for_binlog參數隻能在資料庫啟動時設定；而隔離級别不僅可以在資料庫啟動時設定，而且在資料庫運作中也可以修改。 是以， 與   innodb_locks_unsafe_for_binlog相 比， READ COMMITTED 隔離級别 提供了更好更靈活的控制。 【備注】行鎖 record lock詳細介紹參考 https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_record_lock。

3、 READ UNCOMMITTED 查詢（SELETCT）語句會被在無鎖的形式下執行，但是可能會讀到一行資料的早期版本（這句話意思是：SElECT操作的資料行是不被鎖定的，其他事務也可以操作，這樣目前事務就可能讀到其它事務沒有commit的資料）。是以，使用這個隔離級别不能保證一緻性讀取。這也被稱為髒讀（ dirty read ）。其他方面，它的工作方式和READ COMMITTED很像。

4、SERIALIZABLE 這個隔離級别和可重複讀（REPEATABLE READ）很像，不過假如資料庫自動送出（ autocommit）被關閉的話，InnoDB隐式地将普通的 SELECT操作轉換為了 SELECT ... LOCK IN SHARE MODE；假如資料庫自動送出是開啟的話，這次 SELECT就是它自己的事務 。是以，它被認為是隻讀的，并且如果作為一緻（非鎖定）讀取執行，則可以被序列化，并且不需要阻塞其他事務。（假如想讓一個普通的 SELECT阻塞其它事務去修改索要查詢的資料，可以把資料庫自動送出關閉。）

Copyright © 2018 Ansel. All rights reserved.