引言

生產(chǎn)環(huán)境中經(jīng)常會遇到鎖等待與死鎖相關(guān)的問題，這類問題通常比較緊急，而且由于鎖相關(guān)影響因素較多，因此分析難度較大。

本文從最簡單的一類鎖等待開始，即并發(fā) update 導(dǎo)致鎖等待。

介紹

如果相同的 update 同時執(zhí)行會發(fā)生什么呢？

實際上會發(fā)生鎖等待，生產(chǎn)環(huán)境中就遇到過這種案例，并發(fā) update 導(dǎo)致鎖等待。

死鎖建立在鎖等待的基礎(chǔ)上，因此需要先理解鎖等待的機制與分析思路。本文通過一個最簡單的并發(fā) update 介紹鎖等待的分析方法。

模擬

首先，聲明事務(wù)隔離級別為 RR（REPEATABLE-READ）。

流程

兩個 session 分別在開啟事務(wù)的前提下執(zhí)行相同的 update 語句導(dǎo)致鎖等待。

其中超時時間由系統(tǒng)參數(shù) innodb_lock_wait_timeout 控制，默認(rèn)值 50s，當(dāng)前值 120s。

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                        120 |
+----------------------------+
1 row in set (0.00 sec)

根據(jù)官方文檔，innodb_lock_wait_timeout 參數(shù)控制 InnoDB 存儲引擎中事務(wù)的行鎖等待時間，超時回滾。

innodb_lock_wait_timeout

The length of time in seconds an InnoDB transaction waits for a row lock before giving up.

MySQL 5.7 中查看事務(wù)加鎖的情況有兩種方式：

使用 information_schema 數(shù)據(jù)庫中的表獲取鎖信息；
使用 SHOW ENGINE INNODB STATUS 獲取鎖信息。

下面分別使用這兩種方式分析當(dāng)前事務(wù)加鎖的情況。

innodb_trx

information_schema.innodb_trx 表中存儲了 InnoDB 存儲引擎當(dāng)前正在執(zhí)行的事務(wù)信息。

其中：

TRX_TABLES_LOCKED 字段表示事務(wù)當(dāng)前執(zhí)行 SQL 持有行鎖涉及到的表的數(shù)量，注意不包括表鎖，因此盡管部分行被鎖定，但通常不影響其他事務(wù)的讀寫操作；

TRX_TABLES_LOCKED

The number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.)

TRX_ROWS_LOCKED 字段表示被事務(wù)鎖定的行數(shù)，其中可能包括被標(biāo)記為刪除但實際上未物理刪除的數(shù)據(jù)行。

TRX_ROWS_LOCKED

The approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction.

結(jié)果表明當(dāng)前有兩個未提交事務(wù)，不同點是其中一個執(zhí)行中，一個鎖等待，相同點是都在內(nèi)存中創(chuàng)建了兩個鎖結(jié)構(gòu)，而且其中一個是行鎖。

mysql> select * from information_schema.innodb_trx\\G
*************************** 1. row ***************************
                    trx_id: 11309021
                 trx_state: LOCK WAIT
               trx_started: 2022-11-22 17:40:16
     trx_requested_lock_id: 11309021:190:3:2
          trx_wait_started: 2022-11-22 17:42:25
                trx_weight: 2
       trx_mysql_thread_id: 1135
                 trx_query: update t2 set name='d' where id=1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1  # 1個表上有行鎖
          trx_lock_structs: 2  # 內(nèi)存中2個鎖結(jié)構(gòu)
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1  # 1行數(shù)據(jù)被鎖定
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 11309020
                 trx_state: RUNNING
               trx_started: 2022-11-22 17:40:09
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 1134
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1  # 1個表上有行鎖
          trx_lock_structs: 2  # 內(nèi)存中2個鎖結(jié)構(gòu)
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1  # 1行數(shù)據(jù)被鎖定
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

從中可以看到與鎖相關(guān)的事務(wù)，但是無法看到鎖的具體類型。

innodb_locks

information_schema.innodb_locks 表中主要包括以下兩方面的鎖信息：

如果一個事務(wù)想要獲取某個鎖但未獲取到，則記錄該鎖信息，即等鎖事務(wù)；
如果一個事務(wù)獲取到了某個鎖，但是這個鎖阻塞了其他事務(wù)，則記錄該鎖信息，即持鎖事務(wù)。

