MySQL并發update導致鎖等待介紹
引言
生產環境中經常會遇到鎖等待與死鎖相關的問題，這類問題通常比較緊急，而且由于鎖相關影響因素較多，因此分析難度較大。
本文從最簡單的一類鎖等待開始，即并發 update 導致鎖等待。
介紹
如果相同的 update 同時執行會發生什么呢？
實際上會發生鎖等待，生產環境中就遇到過這種案例，并發 update 導致鎖等待。
死鎖建立在鎖等待的基礎上，因此需要先理解鎖等待的機制與分析思路。本文通過一個最簡單的并發 update 介紹鎖等待的分析方法。
模擬
首先，聲明事務隔離級別為 RR（REPEATABLE-READ）。
流程
兩個 session 分別在開啟事務的前提下執行相同的 update 語句導致鎖等待。
其中超時時間由系統參數 innodb_lock_wait_timeout 控制，默認值 50s，當前值 120s。
mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 120 |
+----------------------------+
1 row in set (0.00 sec)
根據官方文檔，innodb_lock_wait_timeout 參數控制 InnoDB 存儲引擎中事務的行鎖等待時間，超時回滾。
innodb_lock_wait_timeoutThe length of time in seconds an InnoDB transaction waits for a row lock before giving up.
MySQL 5.7 中查看事務加鎖的情況有兩種方式：
- 使用 information_schema 數據庫中的表獲取鎖信息；
- 使用 SHOW ENGINE INNODB STATUS 獲取鎖信息。
下面分別使用這兩種方式分析當前事務加鎖的情況。
innodb_trx
information_schema.innodb_trx 表中存儲了 InnoDB 存儲引擎當前正在執行的事務信息。
其中：
- TRX_TABLES_LOCKED 字段表示事務當前執行 SQL 持有行鎖涉及到的表的數量，注意不包括表鎖，因此盡管部分行被鎖定，但通常不影響其他事務的讀寫操作；
TRX_TABLES_LOCKEDThe number of
InnoDBtables 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 字段表示被事務鎖定的行數，其中可能包括被標記為刪除但實際上未物理刪除的數據行。
TRX_ROWS_LOCKEDThe 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.
結果表明當前有兩個未提交事務，不同點是其中一個執行中，一個鎖等待，相同點是都在內存中創建了兩個鎖結構，而且其中一個是行鎖。
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 # 內存中2個鎖結構
trx_lock_memory_bytes: 1136
trx_rows_locked: 1 # 1行數據被鎖定
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 # 內存中2個鎖結構
trx_lock_memory_bytes: 1136
trx_rows_locked: 1 # 1行數據被鎖定
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)
從中可以看到與鎖相關的事務，但是無法看到鎖的具體類型。
innodb_locks
information_schema.innodb_locks 表中主要包括以下兩方面的鎖信息：
- 如果一個事務想要獲取某個鎖但未獲取到，則記錄該鎖信息，即等鎖事務；
- 如果一個事務獲取到了某個鎖，但是這個鎖阻塞了其他事務，則記錄該鎖信息，即持鎖事務。
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.
注意只有當事務因為獲取不到鎖而被阻塞即發生鎖等待時 innodb_locks 表中才會有記錄，因此當只有一個事務時，無法查看該事務所加的鎖信息。
如下所示，鎖超時之后查詢 innodb_locks 表，結果為空。
mysql> select * from information_schema.innodb_locks\\G
Empty set, 1 warning (0.00 sec)
如下所示，鎖超時之前查詢 innodb_locks 表，結果表明所有事務共請求了兩次 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)
從中可以看到具體請求的鎖的類型，但是無法區分等鎖事務與持鎖事務。
innodb_lock_waits
information_schema.innodb_lock_waits 表中記錄每個阻塞的事務是因為獲取不到哪個事務持有的鎖而阻塞。
結果表明 11309020 事務阻塞了 11309021 事務。
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)
從中可以看到事務之間鎖的依賴關系，但是無法查看到持鎖 SQL，因此通常需要將該表與其他表做關聯查詢。
關聯查詢
如下所示，可以在發生鎖等待的現場關聯查詢 information_schema 數據庫中的多張表表分析持鎖與等鎖的事務與 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 存儲引擎標準監控的狀態信息。
SHOW ENGINE INNODB STATUS displays extensive information from the standard InnoDB Monitor about the state of the InnoDB storage engine.
其中 TRANSACTIONS 部分的信息可用于分析鎖等待與死鎖。
TRANSACTIONSIf this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.
結果如下所示，TRANSACTIONS 部分包括兩個未提交事務。
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
------------
# 下一個待分配的事務id信息
Trx id counter 11309022
# 清除舊MVCC行時使用的事務ID，該事務與當前事務之間的老版本數據未被清除
Purge done for trx's n:o < 11309020 undo n:o < 0 state: running but idle
# 每個回滾段都有一個History鏈表，這些鏈表的總長度等于64
History list length 64
# 各個事務的具體信息
LIST OF TRANSACTIONS FOR EACH SESSION:
# not started 空閑事務，表示事務已經提交并且沒有再發起影響事務的語句
---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)
# 事務ID等于11309021的事務，處于活躍狀態154秒，正在使用索引讀取數據行
---TRANSACTION 11309021, ACTIVE 154 sec starting index read
# 事務11309021正在使用1張表，有1張表有鎖
mysql tables in use 1, locked 1
# 等鎖，鎖鏈表長度為2，占用內存1136字節，其中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
# 事務運行中SQL語句
update t2 set name='d' where id=1
# 鎖等待發生時在等待的鎖信息，已等待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
# 內存地址，用于調試
0: len 4; hex 80000001; asc ;; # 聚簇索引的值，80000001 表示主鍵值為1
1: len 6; hex 000000ac8fdc; asc ;; # 事務ID，對應十進制 11309020
2: len 7; hex 730000002a0b0d; asc s * ;; # unod記錄
3: len 1; hex 64; asc d;; # 非主鍵字段的值，'d'
------------------
# 持鎖，事務ID等于11309021的事務對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;;
# 事務ID等于11309020的事務，處于活躍狀態161秒
---TRANSACTION 11309020, ACTIVE 161 sec
# 該事務有2個鎖結構，其中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
# 持鎖，事務ID等于11309020的事務對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
============================
從中可以看到事務持鎖與等鎖的詳細信息，但是無法看到持鎖的 SQL。
由于信息不全，因此 SHOW ENGINE INNODB STATUS 更適合分析死鎖，因為死鎖已經沒有了現場，而鎖等待通常現場還在，可以直接查看 information_schema 數據庫中的表。
主要信息如下所示。
- 11309021 事務持有 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 事務分別持有 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
因此，鎖等待分析的結論如下所示：
- update 操作需要獲取兩把鎖，包括表級別的意向排它鎖與行級別 X 鎖（RECORD LOCK）；
- 并發 update 時由于意向鎖之間兼容，而行級 X 鎖之間沖突，導致發生鎖等待。
原理
鎖
首先為什么需要鎖？
鎖本質上是一種并發控制手段，用于解決事務在并發執行時可能引發的一致性問題。
并發事務訪問相同數據基本上可以分為以下三種情況：
- 讀-讀，相互不影響，因此允許；
- 寫-寫，會導致臟寫，因此不允許，通過給記錄加鎖實現；
- 讀-寫或寫-讀，會導致臟讀、不可重復讀、幻讀。解決方案主要分兩種：
而 InnoDB 存儲引擎支持事務與行鎖，并實現了基于 MVCC 的事務并發處理機制。
鎖的類型
如下所示，根據不同的維度，可以將鎖分為不同的類型。
其中：
- 根據加鎖機制，實際上就是鎖的實現方式，可以將鎖分為以下兩類：
- 樂觀鎖，先加鎖后訪問，傳統的關系型數據庫使用這種鎖機制；
- 悲觀鎖，先訪問后加鎖，常見實現如 CAS、版本號控制。
- 根據兼容性，可以將鎖分為以下兩類：
- 共享鎖，Shared-Lock，S 鎖，讀鎖；
- 排它鎖，Exclusive-Lock，X 鎖，寫鎖。
- 根據鎖的粒度，可以將鎖分為以下三類：
- 表鎖，Table-Lock，MyISAM 存儲引擎僅支持表鎖；
- 頁鎖，Page-Lock，使用相對較少；
- 行鎖，Row-Lock，InnoDB 存儲引擎也支持行鎖。
- 根據鎖的模式，可以將鎖分為以下幾種：
- 行鎖，Record Lock，鎖定一條記錄；
- 間隙鎖，Gap Lock，鎖定一個范圍，不包括記錄本身；
- Next-key Lock，鎖定一個范圍的記錄包括記錄本身，Next-key Lock = Record Lock + Gap Lock；
- 插入意向鎖，Insert Intention Lock，用于行鎖和表鎖共存。
具體各種類型鎖的介紹將在本系列后續文章中逐一介紹。
這里簡單介紹下行鎖，行鎖鎖定的是什么，是索引還是數據？
實際上 InnoDB 行鎖是通過給索引項加鎖實現的 ，如果沒有索引，InnoDB 會通過隱藏的聚簇索引來對記錄加鎖。
因此如果不通過索引條件檢索數據，InnoDB 將對表中所有數據加鎖，實際效果與表鎖一樣。
鎖的結構
對一條記錄加鎖的本質是在內存中創建一個鎖結構與之關聯（隱式鎖除外）。如果有多個鎖，保存在鏈表結構中。
簡化后的鎖結構示意圖如下所示，主要包括 trx 信息與 is_waiting 屬性，分別表示鎖所在的事務信息與當前事務是否在等待，然后將鎖結構與行記錄關聯。
img
假設事務 T1 改動了這條記錄，就生成了一個鎖結構與該記錄關聯，因此 is_waiting 屬性為 false，表示加鎖成功。
事務 T1 提交之前， 另一個事務 T2 也想改動這條記錄，先去查看有沒有鎖結構與這條記錄關聯，發現有一個鎖結構與之關聯后，也生成了一個鎖結構與該記錄關聯，不過 is_waiting 屬性為 true，表示鎖等待，直到 T1 提交后釋放鎖。
img
更詳細的 InnoDB 存儲引擎中的事務鎖結構如下所示。
img
其中：
- 鎖所在的事務信息：無論表鎖還是行鎖，都是在事務執行過程中給生成的，因此需要加載是哪個事務生成了這個鎖結構；
- 索引信息：對于行鎖需要記錄加鎖的記錄屬于哪個索引，原因是行鎖是給索引項加鎖；
- 表鎖/行鎖信息：
- 對于表鎖，記載這是對哪個表加的鎖，還有其他的一些信息；
- 對于行鎖，主要記載三個信息，包括 Space ID 記錄所在表空間、Page Number 記錄所在頁號、 n_bit 表示對哪一條記錄加了鎖，對于行鎖，一條記錄對應一個比特位；
- type_node：32 個比特位，記載三部分信息，包括 lock_mode 鎖的模式、lock_type 鎖的類型和 rec_lock_type 行鎖的具體類型：
- lock_mode，鎖的模式，占用低 4 位，十進制的 0、1、2、3、4 分別表示表級共享意向鎖 IS、表級排它意向鎖 IX、行級共享鎖 LOCK_S、行級排它鎖 LOCK_X、表級 LOCK_AUTO_INC 自增鎖；
- lock_type，鎖的類型，占用第 5～8 位，不過現階段只有第 5 位和第 6 位被使用。其中十進制的 16 和 32 分別表示表級鎖與行級鎖；
- rec_lock_type，行鎖的具體類型，十進制的 0、512、1024、2048 分別表示 LOCK_ORDINARY 即 Next-key Lock、LOCK_GAP 即間隙鎖、LOCK_REC_NOT_GAP 即正經記錄鎖、LOCK_INSERT_INTENTION 即插入意向鎖。此外，十進制的 256 表示 LOCK_WAIT，因此當第 9 個比特位為 0 與 1 分別表示當前事務獲取到鎖與未獲取到鎖處于等待狀態。
- 其他信息：為了更好的管理系統運行過程中生成的各種鎖結構而設計了各種哈希表和鏈表，可以先忽略；
- 一堆比特位：比特位的數量是由上面提到的 n_bits 屬性表示，頁面中的每條記錄在記錄頭信息中都包含一個 heap_no 屬性，偽記錄 Infimum 的 heap_no 值為0，Supremum 的 heap_no 值為 1，之后每插入一條記錄，heap_no 值就增 1。鎖結構最后的一堆比特位就對應著一個頁面中的記錄，一個比特位映射一個 heap_no。
文中案例update t2 set name='d' where id=1;這條 update 語句執行時鎖結構中信息如下所示。
---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，每個鏈表節點代表該事務持有的一個鎖結構，包括表鎖，記錄鎖以及自增鎖等。
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
其中：
- LOCK WAIT 2 lock struct(s) 表示事務正在等待鎖，其中鎖鏈表的長度為 2，并非表示在等待兩把鎖；
- 2 locks 表示 IX 鎖和 lock_mode X locks rec but not gap 即 Record Lock。
小技巧
鎖等待分析
分析鎖等待時，建議在發生鎖等待的現場關聯查詢分析持鎖與等鎖的事務與 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 執行過程中需要獲取的鎖。
select * from performance_schema.data_locks \\G
上文中提到，只有當事務因為獲取不到鎖而被阻塞即發生鎖等待時 information_schema.innodb_locks 表中才會有記錄，而 performance_schema.data_locks 表中即使事務沒有被阻塞，也可以看到事務持有的鎖，這一點對于鎖分析非常有用。
查看 update 這條 SQL 執行需要獲取的鎖。
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)
結果顯示 update 操作需要獲取兩把鎖，包括表級別的意向排它鎖與行級別 X 鎖（RECORD LOCK），與上文中分析結論一致。
上文中查看 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 代替。
結論
鎖本質是是一種并發控制手段，用于解決事務在并發執行時可能引發的一致性問題。
寫-寫操作會導致臟寫，即一個事務覆蓋另一個事務未提交的更改，因此需要給寫操作加寫鎖。
InnoDB 存儲引擎支持事務與行鎖，其中行鎖是給索引項加鎖。
對一條記錄加鎖的本質是在內存中創建一個鎖結構與之關聯（隱式鎖除外）。如果有多個鎖，保存在鏈表結構中。
鎖結構中主要包括 trx 信息與 is_waiting 屬性，分別表示鎖所在的事務信息與當前事務是否在等待，然后將鎖結構與行記錄關聯。
InnoDB 中鎖的實現是悲觀鎖，先加鎖后訪問，因此無論是否獲取到鎖，都會在內存中生成對應的鎖結構，其中 is_waiting 為 false 表示持鎖，為 true 表示等鎖。
因此，并發 update 會導致鎖等待，分析鎖等待的方法主要包括：
- 使用 information_schema 數據庫中的表獲取鎖信息，不過要求鎖等待現場查看；
- 使用 SHOW ENGINE INNODB STATUS 獲取鎖信息，不過信息不全，因此適合死鎖分析。
從 MySQL 8.0.1 版本開始，可以通過 performance_schema.data_locks 表查看 SQL 執行過程中需要獲取的鎖。即使事務沒有被阻塞，也可以看到事務持有的鎖，這一點對于鎖分析非常有用。
通過查詢 performance_schema.data_locks 表，可以明確的看到 update 操作需要獲取兩把鎖，包括表級別的意向排它鎖與行級別 X 鎖（RECORD LOCK）。
待辦
- 鎖的類型
- 鎖的信息，n_bits、n_fields
- 死鎖分析
- 事務隔離級別、MVCC 與鎖的關系
-
SQL
+關注
關注
1文章
760瀏覽量
44078 -
MySQL
+關注
關注
1文章
802瀏覽量
26446 -
Hash算法
+關注
關注
0文章
43瀏覽量
7379
發布評論請先 登錄
相關推薦
深度剖析MySQL/InnoDB的并發控制和加鎖技術
基于MySQL的鎖機制
樹莓派MYSQL安裝【附離線安裝mysql】
MySQL中update修改數據與原數據相同是否會再次執行
select語句和update語句分別是怎么執行的
Linux中的傷害/等待互斥鎖介紹
數據庫的鎖機制真正的原理
select......for update會鎖表還是鎖行？
MySQL是怎么加行級鎖的？有什么規則？
一文徹底搞懂MySQL鎖究竟鎖的啥1
一文徹底搞懂MySQL鎖究竟鎖的啥2
MySQL并發Replace into導致死鎖場景簡析
工商網監
評論