快照讀與加鎖讀 - MySQL 8.0官方檔案筆記（三）-有解無憂

檔案版本：8.0
來源：Consistent Nonlocking Reads 、Locking Reads
上一篇：事務隔離級別

本篇主要介紹InnoDB的快照讀與加鎖讀，
快照讀嚴格來說應該翻譯為一致的無鎖讀（Consistent Nonlocking Reads），但國內一般都稱作快照讀，在之前的篇章中提到的普通讀、無鎖讀和一致讀等等，實際上講的都是快照讀的概念，

快照讀

快照讀，指的是InnoDB使用多版本機制，為一次查詢提供資料庫在特定時間點的快照，查詢可以看到時間點之前已提交事務產生的變更，看不到未提交或時間點之后提交的，但有個特例，同一事物內快照生成之前產生的變更，也是可以看到的，這個特性會致使這樣一種奇怪的情形：如果你更新了表的一些行，SELECT陳述句可以看到你做出的變更，但也可能看到某些行的老版本，如果其它事務也更新了這個表的一些行，你所看到的表的狀態可能從未在資料庫中存在過，

如果事務的隔離級別是可重復讀（默認級別），同一事務內的所有快照讀都會讀取事務內第一次快照讀時建立的快照，通過提交當前事物和發起新的查詢，可以重繪快照，

而在讀已提交級別下，每次快照讀都會建立最新快照，

在讀已提交和可重復讀級別下，InnoDB會將SELECT陳述句全部轉換為快照讀，快照讀不會給它讀取的表上任何鎖，因此在快照讀執行的同時，其它事務可以對表進行任意修改，
假設你在默認的可重復讀級別下，當你發起一次快照讀（也就是普通的SELECT陳述句），InnoDB會給你的事務分配一個時間點：即記錄查詢看到資料庫資料的時間點，如果其它事務在這個時間點之后洗掉了一行并提交，你的事務將不會感知到行被洗掉，插入和更新同理，

注意

資料庫快斬訓制會應用在事務中的SELECT陳述句，但不會應用于DML陳述句，如果你插入或修改了某些行然后提交，同時另一個可重復讀事務發起的DELETE或UPDATE陳述句，可以影響到這些剛剛提交的行，即使不能查詢到它們，如果一個事務對另一個事務提交的行做更新或洗掉操作，這些本來不可見的變更將對前者可見，例如，你可能會遇到如下場景：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- 回傳 0：沒有匹配記錄，
DELETE FROM t1 WHERE c1 = 'xyz';
-- 將洗掉其它事務最近提交的若干行，

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- 回傳 0：沒有匹配記錄，
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- 影響 10 行：另一個事務剛剛提交了10條值為‘abc’的記錄，
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- 回傳 10: 這個事務現在可以看到它剛剛修改的行，

你可以通過提交事務并執行SELECT或START TRANSACTION WITH CONSISTENT SNAPSHOT陳述句來重繪快照時間點，

上述的這些機制被稱作多版本并發控制，

在下面的例子中，要想會話A看到會話B插入的行，必須讓會話A和會話B都提交事務，從而使快照時間點重繪到B提交以后，

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
時間線
|          SELECT * FROM t;
|          空集
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           空集
                                  COMMIT;

           SELECT * FROM t;
           空集

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

如果你想保證看到資料庫的最新狀態，使用讀已提交級別，亦或發起加鎖讀：

SELECT * FROM t FOR SHARE;

在讀已提交級別下，同一事物內的每一次快照讀都將設定自己的快照，而使用FOR SHARE陳述句，取而代之是產生一次加鎖讀：SELECT陳述句將阻塞直至事務獲得到最新的行記錄，

注意：FOR SHARE陳述句并不會重繪可重復讀事務的快照時間點，

快照讀對特定的DDL陳述句無效：