The INNODB_LOCKS table provides information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.

注意只有當(dāng)事務(wù)因為獲取不到鎖而被阻塞即發(fā)生鎖等待時 innodb_locks 表中才會有記錄，因此當(dāng)只有一個事務(wù)時，無法查看該事務(wù)所加的鎖信息。

如下所示，鎖超時之后查詢 innodb_locks 表，結(jié)果為空。

mysql> select * from information_schema.innodb_locks\\G
Empty set, 1 warning (0.00 sec)

如下所示，鎖超時之前查詢 innodb_locks 表，結(jié)果表明所有事務(wù)共請求了兩次 t2 表的主鍵索引值為 1 的記錄上的 X 型行鎖。

mysql> select * from information_schema.innodb_locks \\G
*************************** 1. row ***************************
    lock_id: 11309021:190:3:2
lock_trx_id: 11309021
  lock_mode: X  # 排它鎖
  lock_type: RECORD  # 行鎖
 lock_table: `test_zk`.`t2`  # 表名
 lock_index: PRIMARY  # 主鍵索引
 lock_space: 190
  lock_page: 3
   lock_rec: 2
  lock_data: 1  # 主鍵值為1
*************************** 2. row ***************************
    lock_id: 11309020:190:3:2
lock_trx_id: 11309020
  lock_mode: X  # 排它鎖
  lock_type: RECORD  # 行鎖
 lock_table: `test_zk`.`t2`  # 表名
 lock_index: PRIMARY  # 主鍵索引
 lock_space: 190
  lock_page: 3
   lock_rec: 2
  lock_data: 1  # 主鍵值為1
2 rows in set, 1 warning (0.00 sec)

從中可以看到具體請求的鎖的類型，但是無法區(qū)分等鎖事務(wù)與持鎖事務(wù)。

innodb_lock_waits

information_schema.innodb_lock_waits 表中記錄每個阻塞的事務(wù)是因為獲取不到哪個事務(wù)持有的鎖而阻塞。

結(jié)果表明 11309020 事務(wù)阻塞了 11309021 事務(wù)。

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 11309021          | 11309021:190:3:2  | 11309020        | 11309020:190:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

從中可以看到事務(wù)之間鎖的依賴關(guān)系，但是無法查看到持鎖 SQL，因此通常需要將該表與其他表做關(guān)聯(lián)查詢。

關(guān)聯(lián)查詢

如下所示，可以在發(fā)生鎖等待的現(xiàn)場關(guān)聯(lián)查詢 information_schema 數(shù)據(jù)庫中的多張表表分析持鎖與等鎖的事務(wù)與 SQL。

mysql> SELECT r.trx_id waiting_trx_id,  
    ->        r.trx_mysql_thread_id waiting_thread,
    ->        r.trx_query waiting_query,
    ->        b.trx_id blocking_trx_id, 
    ->        b.trx_mysql_thread_id blocking_thread,
    ->        b.trx_query blocking_query
    ->    FROM       information_schema.innodb_lock_waits w
    ->    INNER JOIN information_schema.innodb_trx b  ON  
    ->     b.trx_id = w.blocking_trx_id
    ->   INNER JOIN information_schema.innodb_trx r  ON  
    ->     r.trx_id = w.requesting_trx_id;
*************************** 1. row ***************************
 waiting_trx_id: 11309021
 waiting_thread: 1135
  waiting_query: update t2 set name='d' where id=1
blocking_trx_id: 11309020
blocking_thread: 1134
 blocking_query: NULL
1 row in set, 1 warning (0.00 sec)

注意其中從 information_schema.innodb_trx 表中查詢到的 blocking_query 即持鎖的 SQL 為空。

實際上，可以從 performance_schema.events_statements_current 表中查詢到持鎖 SQL。

mysql> select 
    ->     wt.thread_id waiting_thread_id,
    ->     wt.processlist_id waiting_processlist_id,
    ->     wt.processlist_time waiting_time,
    ->     wt.processlist_info waiting_query,
    ->     bt.thread_id blocking_thread_id,
    ->     bt.processlist_id blocking_processlist_id,
    ->     bt.processlist_time blocking_time,
    ->     c.sql_text blocking_query,
    ->     concat('kill ',bt.processlist_id, ';') sql_kill_blocking_connection
    -> from information_schema.innodb_lock_waits l join information_schema.innodb_trx b
    ->     on b.trx_id = l.blocking_trx_id
    -> join information_schema.innodb_trx w
    ->     on w.trx_id = l.requesting_trx_id
    -> join performance_schema.threads wt
    ->     on w.trx_mysql_thread_id=wt.processlist_id
    -> join performance_schema.threads bt
    ->     on b.trx_mysql_thread_id=bt.processlist_id
    -> join performance_schema.events_statements_current c
    ->     on bt.thread_id=c.thread_id \\G
