mysql并发插入死锁

怼烎@ 2022-12-22 08:49 227阅读 0赞

# mysql并发插入死锁 #

## 场景 ##

多个线程同时执行条件插入语句  
SQL：

INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) SELECT ?, ?, ?, 0, ?, ? FROM DUAL WHERE NOT EXISTS(SELECT resource FROM system_lock WHERE resource = ?)

## 状态跟踪 ##

执行命令查看 innodb 状态

show engine innodb status

部分结果：

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2020-11-18 10:57:27 0x7f8cad4f4700
    *** (1) TRANSACTION:
    TRANSACTION 1484317, ACTIVE 0 sec inserting
    mysql tables in use 2, locked 2
    LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 420033, OS thread handle 140242091288320, query id 11981741 123.157.208.7 root executing
    INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) SELECT '2763d6de-9f0a-4c45-8c0b-dbda640224e3', '10.13.64.103:demo2:43', '675fee90-01eb-4511-b64e-c339be116475', 0, '2020-11-18 18:57:25.798050', '2021-11-19 00:46:37.798050' FROM DUAL WHERE NOT EXISTS(SELECT resource FROM system_lock WHERE resource = '2763d6de-9f0a-4c45-8c0b-dbda640224e3')
    
    *** (1) HOLDS THE LOCK(S):
    RECORD LOCKS space id 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1484317 lock mode S
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1484317 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 1484315, ACTIVE 0 sec inserting
    mysql tables in use 2, locked 2
    LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 420032, OS thread handle 140241595483904, query id 11981739 123.157.208.7 root executing
    INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) SELECT '2763d6de-9f0a-4c45-8c0b-dbda640224e3', '10.13.64.103:demo2:46', 'ce7a3358-453f-489a-a414-c84403ae1506', 0, '2020-11-18 18:54:29.151050', '2021-11-19 00:43:41.151050' FROM DUAL WHERE NOT EXISTS(SELECT resource FROM system_lock WHERE resource = '2763d6de-9f0a-4c45-8c0b-dbda640224e3')
    
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1484315 lock mode S
    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 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1484315 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)

是因为插入语句使用了条件插入吗？换成单纯的插入试一下

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2020-11-18 11:52:20 0x7f8cad4f4700
    *** (1) TRANSACTION:
    TRANSACTION 1498717, ACTIVE 0 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 8 lock struct(s), heap size 1136, 3 row lock(s)
    MySQL thread id 420353, OS thread handle 140241587422976, query id 12160669 123.157.208.18 root update
    INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) VALUES ('5d57d8cb-38cc-49b3-aa93-2c116e076eed', '10.13.64.103:demo2:41', 'a3142732-3e7e-4d0a-8740-700b0dc5af36', 0, now(), '2021-11-19 01:39:31.065050')
    
    *** (1) HOLDS THE LOCK(S):
    RECORD LOCKS space id 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1498717 lock mode S
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1498717 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 1498721, ACTIVE 0 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s)
    MySQL thread id 420355, OS thread handle 140241588307712, query id 12160673 123.157.208.6 root update
    INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) VALUES ('5d57d8cb-38cc-49b3-aa93-2c116e076eed', '10.13.64.103:demo2:43', '0bafcc68-c4e2-491f-bf3e-abb7935783f0', 0, now(), '2021-11-19 01:39:31.778050')
    
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1498721 lock mode S
    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 544 page no 4 n bits 72 index PRIMARY of table `shoulder_platform`.`system_lock` trx id 1498721 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)

## 原理分析 ##

两个事务都持有该行的 S 锁，期望获取 X 锁时被对方阻塞了。

## 锁消除 ##

删除改行记录（释放锁之后），其中一个事务将成功获取锁，从而执行成功，其他事务因死锁检测而回滚。

## 扩展：mysql官方声明-不同语句产生的锁 ##

SELECT … FROMis a consistent read，无锁  
SELECT … FOR UPDATEandSELECT … FOR SHAREstatements：对于唯一索引，只对需要锁定的记录上record lock，如果不是唯一索引就会加Next-key lock，同时会对不满足条件的第一个record的前面加上gap lock  
update和delete都2一样的策略  
insert比较不一样，需要重点说下  
首先，insert会对要插入的gap加insert intention gap lock，insert intention gap lock不像gap lock一样防止insert，insert intention gap lock互相之间可以共存，这样允许多个insert并发插入不同的位置

其次，对于要插入的位置会加入排他锁

多个insert并发(至少3个），当插入的位置一样的时候，比如3个session都insert table id=x，其中第一个获得排他锁，其他两个session会产生duplicate-key error，当duplicate-key error发生时，两个session都会将锁变化为共享锁，下一步获取排他锁，然后第一个session rollback了，两个session互相持有共享锁，无法获得排他锁，导致死锁

s1 insert id =x 或者 s1 delete id = x

s2 insert id = x

s3 insert id = x

s1 rollback

1.  replace和insert一样
2.  INSERT … ON DUPLICATE KEY UPDATE与insert不一样，当遇到 duplicate-key时，对于primary key获取排他记录锁，对于unique index获取Next-key lock，不会死锁

## 建议 ##

非热点（如抢购）情况，使用悲观锁，先保证业务的正确性和数据的一致性，且能够保证较好的代码可读性，减少维护成本。

若无过多业务逻辑，则直接使用数据库行锁，如

select xxx from tt where field = f for update
    执行更新 sql

若有较耗时的业务逻辑，则使用业务锁，如redis锁、zookeeper 锁

--------------------

优化性能和死锁问题的思路：