05、MySQL进阶：锁等待及死锁初探-程序员快看-教程，程序员编程资料站

文章目录

生猛干货
版本信息
MySQL 行锁分析
MySQL死锁演示
排查过程
查看近期死锁日志信息
查询锁等待命令及kill 锁
优化建议
搞定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>

【操作步骤】

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 对应的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尽量放在事务最后执行
尽可能低级别事务隔离

05、MySQL进阶：锁等待及死锁初探