MySQL8.0鎖情況排查，你學(xué)會(huì)了嗎？

作者：楊延昭 2023-01-02 08:20:14

本文將通過實(shí)驗(yàn)介紹MySQL8.0版鎖該如何排查，以及找到阻塞的sql語句，實(shí)驗(yàn)的MySQL版本為8.0.26，隔離級(jí)別為RR。

在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源(CPU、RAM、IO)的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。從這個(gè)角度來說，鎖對教據(jù)庫而言顯得尤其重要，也更加復(fù)雜。

本文將通過實(shí)驗(yàn)介紹MySQL8.0版鎖該如何排查，以及找到阻塞的sql語句，實(shí)驗(yàn)的MySQL版本為8.0.26，隔離級(jí)別為RR。

1.MySQL8.0版本鎖情況排查核心表

information_schema.innodb_trx  ##正在運(yùn)行的事務(wù)信息。
sys.innodb_lock_waits          ##處于鎖等待的關(guān)聯(lián)事務(wù)信息。
performance_schema.threads     ##SQL線程及線程號(hào)、進(jìn)程號(hào)、OS線程號(hào)等信息

2.行鎖監(jiān)控語句及排查步驟

# 確認(rèn)有沒有鎖等待:
show status like 'innodb_row_lock%';
select * from information_schema.innodb_trx;

# 查詢鎖等待詳細(xì)信息
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.測試驗(yàn)證

mysql> use world;
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

3.1 分別開啟兩個(gè)窗口（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;
執(zhí)行完處于夯住狀態(tài)，默認(rèn)50秒會(huì)超時(shí)回滾。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> set innodb_lock_wait_timeout=5000;    ##鎖等待超時(shí)參數(shù),這里設(shè)置為5000便于測試.
mysql> update city  set name='girl' where id=1;

3.2 再開一個(gè)窗口s3，查看鎖狀態(tài)

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        ##剛剛運(yùn)行的第二個(gè)語句事務(wù)ID
        trx_state: LOCK WAIT   ##處于鎖等待狀態(tài)
      trx_started: 2022-12-23 16:00:42
trx_tables_locked: 1          ##鎖了1張表
  trx_rows_locked: 2     ##鎖了2行
*************************** 2. row ***************************
           trx_id: 8994       ##剛剛運(yùn)行的第一個(gè)語句事務(wù)ID
        trx_state: RUNNING    ##獲得鎖的狀態(tài)
      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   ##阻塞者事務(wù)ID
                blocking_pid: 32     ##阻塞者進(jìn)程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 查看進(jìn)程ID為32的進(jìn)程，無法顯示當(dāng)前執(zhí)行的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 查看進(jìn)程ID為32的進(jìn)程對應(yīng)的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)

3.5 根據(jù)線程ID 75，找到真正執(zhí)行的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語句，確認(rèn)后如果沒問題可以kill掉。

3.6 處理鎖源SQL對應(yīng)的連接線程。

kill  32;

3.7 通過設(shè)置回滾申請鎖的事務(wù)的時(shí)間，讓處于等待的事務(wù)回滾，解決鎖沖突。

set innodb_lock_wait_timeout=500;  #設(shè)置回滾申請鎖的事務(wù)的時(shí)間。

4.innodb_lock_wait_timeout參數(shù)

innodb_lock_wait_timeout指的是事務(wù)等待獲取資源等待的最長時(shí)間，超過這個(gè)時(shí)間還未分配到資源則會(huì)返回應(yīng)用失敗；

參數(shù)的時(shí)間單位是秒，最小可設(shè)置為1s，最大可設(shè)置1073741824秒（34年）,默認(rèn)安裝時(shí)這個(gè)值是50s.

當(dāng)鎖等待超過設(shè)置時(shí)間的時(shí)候，就會(huì)報(bào)如下的錯(cuò)誤；

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

參數(shù)支持范圍為session和global，且支持動(dòng)態(tài)修改，所以可以通過兩種方法修改；

通過語句修改

set innodb_lock_wait_timeout=50;
set global innodb_lock_wait_timeout=50;
注意global的修改對當(dāng)前線程是不生效的，只有建立新的連接才生效

修改參數(shù)文件/etc/my.cnf

innodb_lock_wait_timeout = 50

責(zé)任編輯：武曉燕來源： GreatSQL社區(qū)

MySQL 鎖數(shù)據(jù)庫