记录那些诡异的数据库死锁

1,805 阅读3分钟

场景一:多条事务引起(不易排查, 事务隔离级别RR和RC都会出现)

场景描述

有一个每日交易任务,当交易额度满足一定数量时,就可以增加一次任务完成记录(user_no和trade_date组成唯一索引);实现流程如下:

1.当有交易来的时候,先查询当天该用户是否已经发生过交易了;
模拟sql: select xx1,xx2 from trade where user_no = vv3 and trade_date = vv4

2.如果当前交易不存在则进行插入或更新(主要插入时可能会有并发产生),如果存在就直接更新;
模拟sql:insert into trade(xx1,xx2,xx3,xx4) values(vv1,vv2,vv3,vv4);执行需要捕捉duplicate异常
插入异常后执行更新;

3.更新操作使用悲观锁(for update)进行锁定后再做修改;
锁定是为了获取最新交易额,来判定是否需要添加完成次数,否则乐观锁即可;也就不会出现死锁;
模拟锁定sql: select xx1,xx2 from trade where user_no = vv3 and trade_date = vv4 for update;
模拟修改sql: update trade set xx1=vv1,xx2=vv2 where user_no = vv3 and trade_date = vv4;

建立模型实操模拟

  • 建立模拟表如下:
create table lock_test1(
    id int unsigned primary key auto_increment comment '自增主键',
    biz_id varchar(10) not null comment '业务编号',
    unique KEY uniq_biz_id (biz_id)
) engine=InnoDB DEFAULT CHARSET=utf8mb4 comment='锁定场景1演示表';
  • 建立三个session连接并开启事务(以下按时间线执行)

第一个事务开启和执行如下:

-- 事务一
start transaction;
select id, biz_id from lock_test1 where biz_id = 'test1';
-- 此时发现没有任何数据,紧接着进行插入:
insert into lock_test1(biz_id) values ('test1');
-- 于此同时事务二三也执行相同步骤

第二个事务开启和执行如下:

-- 事务二
start transaction;
select id, biz_id from lock_test1 where biz_id = 'test1';
-- 此时发现没有任何数据,紧接着进行插入(稍晚于事务一):
insert into lock_test1(biz_id) values ('test1');

-- 于此同时事务一三也执行相同步骤

第三个事务开启和执行如下:

-- 事务三
start transaction;
select id, biz_id from lock_test1 where biz_id = 'test1';
-- 此时发现没有任何数据,紧接着进行插入(稍晚于事务一):
insert into lock_test1(biz_id) values ('test1');
-- 于此同时事务一二也执行相同步骤

此时第一个事务commit

-- 事务一
commit;
-- 此时事务一已经处理完毕

事务二和事务三都会抛出(duplicate的异常),此时进入update流程;

-- 事务二
select id, biz_id from lock_test1 where biz_id = 'test1' for update;

-- 事务三
select id, biz_id from lock_test1 where biz_id = 'test1' for update;

然后就出现Deadlock found when trying to get lock;try restarting transaction的信息;
也就是说出现了死锁

死锁原因分析

根据时间线分析: 事务一首先拿到写锁(X), 此时事务二和三过来试图进行插入操作,首先获取写意向锁(IX);
当事务一插入后commit后,事务二三都出现duplicate的异常,此时写意向锁(X)转换成行级读共享锁(S);
此时事务二和事务三都执行for update想要获取行级排他锁(X), 但是锁X的获取条件为所有关于该行的
其他session的任何锁全部释放;所以就出现了事务二想要获取X锁,需要等待事务三的共享锁(S)释放;
事务三想要获取X锁也需要等待事务二的共享锁(S)释放, 所以就导致了此次的死锁;

所以究其原因就是在这种情况下只有3个或以上的事务同时处理该逻辑才会出现死锁。

关于innodb的锁模式的简单介绍文章:yq.aliyun.com/articles/69…
官方关于innodb的锁的模式介绍文章:dev.mysql.com/doc/refman/…

该死锁的解决方案

将for update替换为乐观锁,也就是在where条件后面加上一些期望值;但不符合当前业务;
将此业务的事务隔离级别设置为Serializable,串行执行;
使用分布式锁,和上面串行思想一样;

场景二:使用for update锁定不存在记录导致死锁(隔离级别RR)

未完待续...