MySQL并發(fā)update導(dǎo)致鎖等待介紹
引言
生產(chǎn)環(huán)境中經(jīng)常會(huì)遇到鎖等待與死鎖相關(guān)的問題，這類問題通常比較緊急，而且由于鎖相關(guān)影響因素較多，因此分析難度較大。
本文從最簡單的一類鎖等待開始，即并發(fā) update 導(dǎo)致鎖等待。
介紹
如果相同的 update 同時(shí)執(zhí)行會(huì)發(fā)生什么呢？
實(shí)際上會(huì)發(fā)生鎖等待，生產(chǎn)環(huán)境中就遇到過這種案例，并發(fā) update 導(dǎo)致鎖等待。
死鎖建立在鎖等待的基礎(chǔ)上，因此需要先理解鎖等待的機(jī)制與分析思路。本文通過一個(gè)最簡單的并發(fā) update 介紹鎖等待的分析方法。
模擬
首先，聲明事務(wù)隔離級(jí)別為 RR（REPEATABLE-READ）。
流程
兩個(gè) session 分別在開啟事務(wù)的前提下執(zhí)行相同的 update 語句導(dǎo)致鎖等待。
其中超時(shí)時(shí)間由系統(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 存儲(chǔ)引擎中事務(wù)的行鎖等待時(shí)間，超時(shí)回滾。
innodb_lock_wait_timeoutThe 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 表中存儲(chǔ)了 InnoDB 存儲(chǔ)引擎當(dāng)前正在執(zhí)行的事務(wù)信息。
其中：
- TRX_TABLES_LOCKED 字段表示事務(wù)當(dāng)前執(zhí)行 SQL 持有行鎖涉及到的表的數(shù)量，注意不包括表鎖，因此盡管部分行被鎖定，但通常不影響其他事務(wù)的讀寫操作；
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 字段表示被事務(wù)鎖定的行數(shù)，其中可能包括被標(biāo)記為刪除但實(shí)際上未物理刪除的數(shù)據(jù)行。
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.
結(jié)果表明當(dāng)前有兩個(gè)未提交事務(wù)，不同點(diǎn)是其中一個(gè)執(zhí)行中，一個(gè)鎖等待，相同點(diǎn)是都在內(nèi)存中創(chuàng)建了兩個(gè)鎖結(jié)構(gòu)，而且其中一個(gè)是行鎖。
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個(gè)表上有行鎖
trx_lock_structs: 2 # 內(nèi)存中2個(gè)鎖結(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個(gè)表上有行鎖
trx_lock_structs: 2 # 內(nèi)存中2個(gè)鎖結(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 表中主要包括以下兩方面的鎖信息：
- 如果一個(gè)事務(wù)想要獲取某個(gè)鎖但未獲取到，則記錄該鎖信息，即等鎖事務(wù)；
- 如果一個(gè)事務(wù)獲取到了某個(gè)鎖，但是這個(gè)鎖阻塞了其他事務(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ù)因?yàn)楂@取不到鎖而被阻塞即發(fā)生鎖等待時(shí) innodb_locks 表中才會(huì)有記錄，因此當(dāng)只有一個(gè)事務(wù)時(shí)，無法查看該事務(wù)所加的鎖信息。
如下所示，鎖超時(shí)之后查詢 innodb_locks 表，結(jié)果為空。
mysql> select * from information_schema.innodb_locks\\G
Empty set, 1 warning (0.00 sec)
如下所示，鎖超時(shí)之前查詢 innodb_locks 表，結(jié)果表明所有事務(wù)共請(qǐng)求了兩次 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ǐng)求的鎖的類型，但是無法區(qū)分等鎖事務(wù)與持鎖事務(wù)。
innodb_lock_waits
information_schema.innodb_lock_waits 表中記錄每個(gè)阻塞的事務(wù)是因?yàn)楂@取不到哪個(gè)事務(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)場(chǎng)關(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 為空。
實(shí)際上，可以從 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 存儲(chǔ)引擎標(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 部分的信息可用于分析鎖等待與死鎖。
TRANSACTIONSIf 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 部分包括兩個(gè)未提交事務(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
------------
# 下一個(gè)待分配的事務(wù)id信息
Trx id counter 11309022
# 清除舊MVCC行時(shí)使用的事務(wù)ID，該事務(wù)與當(dāng)前事務(wù)之間的老版本數(shù)據(jù)未被清除
Purge done for trx's n:o < 11309020 undo n:o < 0 state: running but idle
# 每個(gè)回滾段都有一個(gè)History鏈表，這些鏈表的總長度等于64
History list length 64
# 各個(gè)事務(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ù)運(yùn)行中SQL語句
update t2 set name='d' where id=1
# 鎖等待發(fā)生時(shí)在等待的鎖信息，已等待25秒
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
# 等鎖，在等待主鍵索引（index PRIMARY）上的行級(jí)別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，對(duì)應(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ù)對(duì)t2表加了表級(jí)別的意向排它鎖
TABLE LOCK table `test_zk`.`t2` trx id 11309021 lock mode IX
# 等鎖，在等待主鍵索引（index PRIMARY）上的行級(jí)別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個(gè)鎖結(jié)構(gòu)，其中1個(gè)行鎖
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ù)對(duì)t2表加了表級(jí)別的意向排它鎖，IX鎖之間兼容
TABLE LOCK table `test_zk`.`t2` trx id 11309020 lock mode IX
# 持鎖，主鍵索引（index PRIMARY）上的行級(jí)別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 更適合分析死鎖，因?yàn)樗梨i已經(jīng)沒有了現(xiàn)場(chǎng)，而鎖等待通?，F(xiàn)場(chǎng)還在，可以直接查看 information_schema 數(shù)據(jù)庫中的表。
主要信息如下所示。
- 11309021 事務(wù)持有 t2 表的表級(jí)別意向排它鎖，等待主鍵索引上的行級(jí)別 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 表的表級(jí)別意向排它鎖與主鍵索引上的行級(jí)別 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 操作需要獲取兩把鎖，包括表級(jí)別的意向排它鎖與行級(jí)別 X 鎖（RECORD LOCK）；
- 并發(fā) update 時(shí)由于意向鎖之間兼容，而行級(jí) X 鎖之間沖突，導(dǎo)致發(fā)生鎖等待。
原理
鎖
首先為什么需要鎖？
鎖本質(zhì)上是一種并發(fā)控制手段，用于解決事務(wù)在并發(fā)執(zhí)行時(shí)可能引發(fā)的一致性問題。
并發(fā)事務(wù)訪問相同數(shù)據(jù)基本上可以分為以下三種情況：
- 讀-讀，相互不影響，因此允許；
- 寫-寫，會(huì)導(dǎo)致臟寫，因此不允許，通過給記錄加鎖實(shí)現(xiàn)；
- 讀-寫或?qū)?讀，會(huì)導(dǎo)致臟讀、不可重復(fù)讀、幻讀。解決方案主要分兩種：
- MVCC 多版本并發(fā)控制，保存符合條件的記錄的多個(gè)版本，寫操作針對(duì)最新版本，讀操作針對(duì)歷史版本。因此讀-寫不沖突；
- 讀寫操作均加鎖，每次都需要讀取最新版本數(shù)據(jù)，讀寫操作均采用加鎖方式，因此讀-寫沖突。
而 InnoDB 存儲(chǔ)引擎支持事務(wù)與行鎖，并實(shí)現(xiàn)了基于 MVCC 的事務(wù)并發(fā)處理機(jī)制。
鎖的類型
如下所示，根據(jù)不同的維度，可以將鎖分為不同的類型。
其中：
- 根據(jù)加鎖機(jī)制，實(shí)際上就是鎖的實(shí)現(xiàn)方式，可以將鎖分為以下兩類：
- 樂觀鎖，先加鎖后訪問，傳統(tǒng)的關(guān)系型數(shù)據(jù)庫使用這種鎖機(jī)制；
- 悲觀鎖，先訪問后加鎖，常見實(shí)現(xiàn)如 CAS、版本號(hào)控制。
- 根據(jù)兼容性，可以將鎖分為以下兩類：
- 共享鎖，Shared-Lock，S 鎖，讀鎖；
- 排它鎖，Exclusive-Lock，X 鎖，寫鎖。
- 根據(jù)鎖的粒度，可以將鎖分為以下三類：
- 表鎖，Table-Lock，MyISAM 存儲(chǔ)引擎僅支持表鎖；
- 頁鎖，Page-Lock，使用相對(duì)較少；
- 行鎖，Row-Lock，InnoDB 存儲(chǔ)引擎也支持行鎖。
- 根據(jù)鎖的模式，可以將鎖分為以下幾種：
- 行鎖，Record Lock，鎖定一條記錄；
- 間隙鎖，Gap Lock，鎖定一個(gè)范圍，不包括記錄本身；
- Next-key Lock，鎖定一個(gè)范圍的記錄包括記錄本身，Next-key Lock = Record Lock + Gap Lock；
- 插入意向鎖，Insert Intention Lock，用于行鎖和表鎖共存。
具體各種類型鎖的介紹將在本系列后續(xù)文章中逐一介紹。
這里簡單介紹下行鎖，行鎖鎖定的是什么，是索引還是數(shù)據(jù)？
實(shí)際上 InnoDB 行鎖是通過給索引項(xiàng)加鎖實(shí)現(xiàn)的 ，如果沒有索引，InnoDB 會(huì)通過隱藏的聚簇索引來對(duì)記錄加鎖。
因此如果不通過索引條件檢索數(shù)據(jù)，InnoDB 將對(duì)表中所有數(shù)據(jù)加鎖，實(shí)際效果與表鎖一樣。
鎖的結(jié)構(gòu)
對(duì)一條記錄加鎖的本質(zhì)是在內(nèi)存中創(chuàng)建一個(gè)鎖結(jié)構(gòu)與之關(guān)聯(lián)（隱式鎖除外）。如果有多個(gè)鎖，保存在鏈表結(jié)構(gòu)中。
簡化后的鎖結(jié)構(gòu)示意圖如下所示，主要包括 trx 信息與 is_waiting 屬性，分別表示鎖所在的事務(wù)信息與當(dāng)前事務(wù)是否在等待，然后將鎖結(jié)構(gòu)與行記錄關(guān)聯(lián)。
img
假設(shè)事務(wù) T1 改動(dòng)了這條記錄，就生成了一個(gè)鎖結(jié)構(gòu)與該記錄關(guān)聯(lián)，因此 is_waiting 屬性為 false，表示加鎖成功。
事務(wù) T1 提交之前， 另一個(gè)事務(wù) T2 也想改動(dòng)這條記錄，先去查看有沒有鎖結(jié)構(gòu)與這條記錄關(guān)聯(lián)，發(fā)現(xiàn)有一個(gè)鎖結(jié)構(gòu)與之關(guān)聯(lián)后，也生成了一個(gè)鎖結(jié)構(gòu)與該記錄關(guān)聯(lián)，不過 is_waiting 屬性為 true，表示鎖等待，直到 T1 提交后釋放鎖。
img
更詳細(xì)的 InnoDB 存儲(chǔ)引擎中的事務(wù)鎖結(jié)構(gòu)如下所示。
img
其中：
- 鎖所在的事務(wù)信息：無論表鎖還是行鎖，都是在事務(wù)執(zhí)行過程中給生成的，因此需要加載是哪個(gè)事務(wù)生成了這個(gè)鎖結(jié)構(gòu)；
- 索引信息：對(duì)于行鎖需要記錄加鎖的記錄屬于哪個(gè)索引，原因是行鎖是給索引項(xiàng)加鎖；
- 表鎖/行鎖信息：
- 對(duì)于表鎖，記載這是對(duì)哪個(gè)表加的鎖，還有其他的一些信息；
- 對(duì)于行鎖，主要記載三個(gè)信息，包括 Space ID 記錄所在表空間、Page Number 記錄所在頁號(hào)、 n_bit 表示對(duì)哪一條記錄加了鎖，對(duì)于行鎖，一條記錄對(duì)應(yīng)一個(gè)比特位；
- type_node：32 個(gè)比特位，記載三部分信息，包括 lock_mode 鎖的模式、lock_type 鎖的類型和 rec_lock_type 行鎖的具體類型：
- lock_mode，鎖的模式，占用低 4 位，十進(jìn)制的 0、1、2、3、4 分別表示表級(jí)共享意向鎖 IS、表級(jí)排它意向鎖 IX、行級(jí)共享鎖 LOCK_S、行級(jí)排它鎖 LOCK_X、表級(jí) LOCK_AUTO_INC 自增鎖；
- lock_type，鎖的類型，占用第 5～8 位，不過現(xiàn)階段只有第 5 位和第 6 位被使用。其中十進(jìn)制的 16 和 32 分別表示表級(jí)鎖與行級(jí)鎖；
- 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 個(gè)比特位為 0 與 1 分別表示當(dāng)前事務(wù)獲取到鎖與未獲取到鎖處于等待狀態(tài)。
- 其他信息：為了更好的管理系統(tǒng)運(yùn)行過程中生成的各種鎖結(jié)構(gòu)而設(shè)計(jì)了各種哈希表和鏈表，可以先忽略；
- 一堆比特位：比特位的數(shù)量是由上面提到的 n_bits 屬性表示，頁面中的每條記錄在記錄頭信息中都包含一個(gè) heap_no 屬性，偽記錄 Infimum 的 heap_no 值為0，Supremum 的 heap_no 值為 1，之后每插入一條記錄，heap_no 值就增 1。鎖結(jié)構(gòu)最后的一堆比特位就對(duì)應(yīng)著一個(gè)頁面中的記錄，一個(gè)比特位映射一個(gè) heap_no。
文中案例update t2 set name='d' where id=1;這條 update 語句執(zhí)行時(shí)鎖結(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，含義還不確定。
鎖等待時(shí)顯示 2 lock struct(s)，表示 trx->trx_locks 鎖鏈表的長度為2，每個(gè)鏈表節(jié)點(diǎn)代表該事務(wù)持有的一個(gè)鎖結(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。
小技巧
鎖等待分析
分析鎖等待時(shí)，建議在發(fā)生鎖等待的現(xiàn)場(chǎng)關(guān)聯(lián)查詢分析持鎖與等鎖的事務(wù)與 SQL，注意如果鎖等待已超時(shí)，就看不到了，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ù)因?yàn)楂@取不到鎖而被阻塞即發(fā)生鎖等待時(shí) information_schema.innodb_locks 表中才會(huì)有記錄，而 performance_schema.data_locks 表中即使事務(wù)沒有被阻塞，也可以看到事務(wù)持有的鎖，這一點(diǎn)對(duì)于鎖分析非常有用。
查看 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 # 表級(jí)鎖
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 # 行級(jí)鎖
LOCK_MODE: X,REC_NOT_GAP # X 型記錄鎖
LOCK_STATUS: GRANTED
LOCK_DATA: 1 # 鎖定主鍵值為1的記錄
2 rows in set (0.00 sec)
結(jié)果顯示 update 操作需要獲取兩把鎖，包括表級(jí)別的意向排它鎖與行級(jí)別 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)
實(shí)際上，這兩張表在 5.7.14 版本中已過時(shí)，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í)行時(shí)可能引發(fā)的一致性問題。
寫-寫操作會(huì)導(dǎo)致臟寫，即一個(gè)事務(wù)覆蓋另一個(gè)事務(wù)未提交的更改，因此需要給寫操作加寫鎖。
InnoDB 存儲(chǔ)引擎支持事務(wù)與行鎖，其中行鎖是給索引項(xiàng)加鎖。
對(duì)一條記錄加鎖的本質(zhì)是在內(nèi)存中創(chuàng)建一個(gè)鎖結(jié)構(gòu)與之關(guān)聯(lián)（隱式鎖除外）。如果有多個(gè)鎖，保存在鏈表結(jié)構(gòu)中。
鎖結(jié)構(gòu)中主要包括 trx 信息與 is_waiting 屬性，分別表示鎖所在的事務(wù)信息與當(dāng)前事務(wù)是否在等待，然后將鎖結(jié)構(gòu)與行記錄關(guān)聯(lián)。
InnoDB 中鎖的實(shí)現(xiàn)是悲觀鎖，先加鎖后訪問，因此無論是否獲取到鎖，都會(huì)在內(nèi)存中生成對(duì)應(yīng)的鎖結(jié)構(gòu)，其中 is_waiting 為 false 表示持鎖，為 true 表示等鎖。
因此，并發(fā) update 會(huì)導(dǎo)致鎖等待，分析鎖等待的方法主要包括：
- 使用 information_schema 數(shù)據(jù)庫中的表獲取鎖信息，不過要求鎖等待現(xiàn)場(chǎng)查看；
- 使用 SHOW ENGINE INNODB STATUS 獲取鎖信息，不過信息不全，因此適合死鎖分析。
從 MySQL 8.0.1 版本開始，可以通過 performance_schema.data_locks 表查看 SQL 執(zhí)行過程中需要獲取的鎖。即使事務(wù)沒有被阻塞，也可以看到事務(wù)持有的鎖，這一點(diǎn)對(duì)于鎖分析非常有用。
通過查詢 performance_schema.data_locks 表，可以明確的看到 update 操作需要獲取兩把鎖，包括表級(jí)別的意向排它鎖與行級(jí)別 X 鎖（RECORD LOCK）。
待辦
- 鎖的類型
- 鎖的信息，n_bits、n_fields
- 死鎖分析
- 事務(wù)隔離級(jí)別、MVCC 與鎖的關(guān)系
-
SQL
+關(guān)注
關(guān)注
1文章
783瀏覽量
44968 -
MySQL
+關(guān)注
關(guān)注
1文章
850瀏覽量
27741 -
Hash算法
+關(guān)注
關(guān)注
0文章
43瀏覽量
7522
發(fā)布評(píng)論請(qǐng)先 登錄
深度剖析MySQL/InnoDB的并發(fā)控制和加鎖技術(shù)
基于MySQL的鎖機(jī)制
MySQL root密碼忘記怎么辦？
MySQL中update修改數(shù)據(jù)與原數(shù)據(jù)相同是否會(huì)再次執(zhí)行
select語句和update語句分別是怎么執(zhí)行的
Linux中的傷害/等待互斥鎖介紹
數(shù)據(jù)庫的鎖機(jī)制真正的原理
select......for update會(huì)鎖表還是鎖行？
MySQL是怎么加行級(jí)鎖的？有什么規(guī)則？
一文徹底搞懂MySQL鎖究竟鎖的啥1
一文徹底搞懂MySQL鎖究竟鎖的啥2
MySQL并發(fā)Replace into導(dǎo)致死鎖場(chǎng)景簡析
工商網(wǎng)監(jiān)
評(píng)論