*************************** 1. row ***************************
           waiting_thread_id: 1178
      waiting_processlist_id: 1135
                waiting_time: 61
               waiting_query: update t2 set name='d' where id=1
          blocking_thread_id: 1177
     blocking_processlist_id: 1134
               blocking_time: 76
              blocking_query: update t2 set name='d' where id=1
sql_kill_blocking_connection: kill 1134;
1 row in set, 1 warning (0.00 sec)

INNODB STATUS

SHOW ENGINE INNODB STATUS 命令用于查詢 InnoDB 存儲引擎標(biāo)準(zhǔn)監(jiān)控的狀態(tài)信息。

SHOW ENGINE INNODB STATUS displays extensive information from the standard InnoDB Monitor about the state of the InnoDB storage engine.

其中 TRANSACTIONS 部分的信息可用于分析鎖等待與死鎖。

TRANSACTIONS

If this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.

結(jié)果如下所示，TRANSACTIONS 部分包括兩個未提交事務(wù)。

mysql> show engine innodb status \\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-11-22 17:42:50 0x7ff4df900700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
...
------------
TRANSACTIONS
------------
# 下一個待分配的事務(wù)id信息
Trx id counter 11309022
# 清除舊MVCC行時使用的事務(wù)ID，該事務(wù)與當(dāng)前事務(wù)之間的老版本數(shù)據(jù)未被清除
Purge done for trx's n:o < 11309020 undo n:o < 0 state: running but idle
# 每個回滾段都有一個History鏈表，這些鏈表的總長度等于64
History list length 64
# 各個事務(wù)的具體信息
LIST OF TRANSACTIONS FOR EACH SESSION:
# not started 空閑事務(wù)，表示事務(wù)已經(jīng)提交并且沒有再發(fā)起影響事務(wù)的語句
---TRANSACTION 422165848318464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422165848316640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
# 事務(wù)ID等于11309021的事務(wù)，處于活躍狀態(tài)154秒，正在使用索引讀取數(shù)據(jù)行
---TRANSACTION 11309021, ACTIVE 154 sec starting index read
# 事務(wù)11309021正在使用1張表，有1張表有鎖
mysql tables in use 1, locked 1
# 等鎖，鎖鏈表長度為2，占用內(nèi)存1136字節(jié)，其中1把行鎖
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1135, OS thread handle 140689506727680, query id 13803596 127.0.0.1 admin updating
# 事務(wù)運行中SQL語句
update t2 set name='d' where id=1
# 鎖等待發(fā)生時在等待的鎖信息，已等待25秒
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
# 等鎖，在等待主鍵索引（index PRIMARY）上的行級別X鎖（RECORD LOCK），沒有間隙鎖
RECORD LOCKS space id 190 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t2` trx id 11309021 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
# 內(nèi)存地址，用于調(diào)試
 0: len 4; hex 80000001; asc     ;;  # 聚簇索引的值，80000001 表示主鍵值為1
 1: len 6; hex 000000ac8fdc; asc       ;;  # 事務(wù)ID，對應(yīng)十進(jìn)制 11309020
 2: len 7; hex 730000002a0b0d; asc s   *  ;;  # unod記錄
 3: len 1; hex 64; asc d;;  # 非主鍵字段的值，'d'

------------------
# 持鎖，事務(wù)ID等于11309021的事務(wù)對t2表加了表級別的意向排它鎖
TABLE LOCK table `test_zk`.`t2` trx id 11309021 lock mode IX
# 等鎖，在等待主鍵索引（index PRIMARY）上的行級別X鎖（RECORD LOCK），沒有間隙鎖
RECORD LOCKS space id 190 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t2` trx id 11309021 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000ac8fdc; asc       ;;
 2: len 7; hex 730000002a0b0d; asc s   *  ;;
 3: len 1; hex 64; asc d;;

