mysql并发插入死锁_并发插入引起的死锁问题排查

最新推荐文章于 2025-07-15 00:48:34 发布

原创最新推荐文章于 2025-07-15 00:48:34 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql并发插入死锁

本文讲述了2017年春节期间线上系统出现死锁的问题排查过程。最终发现是由客户端发送大量重复请求导致，并且应用程序未进行幂等性设计，使得所有请求均落在数据库上，引发了MySQL事务隔离级别为读提交的并发问题。

2017大年初一晚上,线上报死锁了..大家挂着×××排查问题,也是有点意思.不过当时并没有排查出原因上班之后,发现是客户端的一个bug.本来应该发送一个请求,但是却发送了大量的请求,应用层面又没有做幂等设计,所以所有的请求都落到了数据库层面。数据库是一个过程环境MySQL 5.6.14事务隔离级别读提交引起问题的逻辑大致如下:if not exists (select UserID from award_free_firecracker_watch_common where UserID=pUserID and AwardDate=pToday and GiftID=pGift) then

insert intoaward_free_firecracker_watch_common(UserID,AwardDate,GiftID) values (pUserID,pToday,pGift);

end if;

select AwardTotal into pOld from award_free_firecracker_common where UserID=pUserID and GiftID=pGift for update;

updateaward_free_firecracker_commonsetAwardTotal=AwardTotal+1,AwardLeft=AwardLeft+1whereUserID=pUserIDandGiftID=pGift;

SELECT ROW_COUNT() into pUpdateCount;

if pUpdateCount>0 then

updateaward_free_firecracker_watch_commonsetWatchCount=WatchCount+1whereUserID=pUserIDandAwardDate=pTodayandGiftID=pGift;

end if;

因为这个过程也不是我写的,大致的意思是先查询award_free_firecracker_watch_common表，如果没有这个用户的记录,则插入.然后修改award_free_firecracker_common的记录,最后在修改award_free_firecracker_watch_common的记录.MySQL的加锁过程https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Session 1:Session 2:

Session 3: