MySQL8.0鎖情況排查，你學會了嗎？
作者：楊延昭
本文將通過實驗介紹MySQL8.0版鎖該如何排查，以及找到阻塞的sql語句，實驗的MySQL版本為8.0.26，隔離級別為RR。
在數據庫中，除傳統的計算資源(CPU、RAM、IO)的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對教據庫而言顯得尤其重要，也更加復雜。
本文將通過實驗介紹MySQL8.0版鎖該如何排查，以及找到阻塞的sql語句，實驗的MySQL版本為8.0.26，隔離級別為RR。
information_schema.innodb_trx ##正在運行的事務信息。
sys.innodb_lock_waits ##處于鎖等待的關聯事務信息。
performance_schema.threads ##SQL線程及線程號、進程號、OS線程號等信息
# 確認有沒有鎖等待:
show status like 'innodb_row_lock%';
select * from information_schema.innodb_trx;
# 查詢鎖等待詳細信息
select * from sys.innodb_lock_waits; ----> blocking_pid(鎖源的連接線程)
# 通過連接線程ID找SQL線程語句
select * from performance_schema.threads;
# 通過SQL線程找到SQL語句
select * from performance_schema.events_statements_history;
mysql> use world;
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
s1:
# 加排他鎖
mysql> begin;
mysql> select * from world.city where id=1 for update;
s2:
# 加排他鎖
mysql> begin;
mysql> update city set name='girl' where id=1;
執行完處于夯住狀態，默認50秒會超時回滾。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> set innodb_lock_wait_timeout=5000; ##鎖等待超時參數,這里設置為5000便于測試.
mysql> update city set name='girl' where id=1;
mysql> use information_schema;
mysql> select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from innodb_trx\G
*************************** 1. row ***************************
trx_id: 8995 ##剛剛運行的第二個語句事務ID
trx_state: LOCK WAIT ##處于鎖等待狀態
trx_started: 2022-12-23 16:00:42
trx_tables_locked: 1 ##鎖了1張表
trx_rows_locked: 2 ##鎖了2行
*************************** 2. row ***************************
trx_id: 8994 ##剛剛運行的第一個語句事務ID
trx_state: RUNNING ##獲得鎖的狀態
trx_started: 2022-12-23 15:59:41
trx_tables_locked: 1
trx_rows_locked: 1
2 rows in set (0.00 sec)
mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2022-12-23 16:01:57
wait_age: 00:00:52
wait_age_secs: 52
locked_table: `world`.`city`
locked_table_schema: world
locked_table_name: city
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 8995
waiting_trx_started: 2022-12-23 16:00:42
waiting_trx_age: 00:02:07
waiting_trx_rows_locked: 2
waiting_trx_rows_modified: 0
waiting_pid: 33
waiting_query: update city set name='girl' where id=1
waiting_lock_id: 140190433225944:16:6:2:140190349859736
waiting_lock_mode: X,REC_NOT_GAP
blocking_trx_id: 8994 ##阻塞者事務ID
blocking_pid: 32 ##阻塞者進程ID, show processlist可查;
blocking_query: NULL
blocking_lock_id: 140190433226752:16:6:2:140190349865536
blocking_lock_mode: X,REC_NOT_GAP
blocking_trx_started: 2022-12-23 15:59:41
blocking_trx_age: 00:03:08
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 32
sql_kill_blocking_connection: KILL 32 ##解鎖方法
1 row in set (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------------+--------------------+---------+-------+------------------------+-----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------------------+---------+-------+------------------------+-----------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 27235 | Waiting on empty queue | NULL |
| 29 | root | localhost:43074 | information_schema | Query | 0 | init | show processlist |
| 32 | root | localhost:43080 | world | Sleep | 248 | | NULL |
| 33 | root | localhost:43082 | world | Query | 112 | updating | update city set name='girl' where id=1 |
+----+-----------------+-----------------+--------------------+---------+-------+------------------------+-----------------------------------------+
4 rows in set (0.00 sec)
mysql> select thread_id,processlist_id from performance_schema.threads where processlist_id=32;
+-----------+----------------+
| thread_id | processlist_id |
+-----------+----------------+
| 75 | 32 |
+-----------+----------------+
1 row in set (0.00 sec)
mysql> select thread_id,sql_text from performance_schema.events_statements_history where thread_id=75\G
*************************** 1. row ***************************
thread_id: 75
sql_text: NULL
*************************** 2. row ***************************
thread_id: 75
sql_text: NULL
*************************** 3. row ***************************
thread_id: 75
sql_text: NULL
*************************** 4. row ***************************
thread_id: 75
sql_text: show tables
*************************** 5. row ***************************
thread_id: 75
sql_text: set autocommit=0
*************************** 6. row ***************************
thread_id: 75
sql_text: begin
*************************** 7. row ***************************
thread_id: 75
sql_text: select * from world.city where id=1 for update
*************************** 8. row ***************************
thread_id: 75
sql_text: NULL
*************************** 9. row ***************************
thread_id: 75
sql_text: show databases
*************************** 10. row ***************************
thread_id: 75
sql_text: show tables
10 rows in set (0.00 sec)
找到select * from world.city where id=1 for update語句，確認后如果沒問題可以kill掉。
kill 32;
set innodb_lock_wait_timeout=500; #設置回滾申請鎖的事務的時間。
innodb_lock_wait_timeout指的是事務等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會返回應用失敗；
參數的時間單位是秒，最小可設置為1s，最大可設置1073741824秒（34年）,默認安裝時這個值是50s.
當鎖等待超過設置時間的時候，就會報如下的錯誤；
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
參數支持范圍為session和global，且支持動態修改，所以可以通過兩種方法修改；
- 通過語句修改
set innodb_lock_wait_timeout=50;
set global innodb_lock_wait_timeout=50;
注意global的修改對當前線程是不生效的，只有建立新的連接才生效
- 修改參數文件/etc/my.cnf
innodb_lock_wait_timeout = 50
責任編輯：武曉燕
來源：
GreatSQL社區