快照讀對DROP TABLE無效，因為MySQL不能使用一張被釋放的表，InnoDB已經銷毀了表，
快照讀對進行了拷貝-洗掉操作的ALTER TABLE無效，當重新發起一次快照讀時，事務將看不到新表中的行因為在快照生成時這些行還不存在，此時，事務會回傳一個錯誤：ER_TABLE_DEF_CHANGED，“表定義已改變，請重試事務”，

對于ALTER TABLE操作，MySQL提供三種演算法：

COPY：將在原表的拷貝中進行，隨后表資料將從原表中逐行拷貝，期間不允許DML陳述句并行，

INPLACE：不進行拷貝操作，但可能會原地重建表，在準備與執行階段，ALTER操作可能會獲取表的元資料獨占鎖，期間一般允許DML陳述句并行，

INSTANT：只會修改資料字典中的元資料，在準備與執行階段不會獲取表的元資料獨占鎖，表資料也不會被影響，操作是瞬時的，允許DML陳述句并行，

當ALTER陳述句不支持INSTANT和INPLACE時，MySQL才會采取COPY演算法進行拷貝-洗掉，例如更改欄位順序、改變欄位型別或添加一個主鍵等等，關于觸發COPY的各類條件詳見：Online DDL Operations

對于SELECT陳述句的變種，如未明確FOR UPDATE或FOR SHARE的INSERT INTO ... SELECT，UPDATE ... (SELECT) 和 CREATE TABLE ... SELECT陳述句：

默認情況下，InnoDB對這些陳述句使用更強的鎖，而SELECT部分則表現得像讀已提交級別，即每一次快照讀都會設定最新的快照，即使在同一事務內，
若要在這種情況下進行無鎖讀，須將事務設定為讀未提交或讀已提交，以避免讀行時上鎖，

加鎖讀

如果你在一個事務內先查詢資料，再插入或更新與之相關聯的資料，普通的SELECT陳述句不會給予足夠的安全性保證，其他事務可以更新或洗掉你剛剛查詢的行，InnoDB支持兩種型別的加鎖讀來提供額外的安全性保證：

SELECT ... FOR SHARE

對查詢到的行施加共享鎖，其它事務可以讀取這些行，但在你的事務提交前不能修改它們，如果其它事務事先修改了這些行而沒有提交，你的查詢將阻塞直至其它事務提交，隨后獲取到最新的值，

注意

SELECT ... FOR SHARE是SELECT ... LOCK IN SHARE MODE的替代語法，但LOCK IN SHARE MODE仍保留下來作向后兼容，陳述句功能是一致的，但FOR SHARE支持OF table_name，NOWAIT和and SKIP LOCKED選項，詳見Locking Read Concurrency with NOWAIT and SKIP LOCKED，
SELECT ... FOR UPDATE

對于掃描到的索引值，鎖定其所在行和任何關聯的索引項，這一點與執行UPDATE陳述句效果相同，其它事務如果對這些行進行更新、執行SELECT ... FOR SHARE或特定隔離級別下讀取，將阻塞，如果對快照視圖中存在的行加鎖，快照讀會忽略這些鎖，（老版本的行記錄不能被鎖定，因為它們是通過將行拷貝到記憶體中并執行回滾日志來重建的，）

SELECT ... FOR UPDATE需要SELECT權限，以及DELETE，LOCK TABLES或UPDATE的至少一種權限，

無論在單表還是多表中，上述兩種陳述句在處理樹形結構與圖形結構的資料時非常有用，它們能在遍歷圖邊界或窮舉樹分支的同時，保留了回退與改變“結點”值的權利，

FOR SHARE和FOR UPDATE查詢所產生的鎖將在事務提交或回滾后釋放，

注意

加鎖讀只有在自動提交關閉的情況下才可用，（要么使用START TRANSACTION開啟事務，要么設定autocommit為0，）

宣告在外部陳述句的加鎖讀不會鎖定嵌套子查詢中的行，除非加鎖讀也宣告在子查詢中，例如，下列陳述句不會鎖定t2表中的行：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

