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

文章导航-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. 逐条更新
...