MySQL8.0锁情况排查

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
  
• 作者： 杨延昭
  
• 文章来源：GreatSQL社区投稿
  

在数据库中，除传统的计算资源(CPU、RAM、IO)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对教据库而言显得尤其重要，也更加复杂。
本文将通过实验介绍MySQL8.0版锁该如何排查，以及找到阻塞的sql语句，实验的MySQL版本为8.0.26，隔离级别为RR。
1.MySQL8.0版本锁情况排查核心表

1. information_schema.innodb_trx  ##正在运行的事务信息。
2. sys.innodb_lock_waits          ##处于锁等待的关联事务信息。
3. performance_schema.threads     ##SQL线程及线程号、进程号、OS线程号等信息

复制代码

2.行锁监控语句及排查步骤

1. # 确认有没有锁等待:
2. show status like 'innodb_row_lock%';
3. select * from information_schema.innodb_trx;
5. # 查询锁等待详细信息
6. select * from sys.innodb_lock_waits; ----> blocking_pid(锁源的连接线程)
8. # 通过连接线程ID找SQL线程语句
9. select * from performance_schema.threads;
11. # 通过SQL线程找到SQL语句
12. select * from performance_schema.events_statements_history;

复制代码

3.测试验证

1. mysql> use world;
2. mysql> show tables;
3. +-----------------+
4. | Tables_in_world |
5. +-----------------+
6. | city            |
7. | country         |
8. | countrylanguage |
9. +-----------------+
10. 3 rows in set (0.00 sec)

复制代码

3.1 分别开启两个窗口（session1,session2）

1. s1:
2. # 加排他锁
3. mysql> begin;
4. mysql> select * from world.city where id=1 for update;
6. s2:
7. # 加排他锁
8. mysql> begin;
9. mysql> update city  set name='girl' where id=1;
10. 执行完处于夯住状态，默认50秒会超时回滚。
11. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
13. mysql> set innodb_lock_wait_timeout=5000;    ##锁等待超时参数,这里设置为5000便于测试.
14. mysql> update city  set name='girl' where id=1;

复制代码

3.2 再开一个窗口s3，查看锁状态

1. mysql> use information_schema;
2. mysql> select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from innodb_trx\G
3. *************************** 1. row ***************************
4.            trx_id: 8995        ##刚刚运行的第二个语句事务ID
5.         trx_state: LOCK WAIT   ##处于锁等待状态
6.       trx_started: 2022-12-23 16:00:42
7. trx_tables_locked: 1          ##锁了1张表
8.   trx_rows_locked: 2     ##锁了2行
9. *************************** 2. row ***************************
10.            trx_id: 8994       ##刚刚运行的第一个语句事务ID
11.         trx_state: RUNNING    ##获得锁的状态
12.       trx_started: 2022-12-23 15:59:41
13. trx_tables_locked: 1
14.   trx_rows_locked: 1
15. 2 rows in set (0.00 sec)
16. mysql> select * from sys.innodb_lock_waits\G
17. *************************** 1. row ***************************
18.                 wait_started: 2022-12-23 16:01:57
19.                     wait_age: 00:00:52
20.                wait_age_secs: 52
21.                 locked_table: `world`.`city`
22.          locked_table_schema: world
23.            locked_table_name: city
24.       locked_table_partition: NULL
25.    locked_table_subpartition: NULL
26.                 locked_index: PRIMARY
27.                  locked_type: RECORD
28.               waiting_trx_id: 8995
29.          waiting_trx_started: 2022-12-23 16:00:42
30.              waiting_trx_age: 00:02:07
31.      waiting_trx_rows_locked: 2
32.    waiting_trx_rows_modified: 0
33.                  waiting_pid: 33
34.                waiting_query: update city  set name='girl' where id=1
35.              waiting_lock_id: 140190433225944:16:6:2:140190349859736
36.            waiting_lock_mode: X,REC_NOT_GAP
37.              blocking_trx_id: 8994   ##阻塞者事务ID
38.                 blocking_pid: 32     ##阻塞者进程ID, show processlist可查;
39.               blocking_query: NULL
40.             blocking_lock_id: 140190433226752:16:6:2:140190349865536
41.           blocking_lock_mode: X,REC_NOT_GAP
42.         blocking_trx_started: 2022-12-23 15:59:41
43.             blocking_trx_age: 00:03:08
44.     blocking_trx_rows_locked: 1
45.   blocking_trx_rows_modified: 0
46.      sql_kill_blocking_query: KILL QUERY 32
47. sql_kill_blocking_connection: KILL 32   ##解锁方法
48. 1 row in set (0.00 sec)

