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. 逐条更新

...

相关文章