Mysql并发插入死锁问题

文章目录

• • • 一、问题
    • 二、复现
    • • 表结构
      • 执行顺序
      • 死锁信息
    • 三、死锁原因
    • 四、修改代码
    • FA&Q
    • Reference

一、问题

线上数据库版本，隔离级别：5.6.36-log，REPEATABLE-READ

SELECT @@tx_isolation,version();SHOW VARIABLES;SHOW ENGINE INNODB STATUS;SELECT * FROM `perfORMance_schema`.data_locks;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

项目遇到死锁，业务代码大概如下：

@TransactionalmethodA() {    updateTableA;    methodB();    return true;}@TransactionalmethodB() {    try {        select * from test_lock where uid = 1 and anchor = 1 for update        // insert or update        insert into test_lock(uid, anchor, num) values(1, 1, 1)    } catch {        log.error("deadlock rollback")    }}

上诉代码，当同一个用户同时添加两个不存在的记录时，有可能死锁。当报错后，捕获异常没有抛出，这时方法A相对于调用方来说是成功执行。

二、复现

表结构

CREATE TABLE `test_lock` (  `id` int NOT NULL AUTO_INCREMENT,  `uid` int NOT NULL,  `anchor` int NOT NULL,  `num` int NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uid_anchor` (`uid`,`anchor`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb3;

执行顺序

死锁信息

    ------------------------    LATEST DETECTED DEADLOCK    ------------------------    2023-05-15 15:03:45 139836002379520    *** (1) TRANSACTION:    TRANSACTION 259310840, ACTIVE 95 sec inserting    Mysql tables in use 1, locked 1    LOCK WaiT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1    mysql thread id 100802292, OS thread handle 139832517502720, query id 1989879086 10.254.137.152 pepper_activity update    INSERT INTO test_lock (uid, anchor, num) VALUES (1, 1, 1)        *** (1) HOLDS THE LOCK(S):    RECORD LOCKS space id 13103 page no 5 n bits 72 index uid_anchor of table `payment_ext`.`test_lock` trx id 259310840 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;;​        *** (1) WAITING FOR THIS LOCK TO BE GRANTED:    RECORD LOCKS space id 13103 page no 5 n bits 72 index uid_anchor of table `payment_ext`.`test_lock` trx id 259310840 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 259311033, ACTIVE 89 sec inserting    mysql tables in use 1, locked 1    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1    MySQL thread id 100810550, OS thread handle 139842288764672, query id 1989880897 10.254.137.152 pepper_activity update    INSERT INTO test_lock (uid, anchor, num) VALUES (1, 2, 1)        *** (2) HOLDS THE LOCK(S):    RECORD LOCKS space id 13103 page no 5 n bits 72 index uid_anchor of table `payment_ext`.`test_lock` trx id 259311033 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 13103 page no 5 n bits 72 index uid_anchor of table `payment_ext`.`test_lock` trx id 259311033 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 (1)和(2)，正在HOLDS和WAITING的锁。要插入的数据都不存在，事务1和事务2的select … for update分别加了一个间隙锁，然后插入数据之前要获取插入意向锁。因为间隙锁和意向锁互斥，所以造成了互相等待最终导致了死锁。

间隙锁：间隙锁是在索引记录之间的间隙上的锁，或者是第一条记录之前或最后一条记录后的间隙上的锁。可以防止操作过程中其他事务插入间隙。间隙锁可以共存，一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。

插入意向锁：插入意向锁是一种 INSERT 在行插入之前设置的间隙锁。这个锁表示插入的意图，这样插入到同一个索引间隙中的多个事务如果没有插入到间隙中的相同位置，则不需要相互等待。比如有1和5两条记录，两个事务分别插入2和3，插入之前都会使用插入意向锁锁定1-5的间隙，但是因为是不同的行，所以不需要互相等待。

所以，间隙锁之间可以共存，插入意向锁之间可以共存，但是间隙锁和插入意向锁互斥。

for update的用处，第一是对记录加X锁，第二是可以当前读，也就是在可重复读隔离级别下，可以读到其他事务已提交的数据。

四、修改代码

1. 将插入之前的间隙锁去除(select…for update)，修改时使用自增修改道具num=num+1，在方法上使用Redis分布式锁代替数据库锁（添加，扣减）
2. 将异常抛出
3. 插入统计数据的时候，异步做，不影响主流程
4. 设置spring事务传播类型为REQUIRES_NEW

不要大事务，锁定数据多，时间长，死锁概率就会更大

业务上使用功能分布式锁限制并发修改，防止死锁

FA&Q

验证：分别在两个间隙执行会有阻塞吗？
结果：不会阻塞，插入意向锁是一个特殊的间隙锁。

验证：select … for update 不存在数据的时候，加的锁是间隙锁吗？存在数据时是行锁吗？
结果：存在数据时为行锁，不存在数据时为间隙锁

验证：间隙锁之间不会互斥，插入意向锁之间不会互斥。
结果：同一个事务的间隙锁和插入意向锁不会互斥，两个事务之间的间隙锁和插入意向锁会互斥。

验证：间隙锁在READ COMMITTED隔离级别是否启用？
结果：不启用。

验证：当前读和快照读？
结果：可重复读隔离级别下，事务内普通查询是快照读，select … for update 和 select … lock in share mode 是当前读。

验证：Mysql死锁回滚是全部回滚而不是回滚到上个还原点。
结果：全部回滚。

验证：首先update，失败时进行插入
结果：同样是死锁

验证：on duplicate key update插入两条会死锁吗？
结果：本地执行没发现报错，但是他的执行原理和 select … for update然后插入，是同样的原理。所以高并发下依然有问题。

Reference

Mysql锁

来源地址：https://blog.csdn.net/GY325416/article/details/130712382