MySQL - 锁等待及死锁初探
文章目录
- 生猛干货
- 版本信息
- MySQL 行锁分析
- MySQL死锁演示
- 排查过程
- 查看近期死锁日志信息
- 查询锁等待命令及kill 锁
- 优化建议
- 搞定MySQL
生猛干货
带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试
版本信息
mysql> select version();+-----------+| version() |+-----------+| 5.7.28 |+-----------+1 row in setmysql>
MySQL 行锁分析
mysql> show status like'innodb_row_lock%';+-------------------------------+--------+| Variable_name | Value |+-------------------------------+--------+| Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 222821 || Innodb_row_lock_time_avg | 27852 || Innodb_row_lock_time_max | 51017 || Innodb_row_lock_waits | 8 |+-------------------------------+--------+5 rows in setmysql>
变量说明：
- Innodb_row_lock_current_waits 当前正在等待锁定的数量 单位毫秒
- Innodb_row_lock_time 从系统启动到现在锁定总时间长度 单位毫秒
- Innodb_row_lock_time_avg 每次等待所花平均时间 单位毫秒
- Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花时间 单位毫秒
- Innodb_row_lock_waits 系统启动后到现在总共等待的次数
重点关注 ： Innodb_row_lock_time_avg 、Innodb_row_lock_waits 、Innodb_row_lock_time
MySQL死锁演示
事务隔离级别， 默认 可重复读
mysql> show variables like '%tx_isolation%';+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+1 row in setmysql>
【操作步骤】
| session1 | session2 |
|---|---|
| begin 模拟开启事务 | |
| select from art_info where id=1 for update; | |
| begin 模拟开启事务 | |
| select from account where id=2 for update; | |
| select from art_info where id=2 for update; ——>一直等待 | |
| select from art_info where id=1 for update; —> Deadlock found when trying to get lock; try restarting transaction |
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁
排查过程
【模拟锁等待 】
| session1 | session2 |
|---|---|
| begin 模拟开启事务 | |
| select from art_info where id=1 for update; | |
| begin 模拟开启事务 | |
| select from account where id=2 for update; | |
| select * from account where id=2 for update; |
mysql> select * from art_info where id =2 for update ;1205 - Lock wait timeout exceeded; try restarting transaction
-- 查看事务select * from information_schema.INNODB_TRX;-- 查看锁select * from information_schema.INNODB_LOCKS;-- 查看锁等待select * from information_schema.INNODB_LOCK_WAITS;-- 锁释放 information_schema.INNODB_TRX 查询 trx_mysql_thread_id 然后去 kill 对应的valuekill trx_mysql_thread_id
来吧 ，用上面的SQL查吧
查看近期死锁日志信息
show engine innodb status \G;
查询锁等待命令及kill 锁
-- 查看事务select * from information_schema.INNODB_TRX;-- 查看锁select * from information_schema.INNODB_LOCKS;-- 查看锁等待select * from information_schema.INNODB_LOCK_WAITS;-- 锁释放 information_schema.INNODB_TRX 查询 trx_mysql_thread_id 然后去 kill 对应的valuekill trx_mysql_thread_id
锁等待有自己的超时时间，超过后一般都会自动释放
mysql> select * from art_info where id =2 for update ;1205 - Lock wait timeout exceeded; try restarting transaction
优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少检索条件范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
- 尽可能低级别事务隔离
搞定MySQL
╰半夏微凉°
￡神魔★判官ぃ
不念不忘少年蓝@
向右看齐
还没有评论，来说两句吧...