MySQL 鎖和 MVCC

ACatSmiling發表於2024-07-23
MySqlMVC

Author: ACatSmiling

Since: 2024-07-22

鎖

鎖是計算機協調多個程序或執行緒併發訪問某一資源的機制。在程式開發中會存在多執行緒同步的問題，當多個執行緒併發訪問某個資料的時候，尤其是針對一些敏感的資料（比如訂單、金額等)，就需要保證這個資料在任何時刻最多隻有一個執行緒在訪問，保證資料的完整性和一致性。在開發過程中加鎖是為了保證資料的一致性，這個思想在資料庫領域中同樣很重要。

在資料庫中，除傳統的計算資源（如 CPU、RAM、I/O 等）的爭用以外，資料也是一種供許多使用者共享的資源。為保證資料的一致性，需要對併發操作進行控制，因此產生了鎖。同時，鎖機制也為實現 MySQL 的各個隔離級別提供了保證。鎖衝突也是影響資料庫併發訪問效能的一個重要因素。所以鎖對資料庫而言顯得尤其重要，也更加複雜。

MySQL 併發事務訪問相同記錄

MySQL 併發事務訪問相同記錄的情況,大致可以劃分為三種：

  1. 讀-讀
  2. 寫-寫
  3. 讀-寫或寫-讀

讀-讀

讀-讀，即併發事務相繼讀取相同的記錄。讀取操作本身不會對記錄有任何影響，並不會引起什麼問題，所以允許這種情況的發生。

寫-寫

寫-寫，即併發事務相繼對相同的記錄做出改動。

在這種情況下會發生髒寫的問題，任何一種隔離級別都不允許這種問題的發生。所以在多個未提交事務相繼對一條記錄做改動時，需要讓它們排隊執行，這個排隊的過程其實是透過鎖來實現的。這個所謂的鎖其實是一個記憶體中的結構，在事務執行前本來是沒有鎖的，也就是說一開始是沒有鎖結構和記錄進行關聯的，如圖所示：

當一個事務想對這條記錄做改動時，首先會看看記憶體中有沒有與這條記錄關聯的鎖結構，當沒有的時候就會在記憶體中生成一個鎖結構與之關聯。比如，事務T1要對這條記錄做改動，就需要生成一個鎖結構與之關聯：

在鎖結構裡有很多資訊，為了簡化理解，只把兩個比較重要的屬性拿了出來:

  • trx 資訊：代表這個鎖結構是哪個事務生成的。
  • is_waiting：代表當前事務是否在等待。

當事務 T1 改動了這條記錄後，就生成了一個鎖結構與該記錄關聯，因為之前沒有別的事務為這條記錄加鎖，所以 is_waiting 屬性就是 false，把這個場景就稱之為獲取鎖成功，或者加鎖成功，然後就可以繼續執行操作了。

在事務 T1 提交之前，另一個事務T2也想對該記錄做改動，那麼先看看有沒有鎖結構與這條記錄關聯，發現有一個鎖結構與之關聯後，然後也生成了一個鎖結構與這條記錄關聯，不過鎖結構的 is_waiting 屬性值為 true，表示當前事務需要等待，把這個場景就稱之為獲取鎖失敗，或者加鎖失敗。如圖所示：

在事務 T1 提交之後，就會把該事務生成的鎖結構釋放掉，然後看看還有沒有別的事務在等待獲取鎖，發現了事務 T2 還在等待獲取鎖，所以把事務 T2 對應的鎖結構的 is_waiting 屬性設定為 false，然後把該事務對應的執行緒喚醒，讓它繼續執行，此時事務 T2 就算獲取到鎖了。效果圖就是這樣：

小結幾種說法:

  • 不加鎖：意思就是不需要在記憶體中生成對應的鎖結構，可以直接執行操作。
  • 獲取鎖成功，或者加鎖成功：意思就是在記憶體中生成了對應的鎖結構，而且鎖結構的 is_waiting 屬性為 false，也就是事務可以繼續執行操作。
  • 獲取鎖失敗，或者加鎖失敗，或者沒有獲取到鎖：意思就是在記憶體中生成了對應的鎖結構，不過鎖結構的 is_waiting 屬性為 true，也就是事務需要等待，不可以繼續執行操作。

讀-寫或寫-讀

讀-寫或者寫-讀，即一個事務進行讀取操作，另一個進行改動操作，這種情況下可能發生髒讀、不可重複讀、幻讀的問題。

各個資料庫廠商對 SQL 標準的支援都可能不一樣，比如 MySQL 在REPEATABLE READ隔離級別上就已經解決了幻讀的問題。

那麼，怎麼解決髒讀、不可重複讀、幻讀這些問題呢？ 其實有兩種可選的解決方案：

方案一：讀操作利用多版本併發控制 MVCC，寫操作進行加鎖。

所謂的 MVCC，就是生成一個 ReadView，透過 ReadView 找到符合條件的記錄版本（歷史版本由 undo 日誌構建）。查詢語句只能讀到在生成 ReadView 之前已提交事務所做的更改，在生成 ReadView 之前未提交的事務或者之後才開啟的事務所做的更改是看不到的。而寫操作肯定針對的是最新版本的記錄，讀記錄的歷史版本和改動記錄的最新版本本身並不衝突，也就是採用 MVCC 時，讀-寫操作並不衝突。

普通的 SELECT 語句在 READ COMMITTED 和 REPEATABLE READ 隔離級別下會使用到 MVCC 讀取記錄。

  • 在READ COMMITTED隔離級別下，一個事務在執行過程中，每次執行 SELECT 操作時都會生成一個 ReadView，ReadView 的存在本身就保證了事務不可以讀取到未提交的事務所做的更改，也就是避免了髒讀現象。
  • 在REPEATABLE READ隔離級別下，一個事務在執行過程中，只有第一次執行 SELECT 操作才會生成一個 ReadView，之後的 SELECT 操作都複用這個 ReadView，這樣也就避免了不可重複讀 和幻讀的問題。

方案二：讀、寫操作都採用加鎖的方式。

如果我們的一些業務場景不允許讀取記錄的舊版本，而是每次都必須去讀取記錄的最新版本。比如，在銀行存款的事務中，你需要先把賬戶的餘額讀出來，然後將其加上本次存款的數額，最後再寫到資料庫中。在將賬戶餘額讀取出來後，就不想讓別的事務再訪問該餘額，直到本次存款事務執行完成，其他事務才可以訪問賬戶的餘額。這樣在讀取記錄的時候就需要對其進行加鎖操作，這樣也就意味著讀操作和寫操作也像寫-寫操作那樣排隊執行。

  • 髒讀問題的產生，是因為當前事務讀取了另一個未提交事務寫的一條記錄，如果另一個事務在寫記錄的時候就給這條記錄加鎖，那麼當前事務就無法繼續讀取該記錄了，所以也就不會有髒讀問題的產生了。
  • 不可重複讀問題的產生，是因為當前事務先讀取一條記錄，另外一個事務對該記錄做了改動之後並提交之後，當前事務再次讀取時會獲得不同的值，如果在當前事務讀取記錄時就給該記錄加鎖，那麼另一個事務就無法修改該記錄，自然也不會發生不可重複讀了。
  • 幻讀問題的產生，是因為當前事務讀取了一個範圍的記錄，然後另外的事務向該範圍內插入了新記錄，當前事務再次讀取該範圍的記錄時發現了新插入的新記錄。採用加鎖的方式解決幻讀問題就有一些麻煩，因為當前事務在第一次讀取記錄時幻影記錄並不存在，所以讀取的時候加鎖就有點尷尬（因為並不知道給誰加鎖）。

小結對比發現：

  • 採用 MVCC 方式的話，讀-寫操作彼此並不衝突，效能更高。
  • 採用加鎖方式的話，讀-寫操作彼此需要排隊執行，影響效能。

一般情況下當然願意採用 MVCC 來解決讀-寫操作併發執行的問題，但是業務在某些特殊情況下，要求必須採用加鎖的方式執行。下面就講解下 MySQL 中不同類別的鎖。

鎖的分類

按資料操作的型別劃分：讀鎖、寫鎖

對於資料庫中併發事務的讀-讀情況並不會引起什麼問題，對於寫-寫、讀-寫或寫-讀這些情況可能會引起一些問題，需要使用 MVCC 或者加鎖的方式來解決它們。在使用加鎖的方式解決問題時，由於既要允許讀-讀情況不受影響，又要使寫-寫、讀-寫或寫-讀情況中的操作相互阻塞，所以 MySQL 實現一個由兩種型別的鎖組成的鎖系統來解決。這兩種型別的鎖，通常被稱為共享鎖 (Shared Lock，S Lock) 和排他鎖 (Exclusive Lock，X Lock)，也叫讀鎖 (Read Lock) 和寫鎖 (Write Lock)。

  • 讀鎖：也稱為共享鎖、英文用 S 表示。針對同一份資料，多個事務的讀操作可以同時進行而不會互相影響，相互不阻塞的。
  • 寫鎖：也稱為排他鎖、英文用 X 表示。當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。這樣就能確保在給定的時間裡，只有一個事務能執行寫入，並防止其他使用者讀取正在寫入的同一資源。
  • 舉例（行級讀寫鎖)︰ 如果一個事務 T1 已經獲得了某個行 r 的讀鎖，那麼此時另外的一個事務 T2 是可以再去獲得這個行 r 的讀鎖的，因為讀取操作並沒有改變行 r 的資料。但是，如果某個事務 T3 想獲得行 r 的寫鎖，則它必須等待事務 T1、T2 釋放掉行 r 上的讀鎖才行。

注意：對於 InnoDB 引擎來說，讀鎖和寫鎖即可以加在表上，也可以加在行上。

鎖的相容情況（這裡的相容是指對同一張表或同一個記錄的鎖的相容性情況）：

相容情況 X 鎖 S 鎖
X 鎖 不相容 不相容
S 鎖 不相容 相容
鎖定讀

在採用加鎖方式解決髒讀、不可重複讀、幻讀這些問題時，讀取一條記錄時需要獲取該記錄的 S 鎖，其實是不嚴謹的，有時候需要在讀取記錄時就獲取記錄的 X 鎖 ，來禁止別的事務讀寫該記錄，為此 MySQL 提出了兩種比較特殊的 SELECT 語句格式：

  1. 對讀取的記錄加 S 鎖∶

    SELECT ... LOCK IN SHARE MODE
# 或
SELECT ... FOR SHARE # 8.0新增語法
    • 在普通的 SELECT 語句後邊加 LOCK IN SHARE MODE，如果當前事務執行了該語句，那麼它會為讀取到的記錄加 S 鎖，這樣允許別的事務繼續獲取這些記錄的 S 鎖（比方說別的事務也使用 SELECT … LOCK IN SHARE MODE 語句來讀取這些記錄），但是不能獲取這些記錄的 X 鎖（比如使用 SELECT … FOR UPDATE 語句來讀取這些記錄，或者直接修改這些記錄）。如果別的事務想要獲取這些記錄的 X 鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的 S 鎖釋放掉。

  2. 對讀取的記錄加 X 鎖：

    SELECT ... FOR UPDATE
    • 在普通的 SELECT 語句後邊加 FOR UPDATE，如果當前事務執行了該語句，那麼它會為讀取到的記錄加 X 鎖，這樣既不允許別的事務獲取這些記錄的 S 鎖（比方說別的事務使用 SELECT … LOCK IN SHARE MODE 語句來讀取這些記錄），也不允許獲取這些記錄的 X 鎖（比如使用 SELECT … FOR UPDATE 語句來讀取這些記錄，或者直接修改這些記錄)。如果別的事務想要獲取這些記錄的 S 鎖或者 X 鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的 X 鎖釋放掉。

案例演示：

  • S ---> S：事務 A 先獲取 account 表的 S 鎖，此時，事務 B 也可以正常獲得 account 表的 S 鎖，並讀取記錄。

  • S ---> X：事務 A 先獲取 account 表的 S 鎖，此時，事務 B 無法獲取 account 表的 X 鎖，被阻塞，直到事務 A 提交，事務 B 才可以正常獲取 account 表的 X 鎖。

  • X ---> S：事務 A 先獲取 account 表的 X 鎖，此時，事務 B 無法獲取 account 表的 S 鎖，被阻塞，直到事務 A 提交，事務 B 才可以正常獲取 account 表的 S 鎖。

  • X ---> X：事務 A 先獲取 account 表的 X 鎖，此時，事務 B 無法獲取 account 表的 X 鎖，被阻塞，直到事務 A 提交，事務 B 才可以正常獲取 account 表的 X 鎖。

在 MySQL 5.7 及之前的版本，SELECT … FOR UPDATE，如果獲取不到鎖，會一直等待，直到 innodb_lock_wait_timeout 超時。在 MySQL 8.0 版本中，在 SELECT … FOR UPDATE，SELECT … FOR SHARE 後新增NOWAIT、SKIP LOCKED語法，可以跳過鎖等待，或者跳過鎖定。

如果查詢的行已經加鎖：

  • 那麼 NOWAIT 會立即報錯返回。
  • 而 SKIP LOCKED 也會立即返回，只是返回的結果中不包含被鎖定的行。

演示：

# 事務A，開啟事務，先獲取X鎖
mysql> begin ;
mysql> select * from account for update;
+-- --+--------+- ------ +
| id  |    NAME|  balance|
|   1 │     張三|    40.00|
|   2 |     李四|    0.00 |
|   3 |     王五|   100.00|
+--- -+--------+- ------ +

# 事務B
mysql> begin ;
mysql> select * from account for update nowait;
# 報錯返回
ERROR 3572 (HYO00): Statement aborted because lock(s) could not be acquired immediately and NOMAIT is set.

