- GreatSQL社區原創內容未經授權不得隨意使用，轉載請聯系小編并注明來源，
- GreatSQL是MySQL的國產分支版本，使用上與MySQL一致，
- 作者： 楊延昭
- 文章來源：GreatSQL社區投稿
在資料庫中，除傳統的計算資源(CPU、RAM、IO)的爭用以外，資料也是一種供許多用戶共享的資源，如何保證資料并發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發訪問性能的一個重要因素，從這個角度來說，鎖對教據庫而言顯得尤其重要，也更加復雜，
本文將通過實驗介紹MySQL8.0版鎖該如何排查，以及找到阻塞的sql陳述句，實驗的MySQL版本為8.0.26，隔離級別為RR，
1.MySQL8.0版本鎖情況排查核心表
information_schema.innodb_trx ##正在運行的事務資訊，
sys.innodb_lock_waits ##處于鎖等待的關聯事務資訊，
performance_schema.threads ##SQL執行緒及執行緒號、行程號、OS執行緒號等資訊
2.行鎖監控陳述句及排查步驟
# 確認有沒有鎖等待:
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;
3.測驗驗證
mysql> use world;
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
3.1 分別開啟兩個視窗（session1,session2）
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;
3.2 再開一個視窗s3，查看鎖狀態
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)
3.3 查看行程ID為32的行程，無法顯示當前執行的SQL陳述句
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)
3.4 查看行程ID為32的行程對應的SQL執行緒ID
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)
找出SQL執行緒ID為75
3.5 根據執行緒ID 75，找到真正執行的SQL陳述句
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掉，
3.6 處理鎖源SQL對應的連接執行緒，
kill 32;
3.7 通過設定回滾申請鎖的事務的時間，讓處于等待的事務回滾，解決鎖沖突，
set innodb_lock_wait_timeout=500; #設定回滾申請鎖的事務的時間，
4.innodb_lock_wait_timeout引數
innodb_lock_wait_timeout指的是事務等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會回傳應用失敗；
引數的時間單位是秒，最小可設定為1s，最大可設定1073741824秒（34年）,默認安裝時這個值是50s.
當鎖等待超過設定時間的時候，就會報如下的錯誤；
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
引數支持范圍為session和global，且支持動態修改，所以可以通過兩種方法修改；
1.通過陳述句修改
set innodb_lock_wait_timeout=50;
set global innodb_lock_wait_timeout=50;
注意global的修改對當前執行緒是不生效的，只有建立新的連接才生效
2.修改引數檔案/etc/my.cnf
innodb_lock_wait_timeout = 50
Enjoy GreatSQL ??
關于 GreatSQL
GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，
相關鏈接： GreatSQL社區 Gitee GitHub Bilibili
GreatSQL社區：
社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼添加
GreatSQL社區助手微信好友，發送驗證資訊加群，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/541631.html
標籤：其他