生猛干货
带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试
版本信息
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.28 | +-----------+ 1 row in set mysql>
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 set mysql>
变量说明：
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 set mysql>
【操作步骤】
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁
排查过程
【模拟锁等待 】
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 对应的value kill 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 对应的value kill trx_mysql_thread_id
锁等待有自己的超时时间，超过后一般都会自动释放
mysql> select * from art_info where id =2 for update ; 1205 - Lock wait timeout exceeded; try restarting transaction
优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少检索条件范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
- 尽可能低级别事务隔离
搞定MySQL
https://artisan.blog.csdn.net/article/details/107713456?spm=1001.2014.3001.5502