# 事務(wù)ID等于11309020的事務(wù)，處于活躍狀態(tài)161秒
---TRANSACTION 11309020, ACTIVE 161 sec
# 該事務(wù)有2個鎖結(jié)構(gòu)，其中1個行鎖
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 1134, OS thread handle 140689373869824, query id 13803593 127.0.0.1 admin
# 持鎖，事務(wù)ID等于11309020的事務(wù)對t2表加了表級別的意向排它鎖，IX鎖之間兼容
TABLE LOCK table `test_zk`.`t2` trx id 11309020 lock mode IX
# 持鎖，主鍵索引（index PRIMARY）上的行級別X鎖（RECORD LOCK），沒有間隙鎖
RECORD LOCKS space id 190 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t2` trx id 11309020 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;  # 80000001 表示主鍵值為1
 1: len 6; hex 000000ac8fdc; asc       ;;
 2: len 7; hex 730000002a0b0d; asc s   *  ;;
 3: len 1; hex 64; asc d;;

...
----------------------------
END OF INNODB MONITOR OUTPUT
============================

從中可以看到事務(wù)持鎖與等鎖的詳細(xì)信息，但是無法看到持鎖的 SQL。

由于信息不全，因此 SHOW ENGINE INNODB STATUS 更適合分析死鎖，因為死鎖已經(jīng)沒有了現(xiàn)場，而鎖等待通常現(xiàn)場還在，可以直接查看 information_schema 數(shù)據(jù)庫中的表。

主要信息如下所示。

11309021 事務(wù)持有 t2 表的表級別意向排它鎖，等待主鍵索引上的行級別 X 鎖（RECORD LOCK），沒有間隙鎖；

---TRANSACTION 11309021, ACTIVE 154 sec starting index read
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
update t2 set name='d' where id=1
TABLE LOCK table `test_zk`.`t2` trx id 11309021 lock mode IX
RECORD LOCKS space id 190 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t2` trx id 11309021 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

11309020 事務(wù)分別持有 t2 表的表級別意向排它鎖與主鍵索引上的行級別 X 鎖（RECORD LOCK），沒有間隙鎖。