若要鎖定t2表中的行，在子查詢中宣告加鎖讀：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

加鎖讀案例

假設你想在child表插入一行，并且確保child行在parent表有一個父行，你的應用代碼可以在這個操作序列中的確保參照完整性，

首先，使用快照讀查詢PARENT表來驗證父行是否存在，然而你能安全地將子行插入CHILD表嗎？不行，因為其它事務可以在你執行SELECT和INSERT操作之間洗掉該父行，而你無法感知到，

為了避免這一可能發生的問題，發起帶FOR SHARE的SELECT陳述句：

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

在FOR SHARE查詢回傳了父行‘Jones’后，你就可以安全地將子行插入到CHILD表并提交了，任何嘗試獲取PARENT表中可用行的獨占鎖的事務都會等待，直到你的事務結束，同時也代表每個表中的資料都處于一致狀態，

舉另一個例子，在CHILD_NODES表中設有一整形計數欄位，用于給每個插入到CHILD表的子行賦一個唯一標識，在這種情況下不要使用快照讀或共享鎖來讀取當前計數器的值，因為兩個資料庫用戶可能看到計數器的同一個值，并將在CHILD表插入相同標識值的行，于是會發生重復鍵錯誤，

在這種情況下，FOR SHARE不是一個好的解決方案，如果兩個事務同時讀取到相同的計數值，至少會有一個事務在試圖更新計數器時因死鎖而終止，

如何實作對計數器的讀取和迭代？先使用FOR UPDATE對計數器發起加鎖讀，然后迭代計數器，例如：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE陳述句讀取最新的可用資料，為讀取到的行設定獨占鎖，因此就可以在隨后的UPDATE中匹配到的行上設定相同的鎖，

上面的案例只是為了說明SELECT ... FOR UPDATE如何作業，在MySQL中，生成唯一標識的作業可以通過一條陳述句完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

其中SELECT陳述句只是為了獲得標識資訊（與當前的資料庫連接相關聯），不會查詢任何表，

使用NOWAIT和SKIP LOCKED調整加鎖讀并發性

如果事務鎖住一行資料，其它事務對同一行發起SELECT ... FOR UPDATE或SELECT ... FOR SHARE查詢將必須等待鎖被釋放，這一特性防止了那些查詢出來并將被更新的行被其它事務更新或者洗掉，然而有時你想要查詢行被上鎖時陳述句立即回傳，或者可以接受結果集中不包含被上鎖的行時，就沒有必要等待行鎖被釋放，

通過在SELECT ... FOR UPDATE或SELECT ... FOR SHARE中設定NOWAIT與SKIP LOCKED選項，可以避免不必要的鎖等待，

NOWAIT
使用了`NOWAIT`的加鎖讀將不會等待獲取行鎖，查詢立即執行，在行被上鎖時回傳一個錯誤，
SKIP LOCKED

使用了SKIP LOCKED的加鎖讀也不會等待獲取行鎖，查詢立即執行，從結果集中剔除被上鎖的行，

注意

跳過加鎖行的查詢將回傳一個非一致性的資料視圖，因此SKIP LOCKED不適合常規事務場景，但可以用于在多個事務訪問佇列型別的表時避免鎖競爭，

NOWAIT和SKIP LOCKED只能用于行級鎖，

在復制陳述句中使用NOWAIT和SKIP LOCKED是不安全的，

下面演示NOWAIT和SKIP LOCKED如何使用，會話1開啟了一個事務并獲取了一行行鎖，會話2在同一行發起附帶NOWAIT選項的加鎖讀，因為請求行被會話1鎖住，加鎖讀立刻回傳了錯誤，會話3發起附帶SKIP LOCKED的加鎖讀，則回傳了不包含會話1鎖住行的結果集，

# 會話 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# 會話 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# 會話 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/230253.html

標籤：MySQL

上一篇：MySQL主從同步及讀寫分離

下一篇：MySQL資料庫基礎學習筆記