MySQL死锁问题案例

MySQL死锁问题

问题描述：在一张流水生成的记录表中，当没有当前条件的数据时候，并发情况下会导致有线程因为死锁问题生成流水号失败。

场景

有一张生成流水的表：

场景复现：

简单来说，在根据流水类型、年、月、日生成流水信息的时候有则update流水号+1,无则insert；

Mapper

@Mapper
public interface NumberTestMapper {

    @Update("update number_test set serial_number = serial_number+1 where number_type = #{numberType} and number_year = #{numberYear} and number_month=#{numberMonth} and number_day = #{numberDay}")
    int update(NumberTest numberTest);

    @Insert("insert into number_test values (#{numberType},#{numberYear},#{numberMonth},#{numberDay},#{serialNumber})")
    int insert(NumberTest numberTest);

    @Select("select * from number_test where number_type = #{numberType} and number_year = #{numberYear} and number_month=#{numberMonth} and number_day = #{numberDay}")
    NumberTest selectById(NumberTest numberTest);
}

service

@Service
public class TestService {
    @Autowired
    private NumberTestMapper mapper;



    @Transactional
    public long generate() {
        NumberTest test = new NumberTest();
        test.setNumberType(1);
        test.setNumberYear("2024");
        test.setNumberMonth("01");
        test.setNumberDay("01");
        int update = mapper.update(test);
        if(update == 0){
            test.setSerialNumber(0);
            mapper.insert(test);
        }
        NumberTest select = mapper.selectById(test);
        return select.getSerialNumber();
    }
}

测试

@SpringBootTest
@RunWith(SpringRunner.class)
public class NumberTestMapperTest {
    @Autowired
    private TestService service;
    @Test
    public void test() throws InterruptedException {
        List<Long> result = new CopyOnWriteArrayList<>();
        CountDownLatch countDownLatch = new CountDownLatch(5);
        ExecutorService executorService = Executors.newFixedThreadPool(5);
        for (int i = 0; i < 5; i++) {
            executorService.submit(() -> {
                try{
                    result.add(service.generate());
                }catch (Exception e){
                    e.printStackTrace();
                }finally {
                    countDownLatch.countDown();
                }
            });
        }
        countDownLatch.await();
        executorService.shutdown();
        result.forEach(item -> System.out.println(item));
    }

    @Test
    public void test2() throws InterruptedException {
        CountDownLatch countDownLatch = new CountDownLatch(10);
        ExecutorService executorService = Executors.newFixedThreadPool(10);
        for (int i = 0; i < 10; i++) {
            executorService.submit(() -> {
                try{
                    service.insert();
                }catch (Exception e){
                    e.printStackTrace();
                }finally {
                    countDownLatch.countDown();
                }
            });
        }
        countDownLatch.await();
        executorService.shutdown();
        System.out.println("success!");
    }
}

结果

测试在不存在当前行数据的情况下进行

1. test方法会有死锁产生
2. test2则报主键冲突问题

test方法为什么会产生死锁问题呢？

原因

开启两个事务分别执行：
T1:

BEGIN --step1
--step3
UPDATE number_test set serial_number = serial_number+1 where number_type = 1 and number_year = '2024' and number_month ='01' and number_day = '01';
--step5
insert into number_test VALUES (1,'2024','01','01',0);

COMMIT

T2:

BEGIN --step2
--step4
UPDATE number_test set serial_number = serial_number+1 where number_type = 1 and number_year = '2024' and number_month ='01' and number_day = '01';
--step6
insert into number_test VALUES (1,'2024','01','01',0);
COMMIT

按照step1~step5执行发现：

1. 执行step5的时候被阻塞，无法获取锁
2. 执行step6的时候死锁产生
3. 证明执行update的时候是有锁的，那锁的是什么呢？
4. 经过多次实验发现，将insert中的number_type修改为2后不会去获取锁，证明update锁的是number_type字段。

将表结构修改：

然后发现：

1. update没有使用id主键条件的时候是表锁，使用的时候是行锁
2. 在加了联合索引后，问题仍然存在，而且是表锁，执行到step5后任何数据都插不进去
   

总结：

在并发情况下，多个线程下执行update会有锁，但是update和update直接不会有锁，update和insert之间有锁。insert和update之间相互抢锁形成死锁。

这里不知道为什么在没有数据的时候，同时update相同条件的时候不会有锁，而执行insert的时候就有锁，推测是Mysql为了解决幻读问题使用间隙锁的原因。如果有大神知道，多谢指教！

解决方案：

由于Mysql有解决死锁的机制了，采用try-catch重试解决这个问题

    @Transactional
    public long generate() {
 
        try {
            NumberTest test = new NumberTest();
            test.setNumberType(1);
            test.setNumberYear("2024");
            test.setNumberMonth("01");
            test.setNumberDay("01");
            //update number_test set serial_number = serial_number+1 where number_type = #{numberType} and number_year = #{numberYear} and number_month=#{numberMonth} and number_day = #{numberDay}
            int update = mapper.update(test);
            if(update == 0){
                test.setSerialNumber(0);
                //insert into number_test values (#{numberType},#{numberYear},#{numberMonth},#{numberDay},#{serialNumber})
                mapper.insert(test);
            }
            NumberTest select = mapper.selectById(test);
            return select.getSerialNumber();
        }catch (Exception e){
            return generate();
        }
    }