mysql> select * from account for update skip locked;
# 因為事務A獲得X鎖，所以查到的記錄是空的
Empty set (o.00 sec)

mysql> commit;
Query Ok,o rows affected (0.00 sec)
寫操作

平常所用到的寫操作無非是 DELETE、UPDATE、INSERT 這三種：

  • DELETE：對一條記錄做 DELETE 操作的過程，其實是先在 B+ 樹中定位到這條記錄的位置，然後獲取這條記錄的 X 鎖，再執行 delete mark 操作。也可以把這個定位待刪除記錄在 B+ 樹中位置的過程，看成是一個獲取 X 鎖的鎖定讀。
  • UPDATE：在對一條記錄做 UPDATE 操作時，分為三種情況。
    • 情況 1：未修改該記錄的鍵值（例如主鍵），並且被更新的列佔用的儲存空間在修改前後未發生變化。則先在 B+ 樹中定位到這條記錄的位置，然後再獲取一下記錄的 X 鎖，最後在原記錄的位置進行修改操作。也可以把這個定位待修改記錄在 B+ 樹中位置的過程，看成是一個獲取 X 鎖的鎖定讀。
    • 情況 2：未修改該記錄的鍵值，並且至少有一個被更新的列佔用的儲存空間在修改前後發生變化。則先在 B+ 樹中定位到這條記錄的位置，然後再獲取一下記錄的 X 鎖，將該記錄徹底刪除掉（就是把記錄徹底移入垃圾連結串列)，最後再插入一條新記錄。這個定位待修改記錄在 B+ 樹中位置的過程，看成是一個獲取 X 鎖的鎖定讀，新插入的記錄由 INSERT 操作提供的隱式鎖進行保護。
    • 情況 3：修改了該記錄的鍵值。則相當於在原記錄上做 DELETE 操作之後再來一次 INSERT 操作，加鎖操作就需要按照 DELETE 和 INSERT 的規則進行。（同情況 2）
  • INSERT：一般情況下，新插入一條記錄的操作並不加鎖，透過一種稱之為隱式鎖的結構，來保護這條新插入的記錄在本事務提交前不被別的事務訪問。（因為插入之前就沒有要鎖的記錄，所以也就不需要加 X 鎖了）

按資料操作的粒度劃分：表級鎖、頁級鎖、行鎖

為了儘可能提高資料庫的併發度，每次鎖定的資料範圍越小越好，理論上每次只鎖定當前操作的資料的方案會得到最大的併發度，但是管理鎖是很耗資源的事情（涉及獲取、檢查、釋放鎖等動作）。因此資料庫系統需要在高併發響應和系統效能兩方面進行平衡，這樣就產生了鎖粒度 (Lock granularity)的概念。

對一條記錄加鎖影響的也只是這條記錄而已，我們就說這個鎖的粒度比較細；其實一個事務也可以在表級別進行加鎖，自然就被稱之為表級鎖或者表鎖，對一個表加鎖影響整個表中的記錄，我們就說這個鎖的粒度比較粗。鎖的粒度主要分為表級鎖、頁級鎖和行鎖。

表鎖（Table Lock）

表鎖會鎖定整張表，它是 MySQL 中最基本的鎖策略，並不依賴於儲存引擎（不管是 MySQL 的什麼儲存引擎，對於表鎖的策略都是一樣的），並且表鎖是開銷最小的策略（因為粒度比較大）。由於表級鎖一次會將整個表鎖定，所以可以很好的避免死鎖問題。當然，鎖的粒度大所帶來最大的負面影響就是出現鎖資源爭用的機率也會最高，導致併發率大打折扣。

表級別的 S 鎖和 X 鎖

在對某個表執行 SELECT、INSERT、DELETE、UPDATE 語句時，InnoDB 儲存引擎是不會為這個表新增表級別的 S 鎖或者 X 鎖的。在對某個表執行一些諸如 ALTER TABLE、DROP TABLE 這類的 DDL 語句時，其他事務對這個表併發執行諸如 SELECT、INSERT、DELETE、UPDATE 的語句會發生阻塞。同理，某個事務中對某個表執行 SELECT、INSERT、DELETE、UPDATE 語句時，在其他會話中對這個表執行 DDL 語句也會發生阻塞。這個過程其實是透過在 Server 層使用一種稱之為後設資料鎖 (Metadata Locks，簡稱 MDL)的結構來實現的。

一般情況下，不會使用 InnoDB 儲存引擎提供的表級別的 S 鎖和 X 鎖（因為 InnoDB 支援更小粒度的行鎖），只會在一些特殊情況下，比方說崩潰恢復過程中用到。比如，在系統變數 autocommit=0，innodb_table_locks = 1 時，手動獲取 InnoDB 儲存引擎提供的表 t 的 S 鎖或者 X 鎖，可以這麼寫：

  • LOCK TABLES t READ：InnoDB 儲存引擎會對錶 t 加表級別的 S 鎖 。
  • LOCK TABLES t WRITE：InnoDB 儲存引擎會對錶 t 加表級別的 X 鎖 。

儘量避免在使用 InnoDB 儲存引擎的表上使用 LOCK TABLES 這樣的手動鎖表語句，它們並不會提供什麼額外的保護，只是會降低併發能力而已。InnoDB 的厲害之處還是實現了更細粒度的行鎖，關於 InnoDB 表級別的 S 鎖和 X 鎖，大家瞭解一下就可以了。

舉例：下面我們講解 MyISAM 引擎下的表鎖。

  • 步驟 1：建立表並新增資料。

    CREATE TABLE mylock(
id INT NOT NULL PRIMARY KEY auto_increment,NAME VARCHAR(20)
)ENGINE myisam; # 儲存引擎也可以使用InnoDB，只是不建議

# 插入一條資料
INSERT INTO mylock(NAME) VALUES('a');

# 查詢表中所有的資料
SELECT * FROM mylock;
+----+------+
| id | NAME |
+----+------+
|  1 | a    |
+----+------+

  • 步驟 2：檢視錶上加過的鎖。

    SHOW OPEN TABLES WHERE In_use > 0;
# 或者
SHOW OPEN TABLES; # 主要關注In_use欄位的值>0

SHOW OPEN TABLES; # 以下為部分輸出，沒有In_use>0的記錄，表明當前資料庫中沒有被鎖定的表。
+--------------------+---------------------------+--------+-------------+
| Database           | Table                     | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| atguigudb3         | user1                     |      0 |           0 |
| mysql              | tablespace_files          |      0 |           0 |
| mysql              | column_statistics         |      0 |           0 |
| atguigudb3         | account                   |      0 |           0 |
| mysql              | table_stats               |      0 |           0 |
| mysql              | check_constraints         |      0 |           0 |
| mysql              | view_table_usage          |      0 |           0 |
| mysql              | tables_priv               |      0 |           0 |
| mysql              | column_type_elements      |      0 |           0 |
| mysql              | foreign_key_column_usage  |      0 |           0 |
| mysql              | time_zone_name            |      0 |           0 |
.........................................................................
| information_schema | TABLES                    |      0 |           0 |
| mysql              | time_zone_transition_type |      0 |           0 |
| mysql              | tablespaces               |      0 |           0 |
+--------------------+---------------------------+--------+-------------+
61 rows in set (0.01 sec)

  • 步驟 3：手動增加表鎖命令。

    LOCK TABLES t READ: # 儲存引擎會對錶t加表級別的共享鎖。共享鎖也叫讀鎖或S鎖，Share的縮寫
LOCK TABLES t WRITE; # 儲存引擎會對錶t加表級別的排他鎖。排它鎖也叫獨佔鎖、寫鎖或X鎖，是eXclusive的縮寫

    # 示例
mysql> LOCK TABLES mylock READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW OPEN TABLES WHERE In_use > 0;
+----------------+--------+--------+-------------+
| Database       | Table  | In_use | Name_locked |
+----------------+--------+--------+-------------+
| platform_basic | mylock |      1 |           0 |
+----------------+--------+--------+-------------+
1 row in set (0.00 sec)

  • 步驟 4：釋放鎖。

    # 釋放鎖
UNLOCK TABLES; # 釋放當前加鎖的表

  • 步驟 5：加讀鎖。為 mylock 表加 READ 鎖（讀阻塞寫），觀察阻塞的情況，流程如下：

    ########################SessonA中########################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables mylock read; # 事務A為表加上讀鎖
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock; # 事務A可讀
+----+------+
| id | NAME |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> update mylock set name = 'a1' where id = 1; # 事務A不可寫
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

mysql> select * from account; # 事務A不可操作其他表
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES

##################################sessionB###############################
mysql> select * from mylock; # 事務B可以讀
+----+------+
| id | NAME |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

mysql> update mylock set name = 'a2' where id = 1; # 事務B不可寫，需要等待
# 阻塞等待事務A釋放鎖....

########################SessionA##########################
mysql> unlock tables; # 事務A釋放鎖
Query OK, 0 rows affected (0.00 sec)

########################SessionB#########################
mysql> update mylock set name = 'a2' where id = 1; # 事務B獲取到鎖，進行寫操作
Query OK, 1 row affected (13.41 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mylock; # 事務B提交後，資料發生變更
+----+------+
| id | NAME |
+----+------+
|  1 | a2   |
+----+------+
1 row in set (0.00 sec)

  • 步驟 6∶加寫鎖。為 mylock 表加 WRITE 鎖，觀察阻塞的情況，流程如下：

    ########################SessionA########################################
mysql> lock tables mylock write; # 事務A為表上加寫鎖
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock; # 事務A可以讀
+----+------+
| id | NAME |
+----+------+
|  1 | a1   |
+----+------+
1 row in set (0.00 sec)

mysql> update mylock set name = 'a2' where id = 1; # 事務A可以寫
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account; # 事務A無法操作其他表
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES

############################SessionB##################################
mysql> select * from mylock; # 事務B不可以讀
# 阻塞等待事務A釋放鎖....

mysql> update mylock set name = 'a3' where id = 1; # 事務B不可以寫
# 阻塞等待事務A釋放鎖....

mysql> select * from account; # 事務B可以操作其他表
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | abc    |      40 |
|  2 | 李四   |       0 |
|  3 | 王五   |     100 |
|  4 | 馬六   |    1000 |
|  5 | 張三   |    6666 |
+----+--------+---------+
5 rows in set (0.01 sec)

總結：MyISAM 在執行查詢語句（SELECT）前，會給涉及的所有表加讀鎖，在執行增刪改操作前，會給涉及的表加寫鎖。InnoDB 儲存引擎是不會為這個表新增表級別的讀鎖或者寫鎖的。

MySQL 的表級鎖有兩種模式：（以 MyISAM 表進行操作的演示）

  • 表共享讀鎖（Table Read Lock）

  • 表獨佔寫鎖（Table Write Lock）

  • 二者的關係：

    鎖型別 自己可讀 自己可寫 自己可操作其他表 他人可讀 他人可寫
    讀鎖 是 否 否 是 否，需等待
    寫鎖 是 是 否 否，需等待 否，需等待
意向鎖（intention lock)

InnoDB 支援多粒度鎖 (multiple granularity locking)，它允許行級鎖與表級鎖共存，而意向鎖就是其中的一種表鎖。

  • 意向鎖的存在是為了協調行鎖和表鎖的關係，支援多粒度（表鎖與行鎖）的鎖並存。
  • 意向鎖是一種不與行級鎖衝突的表級鎖，這一點非常重要。
  • 意向鎖表明 "某個事務正在某些行持有了鎖或該事務準備去持有鎖"。

意向鎖分為兩種：

  • 意向共享鎖 (intention shared lock, IS)：事務有意向對錶中的某些行加共享鎖（S 鎖）。

    # 事務要獲取某些行的S鎖，必須先獲得表的IS鎖
SELECT column FROM table ... LOCK IN SHARE MODE;

  • 意向排他鎖 (intention exclusive lock, IX)：事務有意向對錶中的某些行加排他鎖（X 鎖）。

    # 事務要獲取某些行的X鎖，必須先獲得表的IX鎖
SELECT column FROM table ... FOR UPDATE;

意向鎖是由儲存引擎自己維護的，使用者無法手動操作意向鎖，在為資料行加共享鎖/排他鎖之前，InooDB 會先獲取該資料行所在資料表的對應意向鎖。

意向鎖要解決的問題：

現在有兩個事務，分別是 T1 和 T2，其中 T2 試圖在該表級別上應用共享或排它鎖，如果沒有意向鎖存在，那麼 T2 就需要去檢查各個頁或行是否存在鎖；如果存在意向鎖，那麼此時就會受到由 T1 控制的表級別意向鎖的阻塞。T2 在鎖定該表前不必檢查各個頁或行鎖，而只需檢查表上的意向鎖。簡單來說，意向鎖就是給更大一級別的空間示意裡面是否已經上過鎖。

在資料表的場景中，如果我們給某一行資料加上了排它鎖，資料庫會自動給更大一級的空間，比如資料頁或資料表加上意向鎖，告訴其他人這個資料頁或資料表已經有人上過排它鎖了，這樣當其他人想要獲取資料表排它鎖的時候，只需要瞭解是否有人已經獲取了這個資料表的意向排他鎖即可。

  • 如果事務想要獲得資料表中某些記錄的共享鎖，就需要在資料表上新增意向共享鎖。
  • 如果事務想要獲得資料表中某些記錄的排他鎖，就需要在資料表上新增意向排他鎖。

這時，意向鎖會告訴其他事務已經有人鎖定了表中的某些記錄。

舉例：事務的隔離級別預設為 REPEATABLE-READ，如下所示。

