Mysql中update后insert造成死锁的分析
问题描述
sql如下：
1START TRANSACTION;
2UPDATE table_a SET ... WHERE id = x ;
3IF(ROW_COUNT() = 0) THEN
4 INSERT INTO table_a id VALUES x;
5END IF;
6COMMIT;
其中id为主键。
平均⼀天有不到10次的死锁。
排查过程
⾸先查看程序⽇志，发现死锁都只有新⽤户⾸次登录时才出现。也就是说，update时发现数据库中并没有相应的⾏，所以会进⾏接下来的
插⼊操作，这时发⽣了死锁。
然后，查询了innodb的⽇志，这⾥贴出关键的部分。
1------------------------
2LATEST DETECTED DEADLOCK
3------------------------
42018-02-02 23:35:03 7fe7f03ff700
5*** (1) TRANSACTION:
6TRANSACTION 72155984, ACTIVE 0 sec inserting
7mysql tables in use 1, locked 1
8LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
9MySQL thread id 1279838, OS thread handle 0x7fe803bff700, query id 988205122 172.16.0.123 acspassport update
10INSERT INTO table_a id VALUES x
11*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
12RECORD LOCKS space id 85 page no 3763 n bits 184 index `PRIMARY` of table `accountdb`.`last_login_openid` trx id 72155984 lock_mode X insert intenti 13Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
14 0: len 8; hex 73757072656d756d; asc supremum;;
15
16*** (2) TRANSACTION:
17TRANSACTION 72155983, ACTIVE 0 sec inserting
18mysql tables in use 1, locked 1
19 3 lock struct(s), heap size 1184, 2 row lock(s)
20MySQL thread id 1248122, OS thread handle 0x7fe7f03ff700, query id 988205121 172.16.0.123 acspassport update
21INSERT INTO table_a id VALUES x
22*** (2) HOLDS THE LOCK(S):
23RECORD LOCKS space id 85 page no 3763 n bits 184 index `PRIMARY` of table `accountdb`.`last_login_openid` trx id 72155983 lock_mode X
24Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
25 0: len 8; hex 73757072656d756d; asc supremum;;
26
27*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
28RECORD LOCKS space id 85 page no 3763 n bits 184 index `PRIMARY` of table `accountdb`.`last_login_openid` trx id 72155983 lock_mode X insert intenti 29Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
30 0: len 8; hex 73757072656d756d; asc supremum;;
31
32*** WE ROLL BACK TRANSACTION (2)
从两个transaction的WAITING FOR THIS LOCK TO BE GRANTED
可以看出两个transaction都在insert申请insert intent X lock 时等待，从⽽导致死锁。
总结出精简后的导致死锁的过程如下，可以轻松的使⽤控制台复现。
transaction A transaction B begin
begin update row a失败
update row b失败insert row a等待
insert row b等待死锁发⽣
成因
innodb不是⾏锁吗，为什么会发⽣死锁呢?
这⾥就涉及到innodb的锁机制了，innodb使⽤了Repeatable Read(RR)的隔离级别。在此级别下，innodb为了防⽌幻读(Phantom Rows)，在实现上使⽤了。并且在search和scan的时候使⽤(record lock + gap lock)，但是对于在主键或唯⼀索引上进⾏查的时候仅使⽤record lock。这⾥有⼀个例外，即当使⽤主键不到的时候该记录的时候，则在该区间加gap lock。这次死锁的就是由这个例外情况引起的。
下⾯根据以上原理分析本次死锁的成因。
transaction A 锁的情况transaction B 锁的情况
begin begin
update row a(a>x)失败
区间（x，正⽆穷）gap lock
update row b(b>x)失败
区间（x，正⽆穷）gap lock（gap lock之间不互斥）
sql中update什么意思insert row a等待
insert intention lock等待（gap lock only stop other transactions from inserting to the gap）
insert row b等待insert intention lock 等待
死锁发⽣
根据以上原理，包括
也可能导致死锁。
解决⽅法
根据select结果判断，这⾥有极⼩的概率出现insert duplicate，因为每次多⼀次select，效率肯定不如原来的。如果还有问题可以试试使⽤insert ignore或者insert on duplicate key update。
1select ... where id=x lock in share mode/for update;2if(found_rows()=0)3
insert into id values 1;
1SELECT 1 FROM table_a WHERE id=x; 2IF(FOUND_ROWS() = 0) THEN
3 INSERT INTO table_a id VALUES x; 4ELSE
5 UPDATE table_a SET ... WHERE id=x; 6END IF;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论