MySQL8.0锁情况排查，你学会了吗？

MySQL8.0锁情况排查，你学会了吗？

在数据库中，除传统的计算资源(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 transactionmysql> 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:42trx_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:41trx_tables_locked: 1 trx_rows_locked: 12 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 32sql_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)

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

找到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，且支持动态修改，所以可以通过两种方法修改；

通过语句修改

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

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

innodb_lock_wait_timeout = 50

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。