# 建立表teacher，插入6條資料
CREATE TABLE `teacher`(
  id int NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `teacher` VALUES
(1, 'zhangsan'),
(2 , 'lisi'),
(3, 'wangwu'),
(4, 'zhaoliu'),
(5, 'songhongkang'),
(6 , 'leifengyang');

# 檢視資料
mysql> select * from teacher;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | zhangsan     |
|  2 | lisi         |
|  3 | wangwu       |
|  4 | zhaoliu      |
|  5 | songhongkang |
|  6 | leifengyang  |
+----+--------------+
6 rows in set (0.00 sec)

# 假設事務A獲取了某一行的排他鎖，並未提交
begin;
SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

# 事務B想要獲取teacher表的表讀鎖，語句如下
begin ;
LOCK TABLES teacher READ;
# 阻塞...

因為共享鎖與排他鎖互斥，所以事務 B 在試圖對 teacher 表加共享鎖的時候，必須保證兩個條件：

  1. 當前沒有其他事務持有 teacher 表的排他鎖。
  2. 當前沒有其他事務持有 teacher 表中任意一行的排他鎖。

為了檢測是否滿足第二個條件，事務 B 必須在確保 teacher 表不存在任何排他鎖的前提下，去檢測表中的每一行是否存在排他鎖。很明顯這是一個效率很差的做法，但是有了意向鎖之後，情況就不一樣了。

意向鎖是怎麼解決這個問題的呢？首先需要知道意向鎖之間的相容互斥性，如下所示：

相容性 意向共享鎖（IS） 意向排他鎖（IX）
意向共享鎖（IS） 相容 相容
意向排他鎖（IX） 相容 相容

即意向鎖之間是互相相容的，雖然意向鎖之間互相相容，但是意向鎖會與普通的表級的排他鎖/共享鎖互斥，如下所示：

相容性 意向共享鎖（IS） 意向排他鎖（IX）
表級共享鎖（S） 相容 互斥
表級排他鎖（X） 互斥 互斥

注意：這裡的排他鎖/共享鎖指的都是表鎖，意向鎖不會與行級的共享鎖/排他鎖互斥，可以把意向鎖看做是一種行級鎖的標記。

回到剛才 teacher 表的例子，事務 A 獲取了某一行的排他鎖，並未提交：

BEGIN;
SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

此時，teacher 表存在兩把鎖：teacher 表上的意向排他鎖與 id 為 6 的資料行上的排他鎖。然後，事務 B 想要獲取 teacher 表的共享鎖：

BEGIN;
LOCK TABLES teacher READ;

這個時候，事務 B 檢測事務 A 持有 teacher 表的意向排他鎖，就可以得知事務 A 必然持有該表中某些資料行的排他鎖，那麼事務 B 對 teacher 表的加鎖請求就會被排斥（事務 B 阻塞），而無需去檢測表中的每一行資料是否存在排他鎖。

總結來看，意向鎖可以看作是一種標記，透過意向鎖，可以在一個事務想要對錶進行加鎖請求時，快速的判斷是否會被阻塞，提高效率。

意向鎖的併發性：

意向鎖不會與行級的共享鎖/排他鎖互斥！正因為如此，意向鎖並不會影響到多個事務對不同資料行加排他鎖時的併發性。（如果互斥，那麼行級鎖直接就退化成表鎖了，就沒有什麼優勢了）

我們擴充套件一下上面 teacher 表的例子，來概括一下意向鎖的作用（一條資料從被鎖定到被釋放的過程中，可能存在多種不同鎖，但是這裡只著重表現意向鎖）

  1. 事務 A 先獲取了某一行的排他鎖，並未提交：

    BEGIN;
SELECT * FROM teacher WHERE id = 6 FOR UPDATE;

  2. 此時，事務 A 獲取了 teacher 表上的意向排他鎖，事務 A 獲取了 id 為 6 的資料行上的排他鎖。之後，事務 B 想要獲取 teacher 表的共享鎖：

    BEGIN;
LOCK TABLES teacher READ;

  3. 事務 B 檢測到事務 A 持有 teacher 表的意向排他鎖，則事務 B 對 teacher 表的加鎖請求被排斥（事務 B 阻塞）。如果事務 C 想獲取 teacher 表中某一行的排他鎖：

    BEGIN;
SELECT * FROM teacher WHERE id = 5 FOR UPDATE;

  4. 那麼，事務 C 先申請 teacher 表的意向排他鎖，事務 C 檢測到事務 A 持有 teacher 表的意向排他鎖，因為意向鎖之間並不互斥，所以事務 C 獲取到了 teacher 表的意向排他鎖。因為 id 為 5 的資料行上不存在任何排他鎖，最終，事務 C 成功獲取到了該資料行上的排他鎖。

從上面的案例可以得到如下結論：

  • InnoDB 支援多粒度鎖，特定場景下，行級鎖可以與表級鎖共存。
  • 意向鎖之間互不排斥，但除了 IS 與 S 相容外，意向鎖會與共享鎖 / 排他鎖互斥。
  • IX，IS 是表級鎖，不會和行級的 X，S 鎖發生衝突，只會和表級的 X，S 發生衝突。
  • 意向鎖在保證併發性的前提下，實現了行鎖和表鎖共存，且滿足事務隔離性的要求。
自增鎖（AUTO-INC 鎖）

在使用 MySQL 過程中，我們可以為表的某個列新增AUTO_INCREMENT屬性。舉例：

CREATE TABLE `teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

由於這個表的 id 欄位宣告瞭 AUTO_INCREMENT，意味著在書寫插入語句時不需要為其賦值，SQL 語句修改如下所示：

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');

上邊的插入語句並沒有為 id 列顯式賦值，所以系統會自動為它賦上遞增的值，結果如下所示：

SELECT * FROM teacher;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
+----+----------+

現在看到的上面插入資料的方式，是一種簡單的插入模式。所有插入資料的方式總共分為三類，分別是 "Simple inserts"，"Bulk inserts" 和 "Mixed-mode inserts"。

  1. Simple inserts (簡單插入)：可以預先確定要插入的行數（當語句被初始處理時）的語句。包括沒有巢狀子查詢的單行和多行 INSERT … VALUES() 和 REPLACE 語句。比如我們上面舉的例子就屬於該類插入，已經確定要插入的行數。

  2. Bulk inserts (批次插入)：事先不知道要插入的行數（和所需自動遞增值的數量）的語句。比如 INSERT … SELECT，REPLACE … SELECT 和 LOAD DATA 語句，但不包括純 INSERT。InnoDB 在每處理一行，為 AUTO_INCREMENT 列分配一個新值。

  3. Mixed-mode inserts (混合模式插入)：其中一種是指那些是 Simple inserts 語句，但是指定部分新行的自動遞增值。例如 INSERT INTO teacher (id,name) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’)，只是指定了部分 id 的值，其他部分 id 的值還是自增。另一種型別的混合模式插入是 INSERT … ON DUPLICATE KEY UPDATE。

對於上面資料插入的案例，MySQL 中採用了自增鎖的方式來實現，AUTO-INC 鎖是當向使用含有 AUTO_INCREMENT 列的表中插入資料時需要獲取的一種特殊的表級鎖，在執行插入語句時就在表級別加一個 AUTO-INC 鎖，然後為每條待插入記錄的 AUTO_INCREMENT 修飾的列分配遞增的值，在該語句執行結束後，再把 AUTO-INC 鎖釋放掉。一個事務在持有 AUTO-INC 鎖的過程中，其他事務的插入語句都要被阻塞，這可以保證一個語句中分配的遞增值是連續的。也正因為此，其併發性顯然並不高，當我們向一個有 AUTO_INCREMENT 關鍵字的主鍵插入值的時候，每條語句都要對這個表鎖進行競爭，這樣的併發潛力其實是很低下的，所以 InnoDB 透過innodb_autoinc_lock_mode的不同取值來提供不同的鎖定機制，來顯著提高 SQL 語句的可伸縮性和效能。

innodb_autoinc_lock_mode 有三種取值，分別對應與不同鎖定模式：

  • innodb_autoinc_lock_mode = 0 ("傳統"鎖定模式)：在此鎖定模式下，所有型別的 INSERT 語句都會獲得一個特殊的表級 AUTO-INC 鎖，用於插入具有 AUTO_INCREMENT 列的表。這種模式其實就如我們上面的例子，即每當執行 INSERT 的時候，都會得到一個表級鎖（AUTO-INC 鎖），使得語句中生成的 auto_increment 為順序，且在 binlog 中重放的時候，可以保證 master 與 slave 中資料的 auto_increment 是相同的。因為是表級鎖，當在同一時間多個事務中執行 INSERT 的時候，對於 AUTO-INC 鎖的爭奪會限制併發能力。
  • innodb_autoinc_lock_mode = 1("連續"鎖定模式)：在 MySQL 8.0 之前，連續鎖定模式是預設的。在這個模式下，"Bulk inserts" 仍然使用 AUTO-INC 表級鎖，並保持到語句結束。這適用於所有 INSERT … SELECT，REPLACE … SELECT 和 LOAD DATA 語句。同一時刻只有一個語句可以持有 AUTO-INC 鎖。對於 "Simple inserts"（要插入的行數事先已知），則透過在 mutex（輕量鎖）的控制下獲得所需數量的自動遞增值來避免表級 AUTO-INC 鎖，它只在分配過程的持續時間內保持，而不是直到語句完成。"Simple inserts" 不使用表級 AUTO-INC 鎖，除非 AUTO-INC 鎖由另一個事務保持。如果另一個事務保持 AUTO-INC 鎖，則 "Simple inserts" 等待 AUTO-INC 鎖，如同它是一個 "Bulk inserts"。
  • innodb_autoinc_lock_mode = 2("交錯"鎖定模式)：從 MySQL 8.0 開始，交錯鎖定模式是預設的。在這種鎖定模式下，所有 INSERT 語句都不會使用表級 AUTO-INC 鎖，並且可以同時執行多個語句。這是最快和最可擴充套件的鎖定模式，但是當使用基於語句的複製或恢復方案時，從二進位制日誌重播 SQL 語句時，這是不安全的。在此鎖定模式下，自動遞增值保證在所有併發執行的所有型別的 INSERT 語句中是唯一且單調遞增的。但是，由於多個語句可以同時生成數字（即，跨語句交叉編號），為任何給定語句插入的行生成的值可能不是連續的。如果執行的語句是 "Simple inserts"，其中要插入的行數已提前知道，除了 "Mixed-mode inserts" 之外，為單個語句生成的數字不會有間隙。然而，當執行 "Bulk inserts" 時，在由任何給定語句分配的自動遞增值中可能存在間隙。
後設資料鎖（MDL 鎖）

MySQL 5.5 引入了Meta Data Lock，簡稱 MDL 鎖，屬於表鎖範疇。MDL 的作用是，保證讀寫的正確性。比如，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，增加了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。

因此，當對一個表做增刪改查操作（DML 操作）的時候，加 MDL 讀鎖；當要對錶做結構變更操作（DDL 操作）的時候，加 MDL 寫鎖。

  • 讀鎖之間不互斥，因此可以有多個執行緒同時對一張表增刪改查。
  • 讀鎖與寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性，解決了 DML 和 DDL 操作之間的一致性問題。
  • MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。

舉例：後設資料鎖的使用場景模擬。

  • 事務 A：從表中查詢資料。

  • 事務 B：修改表結構，增加新列。

  • 事務 C：檢視當前 MySQL 的程序，可以得出 B 中的阻塞就是因為 A 為 teacher 加了 MDL 鎖。

  • 在事務 B 中結束脩改，重新進行讀操作。

  • 事務 B 中之前的所有進行提交，重新開啟事務盡心修改，同時 C 中也開啟一個事務進行查詢。可以看出，事務 B 被阻塞，這是因為事務 A 拿到了 teacher 表的後設資料讀鎖，事務 B 想申請 teacher 表的後設資料寫鎖，由於讀寫鎖互斥，事務 B 需要等待事務 A 釋放後設資料鎖才能執行。而事務 C 要在表 teacher 上新申請 MDL 讀鎖的請求也會被事務 B 阻塞。如前面所說，所有對錶的增刪改查操作都需要先申請 MDL 讀鎖，現在就都會被阻塞了，也就等於這個表現在完全不可讀寫了，併發性大大降低！！！這也就是後設資料鎖可能帶來的問題。

InnoDB 中的行鎖（Row Lock）

行鎖也稱為記錄鎖，顧名思義，就是鎖住某一行（某條記錄 Row)。需要的注意的是，MySQL 伺服器層並沒有實現行鎖機制，行級鎖只在儲存引擎層實現。

  • 優點：鎖定力度小，發生鎖衝突機率低，可以實現的併發度高。
  • 缺點：對於鎖的開銷比較大，加鎖會比較慢，容易出現死鎖情況。

InnoDB 與 MyISAM 的最大不同有兩點：一是支援事務（TRANSACTION）；二是採用了行級鎖。

演示環境搭建：

# 建立表
CREATE TABLE student (
  id INT,
  name VARCHAR(20),
  class varchar (10) ,PRIMARY KEY (id)
)Engine=InnoDB CHARSET=utf8;

# 插入幾條記錄
INSERT INTO student VALUES
(1, '張三', '一班'),
(3, '李四', '一班'),
(8, '王五', '二班'),
(15, '趙六', '二班'),
(20, '錢七', '三班');

# 檢視
SELECT *FROM student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 張三   | 一班   |
|  3 | 李四   | 一班   |
|  8 | 王五   | 二班   |
| 15 | 趙六   | 二班   |
| 20 | 錢七   | 三班   |
+----+--------+--------+

student 表中的聚簇索引的簡圖如下所示：

這裡把 B+ 樹的索引結構做了一個超級簡化，只把索引中的記錄給拿了出來，下面看看都有哪些常用的行鎖型別。

記錄鎖（Record Locks）

記錄鎖也就是僅僅把一條記錄鎖上，官方的型別名稱為LOCK_REC_NOT_GAP。比如把 id 值為 8 的那條記錄加一個記錄鎖的示意圖如圖所示，僅僅是鎖住了 id 值為 8 的記錄，對周圍的資料沒有影響：

舉例如下：

程式碼演示：

###############################SessionA###################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update student set name = '張三1' where id = 1; # 為id=1的記錄加X型的行鎖
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

###############################SessionB###################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 2 lock in share mode;
Empty set (0.00 sec)

mysql> select * from student where id = 3 lock in share mode;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  3 | 李四   | 一班   |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> select * from student where id = 1 lock in share mode; 
# 阻塞...，因為sessonA中的事務對該記錄了X鎖
ERROR 1205 (HY000): Lock wait timeout exceeded; # 執行超時

mysql> update student set name = '李四1' where id = 3; # 為id=3的記錄加X型的鎖
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update student set name = '張三2' where id = 1;
# 阻塞...
ERROR 1205 (HY000): Lock wait timeout exceeded; # 執行超時

###############################SessionA###################################
mysql> commit; # 提交
Query OK, 0 rows affected (0.01 sec)

###############################SessionB###################################
mysql> update student set name = '張三2' where id = 1; # 再次嘗試獲取X鎖，執行成功
Query OK, 1 row affected (5.74 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 張三2   | 一班   |
|  3 | 李四1   | 一班   |
|  8 | 王五    | 二班   |
| 15 | 趙六    | 二班   |
| 20 | 錢七    | 三班   |
+----+---------+--------+
5 rows in set (0.00 sec)

記錄鎖是有 S 鎖和 X 鎖之分的，稱之為S 型記錄鎖和X 型記錄鎖。

  • 當一個事務獲取了一條記錄的 S 型記錄鎖後，其他事務也可以繼續獲取該記錄的 S 型記錄鎖，但不可以繼續獲取 X 型記錄鎖。
  • 當一個事務獲取了一條記錄的 X 型記錄鎖後，其他事務既不可以繼續獲取該記錄的 S 型記錄鎖，也不可以繼續獲取 X 型記錄鎖。
間隙鎖（Gap Locks）

MySQL 在 REPEATABLE-READ 隔離級別下是可以解決幻讀問題的，解決方案有兩種，可以使用 MVCC 方案解決，也可以採用 加鎖 方案解決。但是在使用加鎖方案解決時有個大問題，就是事務在第一次執行讀取操作時，那些幻影記錄尚不存在，我們無法給這些幻影記錄加上記錄鎖。InnoDB 提出了一種稱之為Gap Locks的鎖，官方的型別名稱為LOCK_GAP，我們可以簡稱為gap 鎖。

比如，把 id 值為 5 的那條記錄加一個 gap 鎖的示意圖如下：

圖中 id 值為 5 的記錄加了gap 鎖，意味著不允許別的事務在 id 值為 5 的記錄所在的間隙插入新記錄，其實就是 id 列的值 (3, 8) 這個區間的新記錄是不允許立即插入的。比如，有另外一個事務再想插入一條 id 值為 4 的新記錄，它定位到該條新記錄在 id 為 5 的間隙鎖的範圍內，所以就會阻塞插入操作，直到擁有這個 gap 鎖的事務提交了之後，id 列的值在區間 (3, 8) 中的新記錄才可以被插入。

gap 鎖僅僅是為了防止插入幻影記錄而提出的。雖然有共享 gap 鎖和獨佔 gap 鎖這樣的說法，但是它們起到的作用是相同的。而且如果對一條記錄加了 gap 鎖（不論是共享 gap 鎖還是獨佔 gap 鎖），並不會限制其他事務對這條記錄加記錄鎖或者繼續加 gap 鎖。

舉例如下：

Session1 Session2
select * from student where id = 5 lock in share mode;
select * from student where id = 5 for update;

這裡 Session 2 並不會被堵住，因為表裡並沒有 id = 5 這個記錄，因此 session 1 加的是間隙鎖 (3, 8)，而 Session 2 也是在這個間隙加的間隙鎖。它們有共同的目標，即保護這個間隙，不允許插入值。但，它們之間是不衝突的。

注意，給一條記錄加了 gap 鎖，只是不允許其他事務往這條記錄前邊的間隙插入新記錄，那對於最後一條記錄之後的間隙，也就是 student 表中 id 值為 20 的記錄之後的間隙該咋辦呢？也就是說給哪條記錄加 gap 鎖才能阻止其他事務插入 id 值在 (20, +∞) 這個區間的新記錄呢？這時候我們在講資料頁時介紹的兩條偽記錄派上用場了：

  • Infimum記錄，表示該頁面中最小的記錄。
  • Supremum記錄，表示該頁面中最大的記錄。

為了實現阻止其他事務插入 id 值在 (20, +∞) 這個區間的新記錄，可以給索引中的最後一條記錄，也就是 id 值為 20 的那條記錄所在頁面的 Supremum 記錄加上一個 gap 鎖，如圖所示：

程式碼演示：

  • 關於 X 和 S 鎖互斥的知識回顧：

    ###############################SessionA###################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 8 lock in share mode; # 為id=8的記錄加S鎖
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  8 | 王五   | 二班   |
+----+--------+--------+
1 row in set (0.00 sec)

###############################SessionB####################################
mysql> select * from student where id = 8 for update; # A已經為id=8的加了S鎖，B就不能加X鎖了
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

#################SessionA&SessionB########################################
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

  • 間隙鎖：

    #############################SessionA#####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# id=5的記錄不存在，所以無法加上記錄鎖，對於不存在的記錄，加的是間隙鎖(3,8）
mysql> select * from student where id = 5 lock in share mode; 
Empty set (0.00 sec)

############################SessionB####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 依舊加的是間隙鎖，可以看出共享gap鎖和獨佔gap鎖作用相同，而且可以重複加
mysql> select * from student where id = 5 for update;
Empty set (0.00 sec)

##########################SessionC######################################
mysql> insert into student(id,name,class) values(6,'tom','三班'); # 在間隙鎖範圍內，無法插入
# 阻塞...
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • 可以這樣實現加 id > 20的間隙鎖：

    mysql> select * from student where id = 25 lock in share mode;
Empty set (0.00 sec)

mysql> select * from student where id > 20 for update;
Empty set (0.00 sec)

注意：如果記錄存在，則使用for update或lock in share mode加的就是記錄鎖，如果記錄不存在加的就是間隙鎖。

間隙鎖的引入，可能會導致同樣的語句鎖住更大的範圍，這其實是影響了併發度的。下面的例子會產生死鎖：

Session1 Session2
begin; select *from student where id = 5 for update; begin;select * from student where id = 5 for update;
INSERT INTO student VALUES(5,'宋紅康,‘二班’);阻塞
INSERT INTO student VALUES(5,‘宋紅康’,‘二班’);(ERROR 1213(40001):Deadlock found when trying to get lock; try restarting transaction)
  • Session1 執行 select … for update 語句，由於 id = 5 這一行並不存在，因此會加上間隙鎖 (3, 8)。
  • Session2 執行 select … for update 語句，同樣會加上間隙鎖 (3, 8)，間隙鎖之間不會衝突，因此這個語句可以執行成功。
  • Session2 試圖插入一行 (5,‘宋紅康’,‘二班’)，被 Session1 的間隙鎖擋住了，只好進入等待。
  • Session1 試圖插入一行 (5,‘宋紅康’,‘二班’)，被 Session2 的間隙鎖擋住了。至此，兩個 Session 陷入死鎖。

程式碼演示：

#############################SessionA####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 5 lock in share mode; # 為id=5加間隙鎖
Empty set (0.00 sec)

#############################SessionB####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 5 for update; # 為id=5加間隙鎖
Empty set (0.00 sec)

mysql> insert into student(id,name,class) values(7,'Tom','一班'); # id=7在間隙區間
# 阻塞...

###########################SessionA######################################
# 出現了死鎖，此報錯也可能在B中出現
mysql> insert into student(id,name,class) values(6,'Jane','一班'); 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

##########################SessionB##################################
# 出現死鎖後，按照策略，讓A回滾，從而B中的Insert執行成功
mysql> insert into student(id,name,class) values(7,'Tom','一班'); 
Query OK, 1 row affected (0.00 sec)

分析：為什麼會出現死鎖呢？

當 SessionA 中執行 INSERT，就會造成 SessionB 中 INSERT 在等 SessionA 中的間隙鎖釋放，SessionA 中的 INSERT 在等 SessionB 中的間隙鎖的釋放（因為只有釋放後，這倆各自的 INSERT 才會繼續執行）。從而 SessionA 和 SessionB 相互等待，就產生了死鎖。

那為啥發生死鎖後，SessionA 執行失敗，SessionB 又成功執行了呢？這涉及 MySQL 的處理死鎖機制：當 MySQL 發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務（將持有最少行級排他鎖的事務進行回滾），讓其他事務得以繼續執行！（詳見後文死鎖章節分析）

臨鍵鎖（Next-Key Locks）

有時候既想鎖住某條記錄，又想阻止其他事務在該記錄前邊的間隙插入新記錄，所以 InnoDB 就提出了一種稱之為Next-Key Locks的鎖，官方的型別名稱為LOCK_ORDINARY，我們也可以簡稱為next-key 鎖。Next-Key Locks 是在儲存引擎是 Innodb、事務級別在 REPEATABLE-READ 的情況下使用的資料庫鎖，Innodb 預設的鎖就是 Next-Key locks。

比如，把 id 值為 8 的那條記錄加一個 next-key 鎖的示意圖如下：

next-key 鎖的本質就是一個記錄鎖和一個 gap鎖的合體，它既能保護該條記錄，又能阻止別的事務將新記錄插入被保護記錄前邊的間隙。

select * from student where id <=8 and id > 3 for update;

程式碼演示：

#############################SessionA####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 為(8,15]加鄰鍵鎖(8-15是間隙鎖，加上15是記錄鎖)
mysql> select * from student where id <= 15 and id > 8 for update; 
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
| 15 | 趙六   | 二班   |
+----+--------+--------+
1 row in set (0.00 sec)

#############################SessionB####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 15 lock in share mode; # 無法獲取記錄15的S鎖
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> select * from student where id = 15 for update; # 無法獲取記錄15的X鎖
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into student(id,name,class) values(12,'Tim','一班'); # 無法在間隙內插入資料
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
    
###############################SessionA&SessionB#######################
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
插入意向鎖（Insert Intention Locks）

我們說一個事務在插入一條記錄時，需要判斷一下插入位置是不是被別的事務加了 gap 鎖 （ next-key 鎖也包含 gap 鎖 ），如果有的話，插入操作需要等待，直到擁有 gap 鎖的那個事務提交。但是，InnoDB 規定事務在等待的時候，也需要在記憶體中生成一個鎖結構，表明有事務想在某個間隙中插入新記錄，只是現在在等待。InnoDB 就把這種型別的鎖命名為Insert Intention Locks，官方的型別名稱為LOCK_INSERT_INTENTION，我們稱為插入意向鎖。插入意向鎖是一種 gap 鎖，不是意向鎖，在 INSERT 操作時產生。

插入意向鎖是在插入一條記錄行前，由 INSERT 操作產生的一種間隙鎖，事實上，插入意向鎖並不會阻止別的事務繼續獲取該記錄上任何型別的鎖。

插入意向鎖是在插入一條記錄行前，由 INSERT 操作產生的一種間隙鎖。該鎖用以表示插入意向，當多個事務在同一區間（gap）插入位置不同的多條資料時，事務之間不需要互相等待。假設存在兩條值分別為 8 和 15 的記錄，兩個不同的事務分別試圖插入值為 11 和 12 的兩條記錄，每個事務在獲取插入行上獨佔的（排他）鎖前，都會獲取 (8, 15) 之間的間隙鎖，但是因為資料行之間並不衝突，所以兩個事務之間並不會產生衝突（阻塞等待)。總結來說，插入意向鎖的特性可以分成兩部分：

  1. 插入意向鎖是一種特殊的間隙鎖 —— 間隙鎖可以鎖定開區間內的部分記錄。

  2. 插入意向鎖之間互不排斥，所以即使多個事務在同一區間插入多條記錄，只要記錄本身（主鍵、唯一索引）不衝突，那麼事務之間就不會出現衝突等待。

注意，雖然插入意向鎖中含有意向鎖三個字，但是它並不屬於意向鎖而屬於間隙鎖，因為意向鎖是表鎖而插入意向鎖是行鎖。

比如，把 id 值為 8 的那條記錄加一個插入意向鎖，示意圖如下：

比如，現在 T1 為 id 值為 8 的記錄加了一個 gap 鎖，然後 T2 和 T3 分別想向 student 表中插入 id 值分別為 4、5 的兩條記錄，所以現在為 id 值為 8 的記錄加的鎖的示意圖就如下所示：

從圖中可以看到，由於 T1 持有 gap 鎖，所以 T2 和 T3 需要生成一個插入意向鎖的鎖結構並且處於等待狀態。當 T1 提交後會把它獲取到的鎖都釋放掉，這樣 T2 和 T3 就能獲取到對應的插入意向鎖了（本質上就是把插入意向鎖對應鎖結構的 is_waiting 屬性改為 false），T2 和 T3 之間也並不會相互阻塞，它們可以同時獲取到 id 值為 8 的插入意向鎖，然後執行插入操作。事實上，插入意向鎖並不會阻止別的事務繼續獲取該記錄上任何型別的鎖。

程式碼演示：

###############################SessionA####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 12 for update; # 加間隙鎖
Empty set (0.00 sec)

###############################SessionB####################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(id,name,class) values(12,'Tim','一班'); 
# 阻塞..同時會加插入意向鎖

##############################SessionC###################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into student(id,name,class) values(11,'Tim','一班');
# 阻塞..同時會加插入意向鎖。可以看出插入意向鎖是相互相容的，畢竟id都不同嘛

##############################SessionA#################################
mysql> commit; # 提交
Query OK, 0 rows affected (0.00 sec)

#############################SessionB###############################
mysql> insert into student(id,name,class) values(12,'Tim','一班'); # 插入成功
Query OK, 1 row affected (45.43 sec)

##############################SessionC#################################
mysql>  insert into student(id,name,class) values(11,'Tim','一班'); # 插入成功
Query OK, 1 row affected (0.00 sec)
頁鎖

頁鎖就是在頁的粒度上進行鎖定，鎖定的資料資源比行鎖要多，因為一個頁中可以有多個行記錄。當使用頁鎖的時候，會出現資料浪費的現象，但這樣的浪費最多也就是一個頁上的資料行。頁鎖的開銷介於表鎖和行鎖之間，會出現死鎖。鎖定粒度介於表鎖和行鎖之間，併發度一般。

死鎖演示：

事務A目前鎖定了頁A，想要鎖定頁B才可以執行完。
事務B目前鎖定了頁B，想要鎖定頁A才可以執行完。
### 產生死鎖

每個層級的鎖數量是有限制的，因為鎖會佔用記憶體空間，鎖空間的大小是有限的。當某個層級的鎖數量超過了這個層級的閾值時，就會進行鎖升級。鎖升級就是用更大粒度的鎖替代多個更小粒度的鎖，比如 InnoDB 中行鎖升級為表鎖，這樣做的好處是佔用的鎖空間降低了，但同時資料的併發度也下降了。

按對待鎖的態度劃分：悲觀鎖、樂觀鎖

從對待鎖的態度來看鎖的話，可以將鎖分成樂觀鎖和悲觀鎖，從名字中也可以看出這兩種鎖是兩種看待資料併發的思維方式。需要注意的是，樂觀鎖和悲觀鎖並不是鎖，而是鎖的設計思想。

悲觀鎖（Pessimistic Locking）

悲觀鎖是一種思想，顧名思義，就是很悲觀，對資料被其他事務的修改持保守態度，會透過資料庫自身的鎖機制來實現，從而保證資料操作的排它性。

悲觀鎖總是假設最壞的情況，每次去拿資料的時候都認為別人會修改，所以每次在拿資料的時候都會上鎖，這樣別人想拿這個資料就會阻塞，直到它拿到鎖（共享資源每次只給一個執行緒使用，其它執行緒阻塞，用完後再把資源轉讓給其它執行緒）。比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖，當其他執行緒想要訪問資料時，都需要阻塞掛起。Java 中 Synchronized 和 ReentrantLock 等獨佔鎖，就是悲觀鎖思想的實現。

秒殺案例 1：商品秒殺過程中，庫存數量的減少，避免出現超賣的情況。比如，商品表中有一個欄位為 quantity 表示當前該商品的庫存量。假設商品為華為 mate 40，id 為 1001，quantity 為 100 個。如果不使用鎖的情況下，操作方法如下所示：

# 第1步：查出商品庫存
select quantity from items where id = 1001;

# 第2步：如果庫存大於0，則根據商品資訊生產訂單
insert into orders(item_id) values(1001);
                    
# 第3步：修改商品的庫存，num表示購買數量
update items set quantity = quantity - num where id = 1001;

這樣寫的話，在併發量小的公司沒有大的問題，但是如果在高併發環境下，可能出現以下問題：

執行緒 A 執行緒 B
1 step 1：查詢還有100部手機 step 1：查詢還有100部手機
2 step 2：生成訂單
3 step 2：生成訂單
4 step 3：減庫存1
5 step 3：減庫存2

其中，執行緒 B 此時已經下單並且減完庫存，這個時候執行緒 A 依然去執行 step 3，就可能會造成超賣。

我們使用悲觀鎖可以解決這個問題，商品資訊從查詢出來到修改，中間有一個生成訂單的過程，使用悲觀鎖的原理就是，在查詢 items 資訊後就把當前的資料鎖定，直到修改完畢後再解鎖。那麼整個過程中，因為資料被鎖定了，就不會出現有第三者來對其進行修改了。而這樣做的前提是需要將要執行的 SQL 語句放在同一個事務中，否則達不到鎖定資料行的目的。修改如下：

# 第1步：查出商品庫存
select quantity from items where id = 1001 for update;

# 第2步：如果庫存大於0，則根據商品資訊生產訂單
insert into orders(item_id) values(1001);

# 第3步：修改商品的庫存，num表示購買數量
update items set quantity = quantity - num where id = 1001;

select … for update 是 MySQL 中的悲觀鎖。此時在 items 表中，id 為 1001 的那條資料就被鎖定了，其他的要執行 select quantity from items where id = 1001 for update; 語句的事務，必須等本次事務提交之後才能執行，這樣可以保證當前的資料不會被其它事務修改。

注意，當執行 select quantity from items where id = 1001 for update; 語句之後，如果在其他事務中執行 select quantity from items where id = 1001; 語句，並不會受第一個事務的影響，仍然可以正常查詢出資料。（存疑？）

另外，select … for update 語句執行過程中所有掃描的行都會被鎖上，因此，在 MySQL 中用悲觀鎖必須確定使用了索引，而不是全表掃描，否則將會把整個表鎖住（表鎖）。

InnoDB 行鎖是透過給索引上的索引項加鎖來實現的，只有透過索引條件檢索資料，InnoDB 才使用行級鎖，否則 InnoDB 將使用表鎖。

悲觀鎖不適用的場景較多，它存在一些不足，因為悲觀鎖大多數情況下依靠資料庫的鎖機制來實現，以保證程式的併發訪問性，同時這樣對資料庫效能開銷影響也很大，特別是 長事務 而言，這樣的開銷往往無法承受，這時就需要樂觀鎖。

樂觀鎖（Optimistic Locking）

樂觀鎖認為對同一資料的併發操作不會總髮生，屬於小機率事件，不用每次都對資料上鎖，但是在更新的時候會判斷一下在此期間，別人有沒有去更新這個資料，也就是不採用資料庫自身的鎖機制，而是透過程式來實現。在程式上，可以採用版本號機制或者CAS 機制實現。樂觀鎖適用於多讀的應用型別，這樣可以提高吞吐量。在 Java 中 java.util.concurrent.atomic 包下的原子變數類就是使用了樂觀鎖的一種實現方式：CAS 實現的。

樂觀鎖的版本號機制

在表中設計一個版本欄位 version，第一次讀的時候，會獲取 version 欄位的取值。然後對資料進行更新或刪除操作時，會執行 UPDATE ... SET version = version + 1 WHERE version = version。此時，如果已經有事務對這條資料進行了更改，修改就不會成功。

這種方式類似我們熟悉的 SVN、CVS 版本管理系統，當修改了程式碼進行提交時，首先會檢查當前版本號與伺服器上的版本號是否一致，如果一致就可以直接提交，如果不一致就需要更新伺服器上的最新程式碼，然後再進行提交。

樂觀鎖的 CAS 機制

時間戳和版本號機制一樣，也是在更新提交的時候，將當前資料的時間戳和更新之前取得的時間戳進行比較，如果兩者一致則更新成功，否則就是版本衝突。

能看到樂觀鎖就是程式設計師自己控制資料併發操作的許可權，基本是透過給資料行增加一個戳（版本號或者時間戳），從而證明當前拿到的資料是否最新。

秒殺案例 2：

# 第1步：查出商品庫存
select quantity from items where id = 1001;

# 第2步：如果庫存大於0，則根據商品資訊生產訂單
insert into orders(item_id) values(1001);

# 第3步：修改商品的庫存，num表示購買數量
update items set quantity = quantity - num, version = version + 1 where id = 1001 and version = #{version};

注意，如果資料表是讀寫分離 (主寫從讀)的表，當 matser 表中寫入的資料沒有及時同步到 slave 表中時，會造成更新一直失敗的問題（因為查詢是 slave 表，而更新是 master 表，如果 master 表的資料沒有及時同步到 slave 表，可能會出現 master 表與 slave 表的 version 不一致，導致更新失敗）。此時，需要強制讀取 master 表中的資料（即將 select 語句放到事務中即可，這時候查詢的就是 master 主庫了）。

如果對同一條資料進行頻繁的修改的話，那麼就會出現這麼一種場景，每次修改都只有一個事務能更新成功，在業務感知上面就有大量的失敗操作。我們把程式碼修改如下：

# 第1步：查出商品庫存
select quantity from items where id = 1001;

# 第2步：如果庫存大於0，則根據商品資訊生產訂單
insert into orders(item_id) values(1001);

# 第3步：修改商品的庫存，num表示購買數量
update items set quantity = quantity - num where id = 1001 and quantity - num > 0;

這樣就會使每次修改都能成功，而且不會出現超賣的現象。

兩種鎖的適用場景

從這兩種鎖的設計思想中，總結一下樂觀鎖和悲觀鎖的適用場景：

  • 樂觀鎖適合讀操作多的場景，相對來說寫的操作比較少。它的優點在於程式實現，不存在死鎖問題，不過適用場景也會相對樂觀，因為它阻止不了除了程式以外的資料庫操作。

  • 悲觀鎖適合寫操作多的場景，因為寫的操作具有排它性。採用悲觀鎖的方式，可以在資料庫層面阻止其他事務對該資料的操作許可權，防止讀-寫和寫-寫的衝突。

把樂觀鎖和悲觀鎖總結如下圖所示：

按加鎖的方式劃分：隱式鎖、顯式鎖

隱式鎖

回顧：

  • 一個事務在執行 INSERT 操作時，如果即將插入的間隙已經被其他事務加了 gap 鎖，那麼本次 INSERT 操作會阻塞，並且當前事務會在該間隙上加一個插入意向鎖。【被動加鎖】
  • 否則，一般情況下，新插入一條記錄的操作並不加鎖（後面會推翻這個結論，嚴格來說是加鎖的，加的是隱式鎖）。【主動加鎖】

那如果一個事務首先插入了一條記錄（此時並沒有在記憶體生產與該記錄關聯的鎖結構），然後另一個事務：

  • 立即使用 SELECT … LOCK IN SHARE MODE 語句讀取這條記錄，也就是要獲取這條記錄的 S 鎖，或者使用 SELECT … FOR UPDATE 語句讀取這條記錄，也就是要獲取這條記錄的 X 鎖，怎麼辦？
    • 如果允許這種情況的發生，那麼可能產生髒讀問題。
  • 立即修改這條記錄，也就是要獲取這條記錄的 X 鎖，怎麼辦?
    • 如果允許這種情況的發生，那麼可能產生髒寫問題。

這時候前邊提過的事務 id又要起作用了。把聚簇索引和二級索引中的記錄分開看一下：

  • 情景一：對於聚簇索引記錄來說，有一個 trx_id 隱藏列，該隱藏列記錄著最後改動該記錄的事務 id。那麼如果在當前事務中新插入一條聚簇索引記錄後，該記錄的 trx_id 隱藏列代表的的就是當前事務的事務id，如果其他事務此時想對該記錄新增 S 鎖或者 X 鎖時，首先會看一下該記錄的 trx_id 隱藏列代表的事務是否是當前的活躍事務，如果是的話，那麼就幫助當前事務建立一個 X 鎖 （也就是為當前事務建立一個鎖結構，is_waiting 屬性是 false），然後自己進入阻塞狀態（也就是為自己也建立一個鎖結構，is_waiting 屬性是 true）。
  • 情景二：對於二級索引記錄來說，本身並沒有 trx_id 隱藏列，但是在二級索引頁面的 Page Header 部分有一個 PAGE_MAX_TRX_ID 屬性，該屬性代表對該頁面做改動的最大的事務 id，如果 PAGE_MAX_TRX_ID 屬性值小於當前最小的活躍事務 id，那麼說明對該頁面做修改的事務都已經提交了，否則就需要在頁面中定位到對應的二級索引記錄，然後回表找到它對應的聚簇索引記錄，然後再重複情景一的做法。
  • 即：一個事務對新插入的記錄可以不顯式的加鎖（生成一個鎖結構），但是由於事務 id 的存在，相當於加了一個隱式鎖。別的事務在對這條記錄加 S 鎖或者 X 鎖時，由於隱式鎖的存在，會先幫助當前事務生成一個鎖結構，然後自己再生成一個鎖結構後進入等待狀態。隱式鎖是一種延遲加鎖的機制，從而來減少加鎖的數量。

隱式鎖在實際記憶體物件中並不含有這個鎖資訊。只有當產生鎖等待時，隱式鎖轉化為顯式鎖。

InnoDB 的 INSERT 操作，對插入的記錄不加鎖，但是此時如果另一個執行緒進行當前讀，類似以下的用例，整個過程會發生什麼呢？

############################SessionA#########################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(12,'關羽','三班'); # 此時相當於會加個隱式鎖
Query OK, 1 row affected (0.00 sec)

###########################SessionC###############################
mysql> SELECT * FROM performance_schema.data_lock_waits\G; # 隱式鎖是查不到的
Empty set (0.00 sec)

##########################SessionB################################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student lock in share mode; # 可以側面得出結論，隱式鎖是存在的
# 阻塞... # 阻塞也會導致事務A中的隱式鎖轉為顯示鎖

##########################SessionC###################################
mysql> SELECT * FROM performance_schema.data_lock_waits\G; # 查到由隱式鎖轉的顯示鎖
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 140078105288944:47:4:10:140078009627240
REQUESTING_ENGINE_TRANSACTION_ID: 421553081999600
            REQUESTING_THREAD_ID: 49
             REQUESTING_EVENT_ID: 25
REQUESTING_OBJECT_INSTANCE_BEGIN: 140078009627240
         BLOCKING_ENGINE_LOCK_ID: 140078105288088:47:4:10:140078009620736
  BLOCKING_ENGINE_TRANSACTION_ID: 17430
              BLOCKING_THREAD_ID: 49
               BLOCKING_EVENT_ID: 25
  BLOCKING_OBJECT_INSTANCE_BEGIN: 140078009620736
1 row in set (0.00 sec)

隱式鎖的邏輯過程如下：

  1. InnoDB 的每條記錄中都一個隱含的 trx_id 欄位，這個欄位存在於聚簇索引的 B+ 樹中。
  2. 在操作一條記錄前，首先根據記錄中的 trx_id 檢查該事務是否是活動的事務（未提交或回滾）。如果是活動的事務，首先將隱式鎖轉換為顯式鎖（就是為該事務新增一個鎖）。
  3. 檢查是否有鎖衝突，如果有衝突，建立鎖，並設定為 waiting 狀態。如果沒有衝突不加鎖，跳到第五步。
  4. 等待加鎖成功，被喚醒，或者超時。
  5. 寫資料，並將自己的 事務 id 寫入trx_id 欄位。
顯式鎖

透過特定的語句進行加鎖，一般稱之為顯示加鎖，例如：

  • 顯示加共享鎖：

    select ... lock in share mode;

  • 顯示加排他鎖：

    select ... for update;

其它鎖之：全域性鎖

全域性鎖就是對整個資料庫例項加鎖。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞：資料更新語句（(資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類事務的提交語句。全域性鎖的典型使用場景是：做全庫邏輯備份。

全域性鎖的命令：

Flush tables with read lock;

其它鎖之：死鎖

基本概念

死鎖是指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈。死鎖舉例如下：

舉例一：

事務 1 事務 2
1 start transaction;
update account set money = 10 where id = 1;		 start transaction;
2 update account set money = 10 where id = 2:
3 update account set money = 20 where id = 2;
4 update account set money = 20 where id = 1;

這時候，事務 1 在等待事務 2 釋放 id = 2 的行鎖，而事務 2 在等待事務 1 釋放 id = 1 的行鎖。 事務 1 和事務 2 在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略 ：

  • 一種策略是，直接進入等待，直到超時。這個超時時間可以透過引數innodb_lock_wait_timeout來設定。
  • 另一種策略是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務（將持有最少行級排他鎖的事務進行回滾），讓其他事務得以繼續執行。將引數innodb_deadlock_detect設定為 on，表示開啟這個邏輯。

在 InnoDB 中，innodb_lock_wait_timeout 的預設值是 50 秒，意味著如果採用第一個策略，當出現死鎖以後，第一個被鎖住的執行緒要過 50 秒才會超時退出，然後其他執行緒才有可能繼續執行。對於線上服務來說，這個等待時間往往是無法接受的。

但是，我們又不可能直接把這個時間設定成一個很小的值，比如 1 秒。這樣當出現死鎖的時候，確實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？所以，超時時間設定太短的話，會出現很多誤傷。

舉例二：

使用者 A 給使用者 B 轉賬 100，與此同時，使用者 B 也給使用者 A 轉賬 100。這個過程，可能導致死鎖。

# 事務1
update account set balance = balance - 108 where name = 'A'; # 操作1
update account set balance = balance + 100 where name = 'B'; # 操作3

# 事務2
update account set balance = balance - 100 where name = 'B'; # 操作2
update account set balance = balance + 100 where name = 'A'; # 操作4

產生死鎖的必要條件：

  1. 兩個或者兩個以上事務。
  2. 每個事務都已經持有鎖並且申請新的鎖。
  3. 鎖資源同時只能被同一個事務持有或者不相容。
  4. 事務之間因為持有鎖和申請鎖導致彼此迴圈等待。

死鎖的關鍵在於：兩個或以上的 Session 加鎖的順序不一致。

程式碼演示：

#####################################SessionA########################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 10 where id = 1; # 為id=1的加X鎖
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

###################################SessionB########################
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 10 where id = 3; # 為id=3的加X鎖
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

######################################SessionA###########################
mysql> update account set balance = balance + 10 where id = 3; # 想要獲取id為3的X鎖
# 阻塞...

#####################################SessionB####################
mysql> update account set balance = balance + 10 where id = 1; # 想要獲取id為1的X鎖
ERROR 1213 (40001): Deadlock found when trying to get lock; # 出現死鎖

#################################SessionA############################
mysql> update account set balance = balance + 10 where id = 3; # 阻塞解開，繼續執行
Query OK, 1 row affected (18.39 sec) # 具體死鎖為啥會被解開，下面會講哦
Rows matched: 1  Changed: 1  Warnings: 0
如何處理死鎖

方式一：等待，直到超時（預設 innodb_lock_wait_timeout=50s）。

即當兩個事務互相等待時，當一個事務等待時間超過設定的閾值時，就將其回滾，另外事務繼續進行。這種方法簡單有效，在 InnoDB 中，引數 innodb_lock_wait_timeout 用來設定超時時間。

缺點：對於線上服務來說，這個等待時間往往是無法接受的。那將此值修改短一些，比如 1 秒，0.1 秒是否合適？不合適，容易誤傷到普通的鎖等待。

方式二：使用死鎖檢測進行死鎖處理。

方式一檢測死鎖太過被動，InnoDB 還提供了wait-for graph演算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要並進入等待時，wait-for graph 演算法都會被觸發。

這是一種較為主動的死鎖檢測機制，要求資料庫儲存鎖的資訊連結串列和事務等待連結串列兩部分資訊。

基於這兩個資訊，可以繪製 wait-for graph 演算法等待圖：

死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。

一旦檢測到迴路、有死鎖，這時候 InnoDB 儲存引擎會選擇回滾 undo 量最小的事務，讓其他事務繼續執行（innodb_deadlock_detect=on 表示開啟這個邏輯）。

缺點：每個新的被阻塞的執行緒，都要判斷是不是由於自己的加入導致了死鎖，這個操作時間複雜度是 O(n)。如果 100 個併發執行緒同時更新同一行，意味著要檢測 100 * 100 = 1 萬次，1 萬個執行緒就會有 1 千萬次檢測。

如何解決？

  • 方式 1：關閉死鎖檢測，但意味著可能會出現大量的超時，會導致業務有損。
  • 方式 2：控制併發訪問的數量。比如在中介軟體中實現對於相同行的更新，在進入引擎之前排隊，這樣在 InnoDB 內部就不會有大量的死鎖檢測工作。

進一步的思路：可以考慮透過將一行改成邏輯上的多行來減少鎖衝突。比如，連鎖超市賬戶總額的記錄，可以考慮放到多條記錄上，賬戶總額等於這多個記錄的值的總和。

如何避免死鎖
  • 合理設計索引，使業務 SQL 儘可能透過索引定位更少的行，減少鎖競爭。
  • 調整業務邏輯 SQL 執行順序，避免 update/delete 等長時間持有鎖的 SQL 在事務前面。
  • 避免大事務，儘量將大事務拆成多個小事務來處理，小事務縮短鎖定資源的時間，發生鎖衝突的機率也更小。
  • 在併發比較高的系統中，不要顯式加鎖，特別是是在事務裡顯式加鎖。如 select … for update 語句，如果是在事務裡執行了 start transaction 或設定了 autocommit 等於 0，那麼就會鎖定所查詢到的記錄。
  • 降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從 RR 調整為 RC，可以避免掉很多因為 gap 鎖造成的死鎖。

鎖的記憶體結構

前邊說對一條記錄加鎖的本質就是在記憶體中建立一個鎖結構與之關聯，那麼是不是一個事務對多條記錄加鎖，就要建立多個鎖結構呢？比如：

# 事務T1
SELECT * FROM user LOCK IN SHARE MODE;

理論上建立多個鎖結構沒問題，但是如果一個事務要獲取 10000 條記錄的鎖，生成 10000 個鎖結構也太崩潰了！所以決定在對不同記錄加鎖時，如果符合下邊這些條件的記錄會放到一個鎖結構中：

  • 在同一個事務中進行加鎖操作。
  • 被加鎖的記錄在同一個頁面中。
  • 加鎖的型別是一樣的。
  • 等待狀態是一樣的。

InnoDB 儲存引擎中的鎖結構如下：

結構解析：

  1. 鎖所在的事務資訊

    • 不論是表鎖還是行鎖，都是在事務執行過程中生成的，哪個事務生成了這個鎖結構，這裡就記錄這個事務的資訊。
    • 此鎖所在的事務資訊在記憶體結構中只是一個指標，透過指標可以找到記憶體中關於該事務的更多資訊，比方說事務id等。

  2. 索引資訊

    • 對於行鎖來說，需要記錄一下加鎖的記錄是屬於哪個索引的。這裡也是一個指標。

  3. 表鎖/行鎖資訊：表鎖結構和行鎖結構在這個位置的內容是不同的。

    • 表鎖：記載著是對哪個表加的鎖，還有其他的一些資訊。
    • 行鎖：記載了三個重要的資訊。
      • Space ID：記錄所在表空間。
      • Page Number：記錄所在頁號。
      • n_bits：對於行鎖來說，一條記錄就對應著一個位元位，一個頁面中包含很多記錄，用不同的位元位來區分到底是哪一條記錄加了鎖。為此在行鎖結構的末尾放置了一堆位元位，這個 n_bits 屬性代表使用了多少位元位。n_bits 的值一般都比頁面中記錄條數多一些，主要是為了之後在頁面中插入了新記錄後也不至於重新分配鎖結構。

  4. type_mode：這是一個 32 位的數，被分成了lock_mode、lock_type和rec_lock_type三個部分，如圖所示。

    • 鎖的模式 lock_mode，佔用低 4 位，可選的值如下：
      • LOCK_IS（十進位制的 0）：表示共享意向鎖，也就是 IS 鎖。
      • LOCK_IX（十進位制的 1）：表示獨佔意向鎖，也就是 IX 鎖。
      • LOCK_S（十進位制的 2）：表示共享鎖，也就是 S 鎖。
      • LOCK_X（十進位制的 3）：表示獨佔鎖，也就是 X 鎖。
      • LOCK_AUTO_INC（十進位制的 4）：表示 AUTO-INC 鎖。
      • 在 InnoDB 儲存引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC 都算是表級鎖的模式，LOCK_S 和 LOCK_X 既可以算是表級鎖的模式，也可以是行級鎖的模式。
    • 鎖的型別 lock_type，佔用第 5～8 位，不過現階段只有第 5 位和第 6 位被使用：
      • LOCK_TABLE（十進位制的 16）：也就是當第 5 個位元位置為 1 時，表示表級鎖。
      • LOCK_REC（十進位制的 32）：也就是當第 6 個位元位置為 1 時，表示行級鎖。
    • 行鎖的具體型別 rec_lock_type，使用其餘的位來表示。只有在 lock_type 的值為 LOCK_REC 時，也就是隻有在該鎖為行級鎖時，才會被細分為更多的型別：
      • LOCK_ORDINARY（十進位制的 0）：表示 next-key 鎖 。
      • LOCK_GAP（十進位制的 512）：也就是當第 10 個位元位置為 1 時，表示 gap 鎖。
      • LOCK_REC_NOT_GAP（十進位制的 1024）：也就是當第 11 個位元位置為 1 時，表示正經記錄鎖。
      • LOCK_INSERT_INTENTION（十進位制的 2048）：也就是當第 12 個位元位置為 1 時，表示插入意向鎖。
      • 其他的型別：還有一些不常用的型別我們就不多說了。
    • is_waiting 屬性，基於記憶體空間的節省，所以把 is_waiting 屬性放到了 type_mode 這個 32 位的數字中：
      • LOCK_WAIT（十進位制的 256）：當第 9 個位元位置為 1 時，表示 is_waiting 為 true，也就是當前事務尚未獲取到鎖，處在等待狀態；當這個位元位為 0 時，表示 is_waiting 為 false，也就是當前事務獲取鎖成功。

  5. 其他資訊：為了更好的管理系統執行過程中生成的各種鎖結構而設計了各種雜湊表和連結串列。

  6. 一堆位元位：如果是行鎖結構的話，在該結構末尾還放置了一堆位元位，位元位的數量是由上邊提到的 n_bits 屬性表示的。InnoDB 資料頁中的每條記錄在記錄頭資訊中都包含一個 heap_no 屬性，偽記錄 Infimum 的 heap_no 值為 0，Supremum 的 heap_no 值為 1，之後每插入一條記錄，heap_no 值就增 1。鎖結構最後的一堆位元位就對應著一個頁面中的記錄，一個位元位對映一個 heap_no，即一個位元位對映到頁內的一條記錄。

鎖監控

關於 MySQL 鎖的監控，我們一般可以透過檢查InnoDB_row_lock等狀態變數來分析系統上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 129831 |
| Innodb_row_lock_time_avg      | 18547  |
| Innodb_row_lock_time_max      | 51095  |
| Innodb_row_lock_waits         | 7      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

對各個狀態量的說明如下：

  • Innodb_row_lock_current_waits：當前正在等待鎖定的數量。
  • Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度。（等待總時長）
  • Innodb_row_lock_time_avg：每次等待所花平均時間。（等待平均時長）
  • Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間。
  • Innodb_row_lock_waits：系統啟動後到現在總共等待的次數。（等待總次數）
  • 對於這 5 個狀態變數，比較重要的 3 個是 Innodb_row_lock_time，Innodb_row_lock_time_avg 和 Innodb_row_lock_waits。

其他監控方法：

MySQL 把事務和鎖的資訊記錄在了information_schema庫中，涉及到的三張表分別是INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS。

MySQL 5.7 及之前，可以透過 information_schema.INNODB_LOCKS 檢視事務的鎖情況，但只能看到阻塞事務的鎖；如果事務並未被阻塞，則在該表中看不到該事務的鎖情況。

MySQL 8.0 刪除了 information_schema.INNODB_LOCKS，新增了performance_schema.data_locks，可以透過 performance_schema.data_locks 檢視事務的鎖情況，和 MySQL 5.7 及之前不同，performance_schema.data_locks 不但可以看到阻塞該事務的鎖，還可以看到該事務所持有的鎖。

同時，information_schema.INNODB_LOCK_WAITS 也被performance_schema.data_lock_waits所代替。

附錄

// TODO

多版本併發控制

什麼是 MVCC

MVCC：Multiversion Concurrency Control，多版本併發控制。顧名思義，MVCC 是透過資料行的多個版本管理來實現資料庫的併發控制。這項技術使得在 InnoDB 的事務隔離級別下執行一致性讀操作有了保證。換言之，就是為了查詢一些正在被另一個事務更新的行，並且可以看到它們被更新之前的值，這樣在做查詢的時候就不用等待另一個事務釋放鎖。

MVCC 沒有正式的標準，在不同的 DBMS 中 MVCC 的實現方式可能是不同的，也不是普遍使用的（可以參考相關的 DBMS 文件）。這裡講解 InnoDB 中 MVCC 的實現機制（MySQL 其它的儲存引擎並不支援它）。

快照讀與當前讀

MVCC 在 MySQL InnoDB 中的實現主要是為了提高資料庫併發效能，用更好的方式去處理讀-寫衝突，做到即使有讀寫衝突時，也能做到不加鎖，非阻塞併發讀，而這個讀指的就是快照讀，而非當前讀。當前讀實際上是一種加鎖的操作，是悲觀鎖的實現，而 MVCC 本質是採用樂觀鎖思想的一種方式。

快照讀

快照讀：又叫一致性讀，讀取的是快照資料。不加鎖的簡單的 SELECT 都屬於快照讀，即不加鎖的非阻塞讀。比如：

SELECT * FROM player WHERE ...;

之所以出現快照讀的情況，是基於提高併發效能的考慮，快照讀的實現是基於 MVCC，它在很多情況下，避免了加鎖操作，降低了開銷。

既然是基於多版本，那麼快照讀可能讀到的並不一定是資料的最新版本，而有可能是之前的歷史版本。

快照讀的前提是隔離級別不是序列級別，序列級別下的快照讀會退化成當前讀。

當前讀

當前讀：讀取的是記錄的最新版本（最新資料，而不是歷史版本的資料），讀取時還要保證其他併發事務不能修改當前記錄，會對讀取的記錄進行加鎖。加鎖的 SELECT，或者對資料進行增刪改操作，都會進行當前讀。比如：

SELECT * FROM student LOCK IN SHARE MODE; # 共享鎖

SELECT * FROM student FOR UPDATE; # 排他鎖

INSERT INTO student values ...; # 排他鎖

DELETE FROM student WHERE ...; # 排他鎖

UPDATE student SET ...; # 排他鎖

注意：InnoDB 增刪改操作預設加 X 鎖，讀操作預設不加鎖。

知識點回顧

再談隔離級別

事務有 4 個隔離級別，可能存在三種併發問題：（準確來說是四種，還有一種是髒寫）

在 MySQL 中，預設的隔離級別是可重複讀，可以解決髒讀和不可重複讀的問題，如果僅從定義的角度來看，它並不能解決幻讀問題。如果想要解決幻讀問題，就需要採用序列化的方式，也就是將隔離級別提升到最高，但這樣一來就會大幅降低資料庫的事務併發能力。

MVCC 可以不採用鎖機制，而是透過樂觀鎖的方式來解決不可重複讀和幻讀問題！它可以在大多數情況下替代行級鎖，降低系統的開銷。

MySQL 中，是遵循上圖的處理方式，可重複讀和序列化兩種隔離級別，都可以解決幻讀的問題。

  • 如果隔離級別是可重複讀，採用的是 MVCC 的方式，這是 MySQL 預設的隔離級別。
  • 如果隔離級別是序列化，採用的是加鎖的方式。
  • 如果採用加鎖的方式，使用的是間隙鎖解決幻讀問題。

隱藏欄位、undo log 版本鏈

回顧一下 undo log 的版本鏈，對於使用 InnoDB 儲存引擎的表來說，它的聚簇索引記錄中都包含兩個必要的隱藏列。

  1. trx_id：每次一個事務對某條聚簇索引記錄進行改動時，都會把該事務的事務 id 賦值給 trx_id 隱藏列。
  2. roll_pointer：每次對某條聚簇索引記錄進行改動時，都會把舊的版本寫入到 undo log 中，然後這個隱藏列就相當於一個指標，可以透過它來找到該記錄修改前的資訊。

舉例：student 表資料如下。

mysql> SELECT * FROM student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 張三   | 一班    |
+----+--------+--------+
1 row in set (0.07 sec)

假設插入該記錄的事務 id 為 8，那麼此刻該條記錄的示意圖如下所示：

insert undo 只在事務回滾時起作用，當事務提交後，該型別的 undo log 就沒用了，它佔用的 Undo Log Segment 也會被系統回收（也就是該 undo log 佔用的 Undo 頁面連結串列要麼被重用，要麼被釋放)。

假設之後兩個事務 id 分別為 10、20 的事務對這條記錄進行 UPDATE 操作，操作流程如下：

發生時間順序 事務 10 事務 20
1 BEGIN;
2 BEGIN;
3 UPDATE student SET name = "李四" WHERE id = 1;
4 UPDATE student SET name = "王五" WHERE id = 1;
5 COMMIT;
6 UPDATE student SET name = "錢七" WHERE id = 1;
7 UPDATE student SET name = "宋八" WHERE id = 1;
8 COMMIT;

有人可能會想，能不能在兩個事務中交叉更新同一條記錄呢？

答案是不能！因為這種情況，就是一個事務修改了另一個未提交事務修改過的資料，屬於髒寫。

InnoDB 使用鎖來保證不會有髒寫情況的發生，也就是在第一個事務更新了某條記錄後，就會給這條記錄加鎖，另一個事務再次更新時，就需要等待第一個事務提交了，把鎖釋放之後才可以繼續更新。

每次對記錄進行改動，都會記錄一條 undo log，每條 undo log 也都有一個 roll_pointer 屬性（INSERT 操作對應的 undo log 沒有該屬性，因為 INSERT 記錄沒有更早的版本，它自己是起始的版本)，可以將這些 undo log 都連起來，串成一個連結串列：

對該記錄每次更新後，都會將舊值放到一條 undo log 中，就算是該記錄的一箇舊版本，隨著更新次數的增多，所有的版本都會被 roll_pointer 屬性連線成一個連結串列，把這個連結串列稱之為版本鏈，版本鏈的頭節點就是當前記錄最新的值。

另外，每個版本中還包含生成該版本時對應的事務 id。

MVCC 實現原理之 ReadView

MVCC 的實現依賴於：隱藏欄位、undo log 版本鏈、ReadView。

什麼是 ReadView

在 MVCC 機制中，多個事務對同一個行記錄進行更新會產生多個歷史快照，這些歷史快照儲存在 undo log 裡。如果一個事務想要查詢這個行記錄，需要讀取哪個版本的行記錄呢？這時就需要用到 ReadView 了，它解決了行的可見性問題。

ReadView：就是事務在使用 MVCC 機制進行快照讀操作時產生的讀檢視。當事務啟動時，會生成資料庫系統當前的一個快照，InnoDB 為每個事務構造了一個陣列，用來記錄並維護系統當前活躍事務的 ID（"活躍" 指的就是，啟動了但還沒提交)

ReadView 和事務是一對一的關係。

設計思路

使用 READ UNCONNMITTED 隔離級別的事務，由於可以讀到未提交事務修改過的記錄，所以直接讀取的記錄就是最新版本了。此時，不需要使用 MVCC，也就不需要 ReadView。

使用 SERIALIZABLE 隔離級別的事務，InnoDB 規定使用加鎖的方式來訪問記錄。此時，不需要使用 MVCC，也就不需要 ReadView。

使用 READ COMMITTED 和 REPEATABLE READ 隔離級別的事務，都必須保證讀到已經提交了的事務修改過的記錄。假如另一個事務已經修改了記錄但是尚未提交，是不能直接讀取最新版本的記錄的，核心問題就是需要判斷一下版本鏈中的哪個版本是當前事務可見的，這是 ReadView 要解決的主要問題。

ReadView 中主要包含 4 個比較重要的內容，分別如下：

  1. creator_trx_id：建立這個 ReadView 的事務 ID。
  2. trx_ids：表示在生成 ReadView 時，當前系統中活躍的讀寫事務的事務 id 列表。
  3. up_limit_id：活躍的事務中最小的事務 ID。
  4. low_limit_id：表示生成 ReadView 時，系統中應該分配給下一個事務的 id 值。low_limit_id 是當前系統最大的事務 id 值，這裡要注意是系統中的事務 id，需要區別於正在活躍的事務 id。

注意：low_limit_id 並不是 trx_ids 中的最大值，實際上，low_limit_id 不存在於 trx_ids 中。事務 id 是遞增分配的，比如，現在有 id 為 1，2，3 這三個事務，之後 id 為 3 的事務提交了。那麼一個新的讀事務在生成 ReadView 時，trx_ids 就包括 1 和 2，up_limit_id 的值就是 1，low_limit_id 的值就是 4。

舉例：

trx_ids 為 trx2、trx3、trx5 和 trx8 的集合，系統的最大事務 id（low_limit_id）為 trx8 + 1（如果在此之前沒有其他的新增事務），活躍的最小事務 id（up_limit_id）為 trx2。

ReadView 的規則

有了這個 ReadView，這樣在訪問某條記錄時，只需要按照下邊的步驟判斷該記錄在 undo log 版本鏈中的某個版本是否可見：

  • 如果被訪問版本的 trx_id 屬性值等於 ReadView 中的 creator_trx_id 值，意味著當前事務在訪問它自己修改過的記錄，所以該版本可以被當前事務訪問。
  • 如果被訪問版本的 trx_id 屬性值小於 ReadView 中的 up_limit_id 值，表明生成該版本的事務在當前事務生成 ReadView 前已經提交，所以該版本可以被當前事務訪問。
  • 如果被訪問版本的 trx_id 屬性值大於或等於 ReadView 中的 low_limit_id 值，表明生成該版本的事務在當前事務生成 ReadView 後才開啟，所以該版本不可以被當前事務訪問。（否則會出現髒讀）
  • 如果被訪問版本的 trx_id 屬性值在 ReadView 的 up_limit_id 和 low_limit_id 之間，那就需要判斷一下 trx_id 屬性值是不是在 trx_ids 列表中。
    • 如果在，說明建立 ReadView 時生成該版本的事務還是活躍的，該版本不可以被當前事務訪問。
    • 如果不在，說明建立 ReadView 時生成該版本的事務已經被提交，該版本可以被當前事務訪問。

此處被訪問版本，是指 undo log 版本鏈中的版本。

MVCC 整體操作流程

瞭解了這些概念之後，來看下當查詢一條記錄的時候，系統如何透過 MVCC 找到它：

  1. 首先，獲取事務自己的版本號，也就是事務 id；
  2. 獲取（生成）ReadView；
  3. 查詢得到的資料，然後與 ReadView 中的事務版本號進行比較；
  4. 如果不符合 ReadView 規則（當前版本不能被訪問），就需要從 undo log 中獲取歷史快照；
  5. 最後返回符合規則的資料。

如果某個版本的資料對當前事務不可見的話，那就順著 undo log 版本鏈找到下一個版本的資料，繼續按照上邊的步驟判斷可見性，依此類推，直到版本鏈中的最後一個版本。如果最後一個版本也不可見的話，那麼就意味著該條記錄對該事務完全不可見，查詢結果就不包含該記錄。

InnoDB中，MVCC 是透過undo log 版本鏈 + ReadView進行資料讀取：undo log 版本鏈儲存了歷史快照，而 ReadView 規則幫我們判斷當前版本的資料是否可見。

在隔離級別為讀已提交（READ COMMITTED）時，一個事務中的每一次 SELECT 查詢都會重新獲取一次 ReadView。示例：

事務 說明
BEGIN;
SELECT * FROM student WHERE id > 2; 獲取一次 Read View
…
SELECT * FROM student WHERE id > 2; 獲取一次 Read View
COMMIT;

注意，此時同樣的查詢語句都會重新獲取一次 ReadView，這時如果 ReadView 不同，就可能產生不可重複讀或者幻讀的情況，這樣符合Read Committed的規則特點。

當隔離級別為可重複讀（REPEATABLE READ）的時候，就避免了不可重複讀，這是因為一個事務只在第一次 SELECT 的時候會獲取一次 ReadView，而後面所有的 SELECT 都會複用這個 ReadView。示例：

舉例說明

假設現在 student 表中只有一條由事務 id 為 8 的事務插入的一條記錄：

mysql> SELECT * FROM student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 張三   | 一班    |
+----+--------+--------+
1 row in set (0.07 sec)

MVCC 只能在READ COMMITTED和REPEATABLE READ兩個隔離級別下工作。接下來看一下 READ COMMITTED 和 REPEATABLE READ 所謂的生成 ReadView 的時機不同，到底不同在哪裡。

關於不同隔離級別下 ReadView 的事務 id，可以概括如下：

  • 對於 RC 隔離級別：
  • 在一個事務中，每次查詢會建立 id 為 0 的 ReadView。
  • 一旦有修改操作，會切換到以當前事務 id 為 creator_trx_id 的新 ReadView。
  • 對於 RR 隔離級別：
  • 在一個事務中，只有第一次的查詢會建立一個 Read View。
  • 這個 ReadView 的 creator_trx_id 就是當前的事務 id。

RR 要求整個事務的查詢都要一致，所以只有第一次查詢才會生成一個 ReadView。而 RC 可以在同一事務內讀取不同版本的資料，所以每次修改和查詢都會生成新的 ReadView。

READ COMMITTED 隔離級別下

READ COMMITTED：每次讀取資料前都生成一個 ReadView。

現在有兩個事務 id 分別為 10、20 的事務在執行：

# Transaction 10
BEGIN;
UPDATE student SET name = "李四" WHERE id = 1;
UPDATE student SET name = "王五" WHERE id = 1;

# Transaction 20
BEGIN;
# 更新了一些別的表的記錄 (為了分配事務 id)
...

說明：事務執行過程中，只有在第一次真正修改記錄時（比如使用 INSERT、DELETE、UPDATE 語句），才會被分配一個單獨的事務 id，這個事務 id 是遞增的。所以我們才在事務 20 中更新一些別的表的記錄，目的是讓它分配事務 id。

此刻，表 student 中 id 為 1 的記錄得到的 undo log 版本鏈如下所示：

假設現在有一個使用 READ COMMITTED 隔離級別的事務開始執行：

# 使用 READ COMMITTED 隔離級別的事務

BEGIN;
# SELECT1 操作，此時，Transaction 10 和 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的列 name 的值為'張三'

這個 SELECT1 的執行過程如下：

  1. 步驟一：在執行 SELECT 語句時會先生成一個 ReadView，ReadView 的 trx_ids 列表的內容就是 [10, 20]，up_limit_id 為 10，low_limit_id 為 21，creator_trx_id 為 0。
  2. 步驟二：從 undo log 版本鏈中挑選可見的記錄，從圖中看出，最新版本的列 name 的內容是 '王五'，該版本的 trx_id 值為 10，在 trx_ids 列表內（說明 ReadView 生成時，trx_id 為 10 的事務還是活躍的），所以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 步驟三：下一個版本的列 name 的內容是 '李四'，該版本的 trx_id 值也為 10，也在 trx_ids 列表內，所以也不符合要求，繼續跳到下一個版本。
  4. 步驟四：下一個版本的列 name 的內容是 '張三'，該版本的 trx_id 值為 8，小於 ReadView 中的 up_limit_id 值 10，所以這個版本是符合要求的，最後，返回給使用者的版本就是這條列 name 為 '張三' 的記錄。

之後，把 事務 id 為 10 的事務提交一下：

# Transaction 10
BEGIN;

UPDATE student SET name = "李四" WHERE id = 1;
UPDATE student SET name = "王五" WHERE id = 1;

COMMIT;

然後再到事務 id 為 20 的事務中，更新一下表 student 中 id 為 1 的記錄：

# Transaction 20
BEGIN;

# 更新了一些別的表的記錄
...
UPDATE student SET name = "錢七" WHERE id = 1;
UPDATE student SET name = "宋八" WHERE id = 1;

此刻，表 student 中 id 為 1 的記錄的版本鏈就長這樣：

然後，再到剛才使用 READ COMMITTED 隔離級別的事務中繼續查詢這個 id 為 1 的記錄，如下：

# 使用 READ COMMITTED 隔離級別的事務
BEGIN;

# SELECT1 操作，此時，Transaction 10 和 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的列 name 的值為'張三'

# SELECT2 操作，此時，Transaction 10 提交，Transaction 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的列 name 的值為'王五'

這個 SELECT2 的執行過程如下:

  1. 步驟一：在執行 SELECT 語句時會又會單獨生成一個 ReadView，該 ReadView 的 trx_ids 列表的內容就是 [20]，up_limit_id 為 20，low_limit_id 為 21，creator_trx_id 為 0。
  2. 步驟二：從 undo log 版本鏈中挑選可見的記錄，從圖中看出，最新版本的列 name 的內容是 '宋八'，該版本的 trx_id 值為20，在 trx_ids 列表內，所以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 步驟三：下一個版本的列 name 的內容是 '錢七'，該版本的 trx_id 值為 20，也在 trx_ids 列表內，所以也不符合要求，繼續跳到下一個版本。
  4. 步驟四：下一個版本的列 name 的內容是 '王五'，該版本的 trx_id 值為 10，小於 ReadView 中的 up_limit_id 值 20，所以這個版本是符合要求的，最後，返回給使用者的版本就是這條列 name 為 '王五' 的記錄。

以此類推，如果之後事務 id 為 20 的記錄也提交了，再次在使用 READ COMMITED 隔離級別的事務中，查詢表 student 中 id 值為 1 的記錄時，得到的結果就是 '宋八' 了，具體流程我們就不分析了。

強調： 使用 READ COMMITTED 隔離級別的事務，在每次查詢開始時，都會生成一個獨立的 ReadView。

REPEATABLE READ 隔離級別下

REPEATABLE READ：只會在第一次執行查詢語句時生成一個 ReadView，之後的查詢就不會重複生成了，而是複用這個 ReadView。

比如，系統裡有兩個事務 id 分別為 10、20 的事務在執行：

# Transaction 10
BEGIN;
UPDATE student SET name = "李四" WHERE id = 1;
UPDATE student SET name = "王五" WHERE id = 1;

# Transaction 20
BEGIN;
# 更新了一些別的表的記錄
...

此刻，表 student 中 id 為 1 的記錄得到的版本連結串列如下所示：

假設現在有一個使用 REPEATABLE READ 隔離級別的事務開始執行：

# 使用 REPEATABLE READ 隔離級別的事務
BEGIN;

# SELECT1 操作，此時，Transaction 10 和 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的列 name 的值為'張三'

這個 SELECT1 的執行過程如下：

  1. 步驟一：在執行 SELECT 語句時會先生成一個 ReadView，ReadView 的 trx_ids 列表的內容就是 [10, 20]，up_limit_id 為 10，low_limit_id 為 21，creator_trx_id 為 0。
  2. 步驟二：然後從 undo log 版本鏈中挑選可見的記錄，從圖中看出，最新版本的列 name 的內容是 '王五'，該版本的 trx_id 值為 10，在 trx_ids 列表內，所以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 步驟三：下一個版本的列 name 的內容是 '李四'，該版本的 trx_id 值也為 10，也在 trx_ids 列表內，所以也不符合要求，繼續跳到下一個版本。
  4. 步驟四：下一個版本的列 name 的內容是 '張三'，該版本的 trx_id 值為 8，小於 ReadView 中的 up_limit_id 值10，所以這個版本是符合要求的，最後，返回給使用者的版本就是這條列 name 為 '張三 ' 的記錄。

之後，我們把事務 id 為 10 的事務提交一下，就像這樣：

# Transaction 10
BEGIN;

UPDATE student SET name = "李四" WHERE id = 1;
UPDATE student SET name = "王五" WHERE id = 1;

COMMIT;

然後，再到事務 id 為 20 的事務中更新一下表 student 中 id 為 1 的記錄：

# Transaction 20
BEGIN;

# 更新了一些別的表的記錄
...
UPDATE student SET name = "錢七" WHERE id = 1;
UPDATE student SET name = "宋八" WHERE id = 1;

此刻，表 student 中 id 為 1 的記錄的版本鏈長這樣：

然後，再到剛才使用 REPEATABLE READ 隔離級別的事務中繼續查詢這個id 為 1 的記錄，如下：

# 使用 REPEATABLE READ 隔離級別的事務
BEGIN;

# SELECT1 操作，此時，Transaction 10 和 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的列 name 的值為'張三'

# SELECT2 操作，此時，Transaction 10 提交，Transaction 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的列 name 的值仍為'張三'

SELECT2 的執行過程如下:

  1. 步驟一：因為當前事務的隔離級別為 REPEATABLE READ，而之前在執行 SELECT1 時已經生成過 ReadView 了，所以此時直接複用之前的 ReadView，之前的 ReadView 的 trx_ids 列表的內容就是 [10, 20]，up_limit_id 為 10，low_limit_id 為 21，creator_trx_id 為 0。
  2. 步驟二：然後從 undo log 版本鏈中挑選可見的記錄，從圖中可以看出，最新版本的列 name 的內容是 '宋八'，該版本的 trx_id 值為 20，在 trx_ids 列表內，所以不符合可見性要求，根據 roll_pointer 跳到下一個版本。
  3. 步驟三：下一個版本的列 name 的內容是 '錢七'，該版本的 trx_id 值為 20，也在 trx_ids 列表內，所以也不符合要求，繼續跳到下一個版本。
  4. 步驟四：下一個版本的列 name 的內容是 '王五'，該版本的 trx_id 值為 10，而 trx_ids 列表中是包含值為 10 的事務 id 的，所以該版本也不符合要求。同理，下一個列 name 的內容是 '李四' 的版本也不符合要求，繼續跳到下一個版本。
  5. 步驟五：下一個版本的列 name 的內容是 '張三'，該版本的 trx_id 值為 8，小於 ReadView 中的 up_limit_id 值 10，所以這個版本是符合要求的，最後，返回給使用者的版本就是這條列 name 為 '張三' 的記錄。

兩次 SELECT 查詢得到的結果是重複的，記錄的列 name 值都是 '張三'，這就是可重複讀的含義。如果我們之後再把事務 id 為 20 的記錄提交了，然後再到剛才使用 REPEATABLE READ 隔離級別的事務中，繼續查詢這個 id 為 1 的記錄，得到的結果還是 '張三'，具體執行過程大家可以自己分析一下。

如何解決幻讀

接下來說明 InnoDB 是如何解決幻讀的。

假設現在表 student 中只有一條資料，資料內容中，主鍵 id = 1，隱藏的 trx_id = 10，它的 undo log 如下圖所示：

假設現在有事務 A 和事務 B 併發執行，事務 A 的事務 id 為 20，事務 B 的事務 id 為 30。

步驟一：事務 A 開始第一次查詢資料，查詢的 SQL 語句如下。

SELECT * FROM student WHERE id >= 1;

在開始查詢之前，MySQL 會為事務 A 產生一個 ReadView，此時 ReadView 的內容如下：trx_ids = [20, 30]，up_limit_id = 20，low_limit_id = 31，creator_trx_id = 20。

由於此時表 student 中只有一條資料，且符合 WHERE id >= 1 條件，因此會查詢出來。然後根據 ReadView機制，發現該行資料的 trx_id = 10，小於事務 A 的 ReadView 裡 up_limit_id，這表示這條資料是事務 A 開啟之前，其他事務就已經提交了的資料，因此事務 A 可以讀取到。

結論：事務 A 的第一次查詢，能讀取到一條資料，id = 1。

步驟二：接著事務 B，往表 student 中新插入兩條資料，並提交事務。

INSERT INTO student(id, name) VALUES(2, '李四');
INSERT INTO student(id, name) VALUES(3, '王五');

此時，表 student 中就有三條資料了，對應的 undo log 如下圖所示：

步驟三：接著事務 A 開啟第二次查詢，根據可重複讀隔離級別的規則，此時事務 A 並不會再重新生成 ReadView。此時表 student 中的 3 條資料都滿足 WHERE id >= 1 的條件，因此會先查出來。然後根據 ReadView 機制，判斷每條資料是不是都可以被事務 A 看到。

  1. 首先 id = 1 的這條資料，前面已經說過了，可以被事務 A 看到。
  2. 然後是 id = 2 的資料，它的 trx_id = 30，此時事務 A 發現，這個值處於 up_limit_id 和 low_limit_id 之間，因此還需要再判斷 30 是否處於 trx_ids 陣列內。由於事務 A 的 trx_ids = [20, 30]，因此在陣列內，這表示 id = 2 的這條資料是與事務 A 在同一時刻啟動的其他事務提交的，所以這條資料不能讓事務 A 看到。
  3. 同理，id = 3 的這條資料，trx_id 也為 30，因此也不能被事務 A 看見。

如下圖所示：

結論：最終事務 A 的第二次查詢，只能查詢出 id = 1 的這條資料，這和事務 A 的第一次查詢的結果是一樣的，因此沒有出現幻讀現象，所以說在 MySQL 的可重複讀隔離級別下，不存在幻讀問題。

總結

這裡介紹了 MVCC 在 READ COMMITTD、REPEATABLE READ 這兩種隔離級別的事務，在執行快照讀操作時訪問記錄的版本鏈的過程。這樣使不同事務的讀-寫、寫-讀操作併發執行，從而提升系統效能

核心點在於 ReadView 的原理，READ COMMITTD、REPEATABLE READ 這兩個隔離級別的一個很大不同就是生成 ReadView 的時機不同：

  • READ COMMITTD 在每一次進行普通 SELECT 操作前，都會生成一個ReadView。
  • REPEATABLE READ 只在第一次進行普通 SELECT 操作前生成一個 ReadView，之後的查詢操作都重複使用這個 ReadView。

說明：之前說執行 DELETE 語句或者更新主鍵的 UPDATE 語句，並不會立即把對應的記錄完全從頁面中刪除，而是執行一個所謂的delete mark操作（標記 0 -> 1），相當於只是對記錄打上了一個刪除標誌位，這主要就是為 MVCC 服務的。另外後面回滾也可能用到這個 delete mark。

透過 MVCC 可以解決：

  • 讀寫之間阻塞的問題：透過 MVCC 可以讓讀寫互相不阻塞，即讀不阻塞寫，寫不阻塞讀，這樣就可以提升事務併發處理能力。
  • 降低了死鎖的機率：這是因為 MVCC 採用了樂觀鎖的方式，讀取資料時並不需要加鎖，對於寫操作，也只鎖定必要的行。
  • 解決快照讀的問題：當查詢資料庫在某個時間點的快照時，只能看到這個時間點之前事務提交更新的結果，而不能看到這個時間點之後事務提交的更新結果。

原文連結

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql-advanced.md

相關文章