一个数据库死锁的案例

昨天一个项目的生产环境出现了数据库死锁问题，导致死锁的订单号已经提交给第三方，但是由于出错回滚到该订单号未记录的状态，结果后续的单子使用的单号仍以该单号开始，这在第三方看来不是新单，而是旧单重复调用接口，就报权限错误；即后续所有的新单都卡在这里。这是一个很严重的bug，自增单号的逻辑显然有问题，但是这里看一下死锁问题。

查看死锁日志后，发现涉及到死锁的应该是两张表，表1是关联表，一般根据order_id和customer_id查询

CREATE TABLE `rel_credit_customer_files` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`order_id` varchar(32) DEFAULT NULL COMMENT '',
`customer_id` varchar(32) DEFAULT NULL COMMENT '',
`file_id` varchar(32) DEFAULT NULL COMMENT '',PRIMARY KEY (`id`),
KEY `pk_order_customer` (`customer_id`,`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8mb4 COMMENT='客户图片关联表啊';

表2是第三方订单信息表，order_id是本家订单号，vx_order_id是第三方订单号。

CREATE TABLE `biz_credit_order_info` (
  `order_id` varchar(32) NOT NULL COMMENT ' 订单号',
  `vx_order_id` varchar(50) DEFAULT NULL COMMENT '第三方返回的订单号',
  `withdraw_time` datetime DEFAULT NULL COMMENT '退回时间',PRIMARY KEY (`order_id`) USING BTREE,
  KEY `pk_order_id` (`order_id`) USING BTREE,
  KEY `pk_bank` (`bank`),
  KEY `pk_institution` (`institution_id`),
  KEY `bank_code` (`bank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='征信订单信息表';

涉及到的语句很多，查看死锁日志后锁定了关键的语句，事务1

replace into biz_credit_order_info(order_id,vx_order_id,withdraw_time) values('2020xxx20','vx001011003753200319030116352','2020-09-09 09:09:09');

delete from rel_credit_customer_files where order_id='
2020xxx20
' and customer_id=20;

insert into rel_credit_customer_files(
customer_id,
file_id,
order_id,
customer_name
) values(20,1,'2020xxx20','张三');

事务2

delete from rel_credit_customer_files where order_id='
2020xxx08
' and customer_id=8;
insert into rel_credit_customer_files(
customer_id,
file_id,
order_id,
customer_name
) values(8,2,'2020xxx08','张三');
update biz_credit_order_info
vx_order_id = 'vx001011003753187396677275648',
withdraw_time ='2020-09-09 10:25:29'
where order_id = '2020xxx08' or vx_order_id = 'vx001011003753187396677275648';

这两个事务是由两个不同的方法执行的（新增和更新），由于疏忽，语句的执行顺序反了，这就为死锁埋了祸根。

rel_credit_customer_files表的两条语句只用看删除的语句，忽略其新增语句（新增语句对死锁形成原因没有影响）

事务1	事务2
begin;	begin;
replace into biz_credit_order_info(order_id,vx_order_id,withdraw_time) values('2020xxx20','vx001011003753200319030116352','2020-09-09 09:09:09'); （Affected rows: 2）
	delete from rel_credit_customer_files where order_id=' 2020xxx08 ' and customer_id=8;（Affected rows: 2）
delete from rel_credit_customer_files where order_id=' 2020xxx20 ' and customer_id=20;(被阻塞)
（Deadlock found when trying to get lock; try restarting transaction）	update biz_credit_order_info vx_order_id = 'vx001011003753187396677275648', withdraw_time ='2020-09-09 10:25:29' where order_id = '2020xxx08' or vx_order_id = 'vx001011003753187396677275648'; （ Affected rows: 0 ）

 

问题在于sql语句写的不严谨导致改语句出现了锁表情况，第一步应该加了next_key锁。第二步的删除语句：本意是根据非唯一索引删除，但是customer_id 为int值与类型不符，发生类型转化，原本走索引变成全表扫描，结果锁所有记录（日志中持有136条记录锁）。第三步的删除操作申请所有记录的锁被阻塞。第四步更新操作有 or操作，正好vx_order_id没有索引，会进行全表扫描（explain它还是会走主键索引），会申请第一步中的记录锁而阻塞，于是发生死锁。发生死锁的删除语句和更新语句虽然不是操作同一条记录，但没有走索引导致可能锁所有行，而两个事务的sql执行顺序也是相反的，导致互相等待发生死锁。以上死锁情况如果是事务2先执行第一步同样会死锁。

将删除语句的数据类型变更/更新语句的vx_order_id字段加索引/更改语句执行顺序，均可以避免死锁。死锁日志提供的信息有限，还需要根据具体的sql执行以及分析语句的索引使用情况信息，才好找出问题。