MySQL锁等待与死锁问题分析

MySQL锁等待与死锁问题分析

前言：

在 MySQL 运维过程中，锁等待和死锁问题是令各位 DBA 及开发同学非常头痛的事。出现此类问题会造成业务回滚、卡顿等故障，特别是业务繁忙的系统，出现死锁问题后影响会更严重。本篇文章我们一起来学习下什么是锁等待及死锁，出现此类问题又应该如何分析处理呢?

1.了解锁等待与死锁

出现锁等待或死锁的原因是访问数据库需要加锁，那你可能要问了，为啥要加锁呢?原因是为了确保并发更新场景下的数据正确性，保证数据库事务的隔离性。

试想一个场景，如果你要去图书馆借一本《高性能MySQL》，为了防止有人提前把这本书借走，你可以提前进行预约(加锁)，这把锁可以怎么加?

封锁图书馆(数据库级别的锁)把数据库相关的书都锁住(表级别的锁)只锁 MySQL 相关的书(页级别的锁)只锁《高性能MySQL》这本书(行级别的锁)

锁的粒度越细，并发级别越高，实现也更复杂。

锁等待也可称为事务等待，后执行的事务等待前面处理的事务释放锁，但是等待时间超过了 MySQL 的锁等待时间，就会引发这个异常。等待超时后的报错为“Lock wait timeout exceeded...”。

死锁发生的原因是两个事务互相等待对方释放相同资源的锁，从而造成的死循环。产生死锁后会立即报错“Deadlock found when trying to get lock...”。

2.现象复现及处理

下面我们以 MySQL 5.7.23 版本为例(隔离级别是 RR )，来复现下上述两种异常现象。

mysql> show create table test_tb\G *************************** 1. row ***************************        Table: test_tb Create Table: CREATE TABLE `test_tb` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `col1` varchar(50) NOT NULL DEFAULT '',   `col2` int(11) NOT NULL DEFAULT '1',   `col3` varchar(20) NOT NULL DEFAULT '',   PRIMARY KEY (`id`),   KEY `idx_col1` (`col1`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)  mysql> select * from test_tb; +----+------+------+------+ | id | col1 | col2 | col3 | +----+------+------+------+ |  1 | fdg  |    1 | abc  | |  2 | a    |    2 | fg   | |  3 | ghrv |    2 | rhdv | +----+------+------+------+ 3 rows in set (0.00 sec)  # 事务一首先执行 mysql> begin; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test_tb where col1 = 'a' for update; +----+------+------+------+ | id | col1 | col2 | col3 | +----+------+------+------+ |  2 | a    |    2 | fg   | +----+------+------+------+ 1 row in set (0.00 sec)  # 事务二然后执行 mysql> begin; Query OK, 0 rows affected (0.01 sec)  mysql> update test_tb set col2 = 1 where col1 = 'a'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

出现上种异常的原因是事务二在等待事务一的行锁，但事务一一直没提交，等待超时而报错。InnoDB 行锁等待超时时间由 innodb_lock_wait_timeout 参数控制，此参数默认值为 50 ，单位为秒，即默认情况下，事务二会等待 50s ，若仍拿不到行锁则会报等待超时异常并回滚此条语句。

对于 5.7 版本，出现锁等待时，我们可以查看 information_schema 中的几张系统表来查询事务状态。

innodb_trx 当前运行的所有事务。innodb_locks 当前出现的锁。innodb_lock_waits 锁等待的对应关系

sys.innodb_lock_waits 视图整合了事务等待状况，同时给出杀掉堵塞源端的 kill 语句。不过是否要杀掉链接还是需要综合考虑的。

死锁与锁等待稍有不同，我们同样也来简单复现下死锁现象。

# 开启两个事务 # 事务一执行 mysql> update test_tb set col2 = 1 where col1 = 'a'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0  # 事务二执行 mysql> update test_tb set col2 = 1 where id = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0  # 回到事务一执行 回车后 此条语句处于锁等待状态 mysql> update test_tb set col1 = 'abcd' where id = 3; Query OK, 1 row affected (5.71 sec) Rows matched: 1  Changed: 1  Warnings: 0  # 回到事务二再执行 此时二者相互等待发生死锁 mysql> update test_tb set col3 = 'gddx' where col1 = 'a'; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

发生死锁后会选择一个事务进行回滚，想查明死锁原因，可以执行 show engine innodb status 来查看死锁日志，根据死锁日志，结合业务逻辑来进一步定位死锁原因。

在实际应用中，我们要尽量避免死锁现象的发生，可以从以下几个方面入手：

事务尽可能小，不要讲复杂逻辑放进一个事务里。涉及多行记录时，约定不同事务以相同顺序访问。业务中要及时提交或者回滚事务，可减少死锁产生的概率。表要有合适的索引。可尝试将隔离级别改为 RC 。

总结：

本篇文章简单介绍了锁等待及死锁发生的原因，其实真实业务中发生死锁还是很难分析的，需要一定的经验积累。本篇文章只是面向初学者，希望各位对死锁能够有个初印象。

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