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

最新推荐文章于 2024-01-08 04:24:19 发布

little_how

最新推荐文章于 2024-01-08 04:24:19 发布

阅读量142

点赞数

分类专栏：数据库文章标签： innodb锁死锁

本文链接：https://blog.csdn.net/w172087242/article/details/99600939

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

场景一：多条事务引起(不易排查, 事务隔离级别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)释放, 所以就导致了此次的死锁；