---TRANSACTION 11309020, ACTIVE 161 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
TABLE LOCK table `test_zk`.`t2` trx id 11309020 lock mode IX
RECORD LOCKS space id 190 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t2` trx id 11309020 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

因此，鎖等待分析的結(jié)論如下所示：

update 操作需要獲取兩把鎖，包括表級別的意向排它鎖與行級別 X 鎖（RECORD LOCK）；
并發(fā) update 時由于意向鎖之間兼容，而行級 X 鎖之間沖突，導(dǎo)致發(fā)生鎖等待。

原理

鎖

首先為什么需要鎖？

鎖本質(zhì)上是一種并發(fā)控制手段，用于解決事務(wù)在并發(fā)執(zhí)行時可能引發(fā)的一致性問題。

并發(fā)事務(wù)訪問相同數(shù)據(jù)基本上可以分為以下三種情況：

讀-讀，相互不影響，因此允許；
寫-寫，會導(dǎo)致臟寫，因此不允許，通過給記錄加鎖實現(xiàn)；
讀-寫或?qū)?讀，會導(dǎo)致臟讀、不可重復(fù)讀、幻讀。解決方案主要分兩種：
- MVCC 多版本并發(fā)控制，保存符合條件的記錄的多個版本，寫操作針對最新版本，讀操作針對歷史版本。因此讀-寫不沖突；
- 讀寫操作均加鎖，每次都需要讀取最新版本數(shù)據(jù)，讀寫操作均采用加鎖方式，因此讀-寫沖突。

而 InnoDB 存儲引擎支持事務(wù)與行鎖，并實現(xiàn)了基于 MVCC 的事務(wù)并發(fā)處理機制。

鎖的類型

如下所示，根據(jù)不同的維度，可以將鎖分為不同的類型。

其中：

根據(jù)加鎖機制，實際上就是鎖的實現(xiàn)方式，可以將鎖分為以下兩類：
- 樂觀鎖，先加鎖后訪問，傳統(tǒng)的關(guān)系型數(shù)據(jù)庫使用這種鎖機制；
- 悲觀鎖，先訪問后加鎖，常見實現(xiàn)如 CAS、版本號控制。
根據(jù)兼容性，可以將鎖分為以下兩類：
- 共享鎖，Shared-Lock，S 鎖，讀鎖；
- 排它鎖，Exclusive-Lock，X 鎖，寫鎖。
根據(jù)鎖的粒度，可以將鎖分為以下三類：
- 表鎖，Table-Lock，MyISAM 存儲引擎僅支持表鎖；
- 頁鎖，Page-Lock，使用相對較少；
- 行鎖，Row-Lock，InnoDB 存儲引擎也支持行鎖。
根據(jù)鎖的模式，可以將鎖分為以下幾種：
- 行鎖，Record Lock，鎖定一條記錄；
- 間隙鎖，Gap Lock，鎖定一個范圍，不包括記錄本身；
- Next-key Lock，鎖定一個范圍的記錄包括記錄本身，Next-key Lock = Record Lock + Gap Lock；
- 插入意向鎖，Insert Intention Lock，用于行鎖和表鎖共存。

具體各種類型鎖的介紹將在本系列后續(xù)文章中逐一介紹。

這里簡單介紹下行鎖，行鎖鎖定的是什么，是索引還是數(shù)據(jù)？

實際上 InnoDB 行鎖是通過給索引項加鎖實現(xiàn)的 ，如果沒有索引，InnoDB 會通過隱藏的聚簇索引來對記錄加鎖。

因此如果不通過索引條件檢索數(shù)據(jù)，InnoDB 將對表中所有數(shù)據(jù)加鎖，實際效果與表鎖一樣。

鎖的結(jié)構(gòu)

對一條記錄加鎖的本質(zhì)是在內(nèi)存中創(chuàng)建一個鎖結(jié)構(gòu)與之關(guān)聯(lián)（隱式鎖除外）。如果有多個鎖，保存在鏈表結(jié)構(gòu)中。

簡化后的鎖結(jié)構(gòu)示意圖如下所示，主要包括 trx 信息與 is_waiting 屬性，分別表示鎖所在的事務(wù)信息與當(dāng)前事務(wù)是否在等待，然后將鎖結(jié)構(gòu)與行記錄關(guān)聯(lián)。

img

假設(shè)事務(wù) T1 改動了這條記錄，就生成了一個鎖結(jié)構(gòu)與該記錄關(guān)聯(lián)，因此 is_waiting 屬性為 false，表示加鎖成功。

事務(wù) T1 提交之前，另一個事務(wù) T2 也想改動這條記錄，先去查看有沒有鎖結(jié)構(gòu)與這條記錄關(guān)聯(lián)，發(fā)現(xiàn)有一個鎖結(jié)構(gòu)與之關(guān)聯(lián)后，也生成了一個鎖結(jié)構(gòu)與該記錄關(guān)聯(lián)，不過 is_waiting 屬性為 true，表示鎖等待，直到 T1 提交后釋放鎖。

img

更詳細(xì)的 InnoDB 存儲引擎中的事務(wù)鎖結(jié)構(gòu)如下所示。

img

其中：

鎖所在的事務(wù)信息：無論表鎖還是行鎖，都是在事務(wù)執(zhí)行過程中給生成的，因此需要加載是哪個事務(wù)生成了這個鎖結(jié)構(gòu)；
索引信息：對于行鎖需要記錄加鎖的記錄屬于哪個索引，原因是行鎖是給索引項加鎖；
表鎖/行鎖信息：
- 對于表鎖，記載這是對哪個表加的鎖，還有其他的一些信息；
- 對于行鎖，主要記載三個信息，包括 Space ID 記錄所在表空間、Page Number 記錄所在頁號、 n_bit 表示對哪一條記錄加了鎖，對于行鎖，一條記錄對應(yīng)一個比特位；
type_node：32 個比特位，記載三部分信息，包括 lock_mode 鎖的模式、lock_type 鎖的類型和 rec_lock_type 行鎖的具體類型：
- lock_mode，鎖的模式，占用低 4 位，十進(jìn)制的 0、1、2、3、4 分別表示表級共享意向鎖 IS、表級排它意向鎖 IX、行級共享鎖 LOCK_S、行級排它鎖 LOCK_X、表級 LOCK_AUTO_INC 自增鎖；
- lock_type，鎖的類型，占用第 5～8 位，不過現(xiàn)階段只有第 5 位和第 6 位被使用。其中十進(jìn)制的 16 和 32 分別表示表級鎖與行級鎖；
- rec_lock_type，行鎖的具體類型，十進(jìn)制的 0、512、1024、2048 分別表示 LOCK_ORDINARY 即 Next-key Lock、LOCK_GAP 即間隙鎖、LOCK_REC_NOT_GAP 即正經(jīng)記錄鎖、LOCK_INSERT_INTENTION 即插入意向鎖。此外，十進(jìn)制的 256 表示 LOCK_WAIT，因此當(dāng)?shù)?9 個比特位為 0 與 1 分別表示當(dāng)前事務(wù)獲取到鎖與未獲取到鎖處于等待狀態(tài)。
其他信息：為了更好的管理系統(tǒng)運行過程中生成的各種鎖結(jié)構(gòu)而設(shè)計了各種哈希表和鏈表，可以先忽略；
一堆比特位：比特位的數(shù)量是由上面提到的 n_bits 屬性表示，頁面中的每條記錄在記錄頭信息中都包含一個 heap_no 屬性，偽記錄 Infimum 的 heap_no 值為0，Supremum 的 heap_no 值為 1，之后每插入一條記錄，heap_no 值就增 1。鎖結(jié)構(gòu)最后的一堆比特位就對應(yīng)著一個頁面中的記錄，一個比特位映射一個 heap_no。

文中案例update t2 set name='d' where id=1;這條 update 語句執(zhí)行時鎖結(jié)構(gòu)中信息如下所示。

---TRANSACTION 11309020, ACTIVE 161 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
TABLE LOCK table `test_zk`.`t2` trx id 11309020 lock mode IX
RECORD LOCKS space id 190 page no 3 n bits 80 index PRIMARY of table `test_zk`.`t2` trx id 11309020 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

其中：

Space ID = 190、Page Number = 3、n_bits = 80、index = PRIMARY
type_mode = LOCK_X | LOCK_REC | LOCK_REC_NOT_GAP = 3 | 32 | 1024
heap no 2，表明表中的第一行記錄被鎖定；
n_fields 4，含義還不確定。

鎖等待時顯示 2 lock struct(s)，表示 trx->trx_locks 鎖鏈表的長度為2，每個鏈表節(jié)點代表該事務(wù)持有的一個鎖結(jié)構(gòu)，包括表鎖，記錄鎖以及自增鎖等。

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

其中：

LOCK WAIT 2 lock struct(s) 表示事務(wù)正在等待鎖，其中鎖鏈表的長度為 2，并非表示在等待兩把鎖；
2 locks 表示 IX 鎖和 lock_mode X locks rec but not gap 即 Record Lock。

小技巧

鎖等待分析

分析鎖等待時，建議在發(fā)生鎖等待的現(xiàn)場關(guān)聯(lián)查詢分析持鎖與等鎖的事務(wù)與 SQL，注意如果鎖等待已超時，就看不到了，SQL 如下所示。

select 
    wt.thread_id waiting_thread_id,
    wt.processlist_id waiting_processlist_id,
    wt.processlist_time waiting_time,
    wt.processlist_info waiting_query,
    bt.thread_id blocking_thread_id,
    bt.processlist_id blocking_processlist_id,
    bt.processlist_time blocking_time,
    c.sql_text blocking_query,
    concat('kill ',bt.processlist_id, ';') sql_kill_blocking_connection
from information_schema.innodb_lock_waits l join information_schema.innodb_trx b
    on b.trx_id = l.blocking_trx_id
join information_schema.innodb_trx w
    on w.trx_id = l.requesting_trx_id
join performance_schema.threads wt
    on w.trx_mysql_thread_id=wt.processlist_id
join performance_schema.threads bt
    on b.trx_mysql_thread_id=bt.processlist_id
join performance_schema.events_statements_current c
    on bt.thread_id=c.thread_id \\G

PS.data_locks

從 MySQL 8.0.1 版本開始，可以通過 performance_schema.data_locks 表查看 SQL 執(zhí)行過程中需要獲取的鎖。

select * from performance_schema.data_locks \\G

上文中提到，只有當(dāng)事務(wù)因為獲取不到鎖而被阻塞即發(fā)生鎖等待時 information_schema.innodb_locks 表中才會有記錄，而 performance_schema.data_locks 表中即使事務(wù)沒有被阻塞，也可以看到事務(wù)持有的鎖，這一點對于鎖分析非常有用。

查看 update 這條 SQL 執(zhí)行需要獲取的鎖。

mysql> select * from performance_schema.data_locks \\G
Empty set (0.00 sec)

mysql> update t2 set name='d' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from performance_schema.data_locks \\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1070:140122972540608
ENGINE_TRANSACTION_ID: 2032017
            THREAD_ID: 64
             EVENT_ID: 26
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t2
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140122972540608
            LOCK_TYPE: TABLE  # 表級鎖
            LOCK_MODE: IX  # X 型意向鎖
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:8:4:2:140122972537552
ENGINE_TRANSACTION_ID: 2032017
            THREAD_ID: 64
             EVENT_ID: 26
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t2
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY  # 主鍵索引
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD  # 行級鎖
            LOCK_MODE: X,REC_NOT_GAP  # X 型記錄鎖
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1  # 鎖定主鍵值為1的記錄
2 rows in set (0.00 sec)

結(jié)果顯示 update 操作需要獲取兩把鎖，包括表級別的意向排它鎖與行級別 X 鎖（RECORD LOCK），與上文中分析結(jié)論一致。

上文中查看 INNODB_LOCKS 與 INNODB_LOCK_WAITS 表中均有告警 1 warning，如下所示查看告警。

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                  |
+---------+------+------------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                       |
+---------+------+-----------------------------------------------------------------------------------------------+
| Warning | 1681 | 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release. |
+---------+------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

實際上，這兩張表在 5.7.14 版本中已過時，8.0.1 版本中已刪除。

This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.

其中：

INFORMATION_SCHEMA.INNODB_LOCKS 被 performance_schema.data_locks 代替；
INFORMATION_SCHEMA.INNODB_LOCK_WAITS 被 data_lock_waitsdata_lock_waits 代替。

結(jié)論

鎖本質(zhì)是是一種并發(fā)控制手段，用于解決事務(wù)在并發(fā)執(zhí)行時可能引發(fā)的一致性問題。

寫-寫操作會導(dǎo)致臟寫，即一個事務(wù)覆蓋另一個事務(wù)未提交的更改，因此需要給寫操作加寫鎖。

InnoDB 存儲引擎支持事務(wù)與行鎖，其中行鎖是給索引項加鎖。

鎖結(jié)構(gòu)中主要包括 trx 信息與 is_waiting 屬性，分別表示鎖所在的事務(wù)信息與當(dāng)前事務(wù)是否在等待，然后將鎖結(jié)構(gòu)與行記錄關(guān)聯(lián)。

InnoDB 中鎖的實現(xiàn)是悲觀鎖，先加鎖后訪問，因此無論是否獲取到鎖，都會在內(nèi)存中生成對應(yīng)的鎖結(jié)構(gòu)，其中 is_waiting 為 false 表示持鎖，為 true 表示等鎖。

因此，并發(fā) update 會導(dǎo)致鎖等待，分析鎖等待的方法主要包括：

使用 information_schema 數(shù)據(jù)庫中的表獲取鎖信息，不過要求鎖等待現(xiàn)場查看；
使用 SHOW ENGINE INNODB STATUS 獲取鎖信息，不過信息不全，因此適合死鎖分析。

從 MySQL 8.0.1 版本開始，可以通過 performance_schema.data_locks 表查看 SQL 執(zhí)行過程中需要獲取的鎖。即使事務(wù)沒有被阻塞，也可以看到事務(wù)持有的鎖，這一點對于鎖分析非常有用。

通過查詢 performance_schema.data_locks 表，可以明確的看到 update 操作需要獲取兩把鎖，包括表級別的意向排它鎖與行級別 X 鎖（RECORD LOCK）。

待辦

鎖的類型
鎖的信息，n_bits、n_fields
死鎖分析
事務(wù)隔離級別、MVCC 與鎖的關(guān)系

聲明：本文內(nèi)容及配圖由入駐作者撰寫或者入駐合作網(wǎng)站授權(quán)轉(zhuǎn)載。文章觀點僅代表作者本人，不代表電子發(fā)燒友網(wǎng)立場。文章及其配圖僅供工程師學(xué)習(xí)之用，如有內(nèi)容侵權(quán)或者其他違規(guī)問題，請聯(lián)系本站處理。舉報投訴