MySql 更新死锁问题 Deadlock found when trying to get lock; try restarting transaction

时间:2023-02-06 07:57:23

文章导航-readme

MySql 更新死锁问题 Deadlock found when trying to get lock; try restarting transaction

1.场景

//table1
CREATE TABLE `retailtrades` (
`TradeId` bigint(20) NOT NULL COMMENT '主键',
`TradeCode` varchar(20) NOT NULL COMMENT '交易单号',
`TradeAmount` decimal(14,4) NOT NULL COMMENT '交易金额',
`CreateTime` datetime NOT NULL COMMENT '创建时间',
`TradeState` tinyint(4) NOT NULL COMMENT '交易状态 1:未支付 2:已支付 3:支付异常',
`SuccessTime` datetime DEFAULT NULL COMMENT '支付成功时间',
PRIMARY KEY (`TradeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='零售交易单表'; //table2
CREATE TABLE `retailorders` (
`OrderId` bigint(20) NOT NULL COMMENT '主键',
`OrderCode` varchar(20) NOT NULL COMMENT '订单编号',
`CreateTime` datetime NOT NULL COMMENT '创建时间',
`OrderStatus` tinyint(4) NOT NULL COMMENT '1:待付款 2:待确认 3:待发货 4:待收货 5:已完成 6:已取消',
PRIMARY KEY (`OrderId`),
UNIQUE KEY `OrderCode` (`OrderCode`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='零售订单表';
//sql1
update retailtrades set TradeState=2 where TradeCode='111706022040540002'; //sql2
update retailorders set OrderStatus=2 where FIND_IN_SET(ordercode,'111706022040540001'); //提交方式:组装到hashtable内事务提交 //当并发请求时出现问题:
Deadlock found when trying to get lock; try restarting transaction

2.知识点

  1. mysql innodb引擎支持事务,更新时采用的是行级锁。
  2. 行级锁必须建立在索引的基础
  3. 行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。如果操作用到了主键索引会先在主键索引上加锁,然后在其他索引上加锁,否则加锁顺序相反。
  4. 对于没有用索引的操作会采用表级锁
  5. mysql FIND_IN_SET 函数会全表扫描

3.问题分析

  1. table1 TradeCode字段未设置索引 update语句会锁表
  2. table2 OrderCode字段虽设置索引但使用FIND_IN_SET 作为查询条件 也会锁表
  3. 程序采用hashtable组装sql语句,由于hash执行是无序的,若同时并发两个请求事务执行顺序如下:
事务1 事务2
begin TRANSACTION ...
update retailtrades set TradeState=2 where TradeCode='111706022040540002'; begin TRANSACTION
锁住table1等待table2 update retailorders set orderstatus=2 where FIND_IN_SET(ordercode,'111706030019320003');
... 锁住table2等待table1
update retailorders set orderstatus=2 where FIND_IN_SET(ordercode,'111706022040540001'); UPDATE retailtrades set tradestate=2 where tradecode='111706030019320004';
死锁 事务1死锁处理后,事务2获取锁执行成功

4.问题模拟重现

//sql1
start TRANSACTION;
UPDATE retailtrades set tradestate=2 where tradecode='111706022040540002';
select sleep(5);
update retailorders set orderstatus=2 where FIND_IN_SET(ordercode,'111706022040540001');
COMMIT //sql2
start TRANSACTION ;
update retailorders set orderstatus=2 where FIND_IN_SET(ordercode,'111706030019320003');
SELECT sleep(5);
UPDATE retailtrades set tradestate=2 where tradecode='111706030019320004';
COMMIT //于navicat中打开两个窗口先执行sql1,后执行sql2,查看执行结果

5.问题解决

通过以上分析结果问题最简单暴力的解决方式就是讲hashtable组装sql改为有序集合,但此种解决方式并不能解决以上sql与表的性能问题。

因此建议如下:

  1. table1 TradeCode字段 考虑是否增加索引提高查询更新速度,避免更新锁表
  2. sql2 避免使用FIND_IN_SET(注意:FIND_IN_SET会全表扫描,效率低下,查询的时候尽量也不要使用),可改为in
  3. sql组装方式改为有序集合
  4. 建议update语句使用主键索引作为更新条件

6.问题扩展

CREATE TABLE `user_item` (
`id` BIGINT(20) NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`item_id` BIGINT(20) NOT NULL,
`status` TINYINT(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_1` (`user_id`,`item_id`,`status`)
) ENGINE=INNODB DEFAULT CHARSET=utf-8
update user_item set status=1 where user_id=? and item_id=?
  1. 由于用到了非主键索引,首先需要获取idx_1上的行级锁
  2. 紧接着根据主键进行更新,所以需要获取主键上的行级锁;
  3. 更新完毕后,提交,并释放所有锁。
//如果在步骤1和2之间突然插入一条语句:
update user_item .....where id=? and user_id=?
//这条语句会先锁住主键索引,然后锁住idx_1。
//蛋疼的情况出现了,一条语句获取了idx_1上的锁,等待主键索引上的锁;
//另一条语句获取了主键上的锁,等待idx_1上的锁,这样就出现了死锁。

解决方案

//1.先获取需要更新的记录的主键
select id from user_item where user_id=? and item_id=?
//2. 逐条更新
...