MySQL8.0锁情况排查
- 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
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhfjhhhj
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
photoshop蒙版画笔没反应怎么办
PHP中文网 06-24