MySQL产生死锁？！不要慌，问题不大《上》

LBruse
203 阅读7分钟

简介

最近查看一些系统运行错误日志，发现不少错误都是因为MySQL事务提交失败，原因是锁等待、锁超时之类的。花了一个下午和团队把各种各样的业务SQL捋了一遍，发现团队SQL水平还是有待提高。。复盘问题的同时，也做一下知识总结。希望能帮助你在日常开发中避免出现此类情况。毕竟生平不识数据库，便称后端也枉然。

死锁示例

我所用的MySQL版本为8.0，创建一张user用户表。

准备工作

CREATE TABLE `user` (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机号',
  `nickname` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
  `real_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '真实姓名',
  `show_which_name` int NOT NULL DEFAULT '2' COMMENT '对外展示名，1：真实姓名，2：昵称',
  `sex` int NOT NULL COMMENT '性别，1:男 0:女 2:保密',
  `created_time` datetime NOT NULL COMMENT '创建时间',
  `updated_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'

添加一些数据。

INSERT INTO `user` VALUES ('1534756548502724610', '13961884811', '123abcde', '用户：13******846', 2, 1, '2022-06-09 12:37:30', '2022-06-09 12:37:30');
INSERT INTO `user` VALUES ('1534766306928394242', '13811112222', '夜影恶魔', '与一塌糊涂', 1, 0, '2022-06-09 13:16:17', '2022-06-27 13:46:48');
INSERT INTO `user` VALUES ('1536563316312514561', '13822223333', '用户：13******699', '用户：13******699', 2, 2, '2022-06-14 12:16:57', '2022-06-27 12:53:16');
INSERT INTO `user` VALUES ('1541299724478681090', '13812345678', '夜影恶mo', '用户：13******678', 2, 1, '2022-06-27 13:57:45', '2022-07-26 16:14:20');

产生死锁

创建第一个MySQL会话,执行语句。

事务1
begin;
select * from user where mobile = '13961884811' for update ;

此时事务1拥有mobile = '13961884811'这条记录的锁。

image.png

创建第二个MySQL会话,执行语句。

事务2
begin;
select * from user where mobile = '13811112222' for update ;

此时事务2拥有mobile = '13811112222' 这条记录的锁。

image.png

切换到事务1,执行语句。

事务1
select * from user where mobile = '13811112222' for update ;

此时由于事务2还没commit，依然占有相应记录的锁。所以事务1的查询进入锁等待状态。

image.png

接着切换到事务2,执行语句。

select * from user where mobile = '13961884811' for update ;

形成循环依赖关系,产生死锁。因为开启了MySQL的死锁检测,所以事务2的查询抛出异常。

image.png

待事务2抛出异常，释放锁后，事务1的查询不再阻塞，成功返回查询结果。

image.png

这里还有个情形是，事务1在查询mobile = '13811112222'的记录时, 事务2没有尝试查询mobile = '13961884811'这条记录，但同时也没有commit。那么事务1在等待一定时间后，会触发锁等待超时，抛出异常，结束事务。

image.png

Tips

这里需要注意的是我在select语句中加上了for update。如果没有for update，再来执行上边的select+select组合流程，你会发现并不会产生锁占用与等待的哈。当然也可以换成select + update组合，或update + update组合的形式来产生锁占用与等待。

读读不互斥，读写互斥，select...for update类似于给读操作也加上了写锁。

这也是为什么select，update，delete尽量走索引的原因，锁住一行记录或几行记录还好说，没走索引，全表扫描的情况下，会大大增加表中数据操作产生锁等待，死锁的概率。

InnoDB解决死锁的方式

死锁检测

我们可以通过如下语句查看死锁检测是否开启,ON=开启 OFF=关闭。开启之后，当MySQL检测到死锁循环依赖时,会立即返回一个错误。

show global variables like 'innodb_deadlock_detect';

image.png

如果你的MySQL没有开启死锁检测，则需要先进行开启。

SET GLOBAL innodb_deadlock_detect = ON;

或者编辑MySQL配置文件，保存并重启MySQL

   [mysqld]
   innodb_deadlock_detect = ON

锁等待超时

可以通过如下语句查看锁等待超时时间设置，单位秒。等查询的时间达到锁等待超时时间后，就会放弃锁请求，避免形成长时间锁等待。

show global variables like 'innodb_lock_wait_timeout';

image.png

设置锁等待超时,在高并发的业务场景下，明显是不能接受的。一般更倾向于开启死锁检测，虽然这样对MySQL来说就会有额外的死锁检测开销。可以尽量在业务代码中保证一定不会出现死锁，以及把业务放到使用Redis，MQ等中间件上进行，避免压力落到MySQL。

优化事务粒度

即尽量不要在一个事务里执行太多SQL操作，保持事务简短，越早commit越好。

比如像上边的示例，原先这样的

begin;
select * from user where mobile = '13961884811' for update ;
select * from user where mobile = '13811112222' for update ;
commit;

进行相应优化后

begin;
select * from user where mobile = '13961884811' for update ;
commit;

Keep it simple

Tips：尽量保证事务越早提交越好。还有一种糟糕的事务使用场景即 业务代码开启了长事务，导致事务迟迟未释放！

优化SQL执行顺序

可以通过调整SQL的顺序来避免产生死锁。

原先的情况：

事务1占有记录1的锁，再尝试获取记录2的锁。

事务2占有记录2的锁，再尝试获取记录1的锁。

事务1
begin;
select * from user where mobile = '13961884811' for update ;
select * from user where mobile = '13811112222' for update ;
commit;

事务2
begin;
select * from user where mobile = '13811112222' for update ;
select * from user where mobile = '13961884811' for update ;
commit;

只需要对事务2的SQL顺序稍作调整，就能打破形成循环依赖的条件

事务2--调整后
begin;
select * from user where mobile = '13961884811' for update ;
select * from user where mobile = '13811112222' for update ;
commit;

这样一来，在事务1占有mobile = '13961884811'这条记录的锁时，开启事务2并尝试获取mobile = '13961884811'的记录锁时，就会进入锁等待状态。能避免形成死锁，但是有一定可能产生锁等待超时。

碍于业余时间编写文章的精力有限，以及个人觉得一篇文章塞下太多内容反而不太好，所以这里只是写了部分关于MySQL事务、锁的相关知识，下一篇文章继续~

avatar
文章
阅读
粉丝