Mysql从入门到精通-[INFO ] Deadlock found when trying to get lock; try restarting transaction

[INFO ] 2020-05-07 15:51:16,810 [SimpleAsyncTaskExecutor-6] c.c.a.a.s.mq.consumer.RabbitReceiver - A9-ATP_DRAW_SUCC_QUEUE deliveryTag : [69], 消费失败：[org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [UPDATE `act_activity_record` SET `act_id`=?, `act_prize_id`=?, `openid`=?, `nickname`=?, `headimgurl`=?, `realname`=?, `phone`='1', `address`=?, `user_id`=?, `prizes_name`=?, `prizes_status`=?, `code`=?, `recieve_status`=?, `recieve_time`=?, `verify_id`=?, `mq_msgid`=?, `mq_status`=?, `num_before`=?, `num_after`=? WHERE seq=?]; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

什么情况下会出现Deadlock found when trying to get lock？
https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks.html
出现死锁需要2个条件：
1）至少2个client（A，B）同时在执行事务
2）clientA锁定了某一行，未提交事务，此时clientB也需要update/delete这一行，此时clientB就会进入等待状态，直到出现Deadlock 。

如何减少死锁的发生？
很重要的两点，就可以避免这种情况
1）事务操作锁定的行数较少（更精确的索引条件）。
2）保证事务较短的执行时间，完成后马上提交。
这里有更加详细的描述：
https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks-handling.html

 

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

 

出现这个原因要记住一点就是：innodb的行锁 和解锁都是针对主键索引的。如果查询时根据索引锁表，但更新时却不是通过主键更新，那么等待的解锁查询的进程将会报1213错误，程序里有可能返回一个null值

 

 

摘要
某些场景会有这样的需求：无记录则插入，有记录则更新。例如：新增用户，以身份证号码作为唯一身份标识，插入时若先查询是否存在记录再决定插入还是更新，在高并发情况下必然存在问题。本文提供三种解决方案。

方案一 加锁
无论通过synchronized锁、ReentranLock锁还是分布式锁，都可以解决该问题。缺点是，加锁会影响性能。方法二和三都是数据库层面解决方案，个人感觉比方法一好一些。

方案二 Unique和Replace Into … SELECT …
首先对唯一性的字段添加唯一索引ALTER TABLE tb_name ADD UNIQUE (col1、col2...)，，通过唯一索引即可保证数据的唯一性。

加入唯一索引后，通过INSERT INTO插入相同数据就会报错，此时需要使用REPLACE INTO插入数据，用法是一样的。通过REPLACE INTO插入数据时，若存在相同数据，会将之前的记录删除，再重新插入数据。缺点是，存在先删除再插入的过程，sql需要考虑全部数据列，不然会丢失部分列的数据。缺点是，建立唯一索引会影响插入效率。下面是具体的例子。

# 建立索引
ALTER TABLE user ADD UNIQUE (id_card);
# 假设user表只有id,name,id_card三个字段，且id字段自增。
# 现在需要插入name=ly，id_card=142733的记录。
# 但是，若之前存在id_card=142733的记录，修改name=ly即可。
REPLACE INTO user (id,name,id_card) 
SELECT id,'ly',142733 FROM user RIGHT JOIN (SELECT 1) AS tab 
ON user.id_card = 142733;

通过RIGHT JOIN (SELECT 1)，若存在id_card=142733的记录，执行sql后会将原始id保存在临时的结果集中，随name和id_card一同插入。若不存在该记录，则将null作为id随name和id_card一同插入。最终实现

方案三 通过预插入语句判断是否存在记录
通过预插入语句，尝试插入，判断修改的记录是否大于0，若大于0表示插入成功，若为0则表示记录已存在，需要执行更新操作。

# 预插入
INSERT INTO user (name,id_card)
SELECT 'ly',142733 FROM DUAL 
WHERE NOT EXISTE (SELECT id_card FROM user WHERE id_card = 142733) ;
# 若预插入语句插入成功（修改记录数=1），则无需后续操作。否则执行更新操作。
UPDATE user SET name = 'ly' WHERE id_card = 142733;

通过NOT EXISTE条件，若存在id_card=142733的记录则伪表DUAL记录为空，预插入语句修改记录为0，此时需要执行更新操作。若不存在id_card=142733的记录，则伪表DUAL记录为一行且内容是'ly',142733，预插入语句修改记录为1，此时不必执行更新语句。