转载
作者:红莲之火
链接:https://www.jianshu.com/p/a96ce670c524
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
问题描述
sql如下:
START TRANSACTION;
UPDATE table_a SET ... WHERE id = x ;
IF(ROW_COUNT() = 0) THEN
INSERT INTO table_a id VALUES x;
END IF;
COMMIT;
其中id为主键。
平均一天有不到10次的死锁。
排查过程
首先查看程序日志,发现死锁都只有新用户首次登录时才出现。也就是说,update时发现数据库中并没有相应的行,所以会进行接下来的插入操作,这时发生了死锁。
然后,查询了innodb的日志,这里贴出关键的部分。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-02-02 23:35:03 7fe7f03ff700
*** (1) TRANSACTION:
TRANSACTION 72155984, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 1279838, OS thread handle 0x7fe803bff700, query id 988205122 172.16.0.123 acspassport update
INSERT INTO table_a id VALUES x
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD 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 intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 72155983, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 1248122, OS thread handle 0x7fe7f03ff700, query id 988205121 172.16.0.123 acspassport update
INSERT INTO table_a id VALUES x
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 85 page no 3763 n bits 184 index `PRIMARY` of table `accountdb`.`last_login_openid` trx id 72155983 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD 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 intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** 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),在实现上使用了gap lock。并且在search和scan的时候使用next-key lock(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之间不互斥) | ||
insert row a等待 | insert intention lock等待(gap lock only stop other transactions from inserting to the gap) | ||
insert row b等待 | insert intention lock 等待 | ||
死锁发生 |
根据以上原理,包括
select ... where id=x lock in share mode/for update;
if(found_rows()=0)
insert into id values 1;
也可能导致死锁。
解决方法
根据select结果判断,这里有极小的概率出现insert duplicate,因为每次多一次select,效率肯定不如原来的。如果还有问题可以试试使用insert ignore或者insert on duplicate key update。
SELECT 1 FROM table_a WHERE id=x;
IF(FOUND_ROWS() = 0) THEN
INSERT INTO table_a id VALUES x;
ELSE
UPDATE table_a SET ... WHERE id=x;
END IF;
以上。