复制代码

3.3 查看进程ID为32的进程，无法显示当前执行的SQL语句

1. mysql> show processlist;
2. +----+-----------------+-----------------+--------------------+---------+-------+------------------------+-----------------------------------------+
3. | Id | User            | Host            | db                 | Command | Time  | State                  | Info                                    |
4. +----+-----------------+-----------------+--------------------+---------+-------+------------------------+-----------------------------------------+
5. |  5 | event_scheduler | localhost       | NULL               | Daemon  | 27235 | Waiting on empty queue | NULL                                    |
6. | 29 | root            | localhost:43074 | information_schema | Query   |     0 | init                   | show processlist                        |
7. | 32 | root            | localhost:43080 | world              | Sleep   |   248 |                        | NULL                                    |
8. | 33 | root            | localhost:43082 | world              | Query   |   112 | updating               | update city  set name='girl' where id=1 |
9. +----+-----------------+-----------------+--------------------+---------+-------+------------------------+-----------------------------------------+
10. 4 rows in set (0.00 sec)

复制代码

3.4 查看进程ID为32的进程对应的SQL线程ID

1. mysql> select thread_id,processlist_id from performance_schema.threads where processlist_id=32;
2. +-----------+----------------+
3. | thread_id | processlist_id |
4. +-----------+----------------+
5. |        75 |             32 |
6. +-----------+----------------+
7. 1 row in set (0.00 sec)

复制代码

找出SQL线程ID为75
3.5 根据线程ID 75，找到真正执行的SQL语句

1. mysql> select thread_id,sql_text from performance_schema.events_statements_history where thread_id=75\G
2. *************************** 1. row ***************************
3. thread_id: 75
4. sql_text: NULL
5. *************************** 2. row ***************************
6. thread_id: 75
7. sql_text: NULL
8. *************************** 3. row ***************************
9. thread_id: 75
10. sql_text: NULL
11. *************************** 4. row ***************************
12. thread_id: 75
13. sql_text: show tables
14. *************************** 5. row ***************************
15. thread_id: 75
16. sql_text: set autocommit=0
17. *************************** 6. row ***************************
18. thread_id: 75
19. sql_text: begin
20. *************************** 7. row ***************************
21. thread_id: 75
22. sql_text: select * from world.city where id=1 for update
23. *************************** 8. row ***************************
24. thread_id: 75
25. sql_text: NULL
26. *************************** 9. row ***************************
27. thread_id: 75
28. sql_text: show databases
29. *************************** 10. row ***************************
30. thread_id: 75
31. sql_text: show tables
32. 10 rows in set (0.00 sec)

复制代码

找到select * from world.city where id=1 for update语句，确认后如果没问题可以kill掉。
3.6 处理锁源SQL对应的连接线程。

1. kill  32;

复制代码

3.7 通过设置回滚申请锁的事务的时间，让处于等待的事务回滚，解决锁冲突。

1. set innodb_lock_wait_timeout=500;  #设置回滚申请锁的事务的时间。

复制代码

4.innodb_lock_wait_timeout参数

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；
参数的时间单位是秒，最小可设置为1s，最大可设置1073741824秒（34年）,默认安装时这个值是50s.
当锁等待超过设置时间的时候，就会报如下的错误；

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

复制代码

参数支持范围为session和global，且支持动态修改，所以可以通过两种方法修改；
1.通过语句修改

1. set innodb_lock_wait_timeout=50;
2. set global innodb_lock_wait_timeout=50;
3. 注意global的修改对当前线程是不生效的，只有建立新的连接才生效

复制代码

2.修改参数文件/etc/my.cnf

1. innodb_lock_wait_timeout = 50

复制代码

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！