同一事务中INSERT...ON DUPLICATE KEY UPDATE后SELECT返回NULL问题排查

我在JPA（Hibernate）开启的MySQL事务内执行以下代码，生产环境偶尔会打出userConfig must not be null的日志，需要排查原因：

UserConfig userConfig = this.findUserConfig(appId, userId, devicePlatform);
if(userConfig == null){
    userConfig = this.addDefaultUserConfig(appId, userId, devicePlatform);
    if (userConfig == null) logger.error("userConfig must not be null")
}

1. UserConfig实体定义 #

@Table(name = "user_config")
public class UserConfig extends TemporalModel {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected long id;

    @Column(name = "user_id")
    private String userId;

    @Column(name = "app_id")
    private String appId;

    @Column(name = "device_platform")
    @Enumerated(EnumType.STRING)
    private Platform devicePlatform;

    @Column(name = "payer")
    private Boolean payer;

    @Column(name = "winning_balance")
    private BigDecimal winningBalance;

    @Column(name = "games_eligibility_count")
    private Integer gamesEligibilityCount;
}

2. findUserConfig方法实现 #

public UserConfig findUserConfig(String appId, String userId, Platform devicePlatform) {
    EntityManager em = threadContextProvider.currentThreadContext().getEm();
    String queryS = "SELECT u FROM UserConfig u WHERE u.userId = :userId and u.appId = :appId";
    TypedQuery<UserConfig> q = em.createQuery(queryS, UserConfig.class)
            .setParameter("userId", userId)
            .setParameter("appId", appId);

    List<UserConfig> userConfig = q.getResultList();
    if (userConfig == null || userConfig.size() == 0) {
        return null;
    }
    return userConfig.get(0);
}

3. addDefaultUserConfig方法实现 #

UserConfig addDefaultUserConfig(String appId, String userId, Platform devicePlatform) {
    EntityManager em = threadContextProvider.currentThreadContext().getEm();
    String queryStr = "INSERT INTO user_config values ( null, :userId , :appId , :devicePlatform, :winningBalance , :payer ," +
            ":ratedOnAppStore , :ratedOnPlayStore , :gamesEligibilityCount , now() , now() ) ON DUPLICATE KEY UPDATE updated_at = now() ;";

    Query q = em.createNativeQuery(queryStr)
        .setParameter("userId", userId)
        .setParameter("appId", appId)
        .setParameter("devicePlatform", devicePlatform == null ? null:devicePlatform.name())
        .setParameter("payer", 0)
        .setParameter("winningBalance", "0.00")
        .setParameter("ratedOnAppStore", 0)
        .setParameter("ratedOnPlayStore", 0)
        .setParameter("gamesEligibilityCount", 0);
    int affectedRows = q.executeUpdate(); // 返回值不为0
    
    UserConfig userConfig = findUserConfig(appId, userId, devicePlatform);
    if (userConfig == null) {
        logger.debug("addDefaultUserConfig: userConfig is null");
    }
    return userConfig;
}

4. 数据库表索引信息 #

• PRIMARY KEY (id)
• UNIQUE KEY user_config (user_id, app_id)

5. 额外观察结果 #

• 当addDefaultUserConfig返回null时，affectedRows的值不为0
• 使用ON DUPLICATE KEY UPDATE updated_at = now()时，返回null的频率比使用ON DUPLICATE KEY UPDATE user_id = :userId低
• 使用ON DUPLICATE KEY UPDATE user_id = :userId时，若事务未执行插入操作，addDefaultUserConfig总会返回null

核心原因分析 #

1. 事务隔离级别导致的快照读限制 #

MySQL默认采用REPEATABLE READ隔离级别，该级别下事务内的所有查询都会读取事务启动时的一致性快照。当多线程并发执行时：

• 线程A先执行findUserConfig返回null，插入新记录但未提交事务
• 线程B启动事务后执行findUserConfig，读取的是事务启动时的快照（此时线程A的记录未提交，快照中无数据），返回null
• 线程B执行INSERT时触发唯一键冲突，转而执行UPDATE操作，修改数据库中的记录
• 线程B后续执行findUserConfig时，依然读取事务启动时的快照，无法获取到刚修改的记录，最终返回null

2. Hibernate缓存与原生SQL的同步缺失 #

原生SQL操作绕过了Hibernate的ORM缓存机制，执行UPDATE后，Hibernate的一级缓存（会话缓存）不会自动同步数据库的最新状态。此时findUserConfig通过JPQL查询时，优先从缓存中获取数据（缓存中无对应记录），再去数据库查询，但受限于REPEATABLE READ的快照读，依然无法读到更新后的记录。

3. ON DUPLICATE KEY UPDATE的操作差异 #

当使用ON DUPLICATE KEY UPDATE user_id = :userId时，该操作未实际修改任何数据（user_id值与原值一致），MySQL不会标记数据为"已修改"，进一步加剧了快照读无法感知变化的概率，导致返回null的频率更高。

解决方案 #

1. 调整事务隔离级别 #

将事务隔离级别改为READ COMMITTED，该级别下事务内的查询可以读取当前已提交的最新数据（包括自身事务的修改），避免快照读的限制。

2. 手动同步Hibernate缓存 #

在执行原生SQL后，强制刷新EntityManager并清空缓存，确保后续查询直接从数据库获取最新数据：

int affectedRows = q.executeUpdate();
em.flush(); // 同步ORM操作到数据库
em.clear(); // 清空一级缓存
UserConfig userConfig = findUserConfig(appId, userId, devicePlatform);

3. 改用JPA API替代原生SQL #

避免使用原生SQL，改用JPA的merge方法实现"不存在则插入，存在则更新"的逻辑，让Hibernate自动维护缓存一致性：

UserConfig userConfig = new UserConfig();
userConfig.setUserId(userId);
userConfig.setAppId(appId);
userConfig.setDevicePlatform(devicePlatform);
// 设置默认值
userConfig.setPayer(false);
userConfig.setWinningBalance(new BigDecimal("0.00"));
userConfig.setGamesEligibilityCount(0);
// merge方法会自动处理插入或更新
userConfig = em.merge(userConfig);

4. 修正原生SQL的字段顺序 #

确保原生SQL中values的顺序与数据库表的字段顺序完全一致，避免数据错位导致的实体映射异常。

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