Java JDBC事务偶发数据异常与锁未释放问题排查求助

偶发异常现象 #

• 每日100笔订单中，有1-2笔触发bought_times未递增告警，但手动核查数据库发现该字段实际已完成递增
• 每月3000笔订单中，有3-5笔完成后，订单locked字段未被置为null（锁未释放），但手动查库该字段确实不为null

技术栈说明 #

• 核心操作（查询、更新、读取）仅使用jdbcTemplate与TransactionTemplate
• 仅MySQL插入模型时使用JPA
• 原采用单线程锁控制操作，升级MySQL后引入Redis分布式锁确保单线程执行核心代码

排查过程 #

• 默认隔离级别为Read Committed，切换至Serializable级别后问题仍存在
• 锁未释放问题与MySQL高负载强相关，低负载环境下无此现象
• 核查MySQL日志未发现错误；尝试REQUIRED_NEW+SERIALIZABLE时出现死锁
• 单线程测试无法复现问题；多线程+Redis公平锁测试曾成功复现，但后续多次运行无法复现
• 推测生产环境中3个Redis锁（120秒超时）偶发超时，导致多线程无锁执行核心代码

临时解决方案 #

将锁超时与事务超时延长至500秒，目前正在监控验证效果

相关代码片段 #

单线程同步测试代码 #

public synchronized void test() {
    long payment = 999;
    long bought_times_before = jdbcTemplate.queryForObject("select bought_times from user where id = ?", new Object[]{1}, Long.class);
    TransactionTemplate tmpl = new TransactionTemplate(txManager);
    tmpl.setTimeout(300);
    tmpl.setName("p:" + payment);
    tmpl.executeWithoutResult(status -> {
        jdbcTemplate.update("update orders set  attempts_to_verify = attempts_to_verify + 1, transaction_value = null where id = ?", payment);
        jdbcTemplate.update("update orders set  locked = null where id = ?", payment);
        jdbcTemplate.update("update user set bought_times = bought_times + 1 where id = 1");
    });
    long bought_times_after = jdbcTemplate.queryForObject("select bought_times from user where id = ?", new Object[]{1}, Long.class);
    if (bought_times_after <= bought_times_before) log.error("bought_times_after <= bought_times_before");
}

单线程事务测试代码 #

@PostConstruct
public void test(){
    jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS TEST ( id int, name int, locked boolean )");
    jdbcTemplate.execute("insert into TEST values(1, 1, 1);");

    for(int i = 0; i < 100000; i++) {
        long prev = jdbcTemplate.queryForObject("select name from TEST where id = 1", Long.class);
        TransactionTemplate tmpl = new TransactionTemplate(txManager);
        jdbcTemplate.update("update TEST set locked = true  where id = 1;");

        tmpl.execute(new TransactionCallbackWithoutResult() {
            @SneakyThrows
            @Override
            protected void doInTransactionWithoutResult(org.springframework.transaction.TransactionStatus status) {
                jdbcTemplate.update("update TEST set name = name + 1 where id = 1;");
                jdbcTemplate.update("update TEST set locked = false  where id = 1;");
            }
        });
        long curr = jdbcTemplate.queryForObject("select name from TEST where id = 1", Long.class);
        boolean lock = jdbcTemplate.queryForObject("select locked from TEST where id = 1", Boolean.class);
        if(curr <= prev){
            log.error("curr <= prev");
        }
        if(lock){
            log.error("lock = true");
        }
    }

}

多线程Redis分布式锁测试代码 #

@PostConstruct
public void test(){
    jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS TEST ( id int, name int, locked boolean )");
    jdbcTemplate.execute("insert into TEST values(1, 1, 1);");
    ExecutorService executorService = Executors.newFixedThreadPool(100);
    for(int i = 0; i < 100000; i++) {
        executorService.submit(() -> {
            RLock rLock = redissonClient.getFairLock("lock");
            try {
                rLock.lock(120, TimeUnit.SECONDS);
                long prev = jdbcTemplate.queryForObject("select name from TEST where id = 1", Long.class);
                TransactionTemplate tmpl = new TransactionTemplate(txManager);
                jdbcTemplate.update("update TEST set locked = true  where id = 1;");
                tmpl.execute(new TransactionCallbackWithoutResult() {
                    @SneakyThrows
                    @Override
                    protected void doInTransactionWithoutResult(org.springframework.transaction.TransactionStatus status) {
                        jdbcTemplate.update("update TEST set name = name + 1 where id = 1;");
                        jdbcTemplate.update("update TEST set locked = false  where id = 1;");
                    }
                });
                long curr = jdbcTemplate.queryForObject("select name from TEST where id = 1", Long.class);
                boolean lock = jdbcTemplate.queryForObject("select locked from TEST where id = 1", Boolean.class);
                if (curr <= prev) {
                    log.error("curr <= prev");
                }
                if (lock) {
                    log.error("lock = true");
                }
            } finally {
                rLock.unlock();
            }
        });
    }

}

内容的提问来源于stack exchange，提问作者zelenb