基于JPA（Postgres）的数据库最大行数竞态安全校验问询

当前我想限制每个用户可创建的角色数量，给出的方案是在查询角色计数时加悲观写锁，期望阻塞重复创建请求，直到锁定的创建请求完成，后续请求能获取正确计数。但我担心当用户无角色时，没有行可锁，无法阻塞其他请求，导致竞态问题。

请问这个方案是否具备竞态条件安全性（且性能合理）？如果不安全，有哪些可行的实现方式？优先Java（Spring Data JPA）方案，也可接受数据库层面的约束（若性能更优）。

附上我的代码：

Repository代码：

@Repository
public interface CharacterRepository extends JpaRepository<PlayerCharacter, BigInteger> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    long countByUserId(BigInteger userId);
}

服务层代码：

@Transactional
public List<PlayerCharacter> createCharacter(PlayerCharacterRequest character) {
    // ...
    if (charRepo.countByUserId(loggedInUserId) >= MAX_CHARACTERS_PER_USER) {
        // 抛出错误
    }
    charRepo.save(newPlayerCharacter);
    // ...
}

方案安全性分析 #

你当前的方案不具备竞态条件安全性，核心问题有两个：

1. 悲观锁无法作用于聚合查询：countByUserId是聚合查询，即使加了PESSIMISTIC_WRITE锁，也只会锁定查询过程中扫描到的现有角色行，无法阻止其他并发请求执行同样的count查询（尤其是当用户无角色时，没有任何行被锁定）。
2. 检查与创建的原子性无法保证：即使用户已有角色，count查询和save操作是两个独立步骤。在默认的READ COMMITTED隔离级别下，其他并发事务的count查询无法读取未提交的save结果，会误判角色数量仍未达上限，进而同时创建角色，导致最终数量超过限制。

可行实现方案 #

一、Java（Spring Data JPA）方案 #

1. 独立计数表+悲观锁（高并发场景首选）

创建一个单独的UserCharacterCount实体，存储每个用户的当前角色数量，确保每个用户有且仅有一行数据：

@Entity
public class UserCharacterCount {
    @Id
    private BigInteger userId;
    private int count;
    // getter、setter
}

对应的Repository：

@Repository
public interface UserCharacterCountRepository extends JpaRepository<UserCharacterCount, BigInteger> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<UserCharacterCount> findByUserId(BigInteger userId);
}

服务层修改为：

@Transactional
public List<PlayerCharacter> createCharacter(PlayerCharacterRequest character) {
    BigInteger userId = loggedInUserId;
    // 锁定用户的计数行，无则初始化
    UserCharacterCount countEntity = userCharCountRepo.findByUserId(userId)
            .orElseGet(() -> new UserCharacterCount(userId, 0));
    
    if (countEntity.getCount() >= MAX_CHARACTERS_PER_USER) {
        throw new RuntimeException("角色数量已达上限");
    }
    
    // 先更新计数
    countEntity.setCount(countEntity.getCount() + 1);
    userCharCountRepo.save(countEntity);
    
    // 再创建角色
    PlayerCharacter newChar = new PlayerCharacter();
    // 赋值逻辑...
    charRepo.save(newChar);
    
    // 返回结果...
}

优势：通过锁定计数行，彻底保证检查+更新的原子性，并发请求会被阻塞直到锁释放，不会出现超量问题；性能稳定，适合高并发场景。

2. 独立计数表+乐观锁（低并发场景优先）

基于上述UserCharacterCount实体，添加乐观锁版本号：

@Entity
public class UserCharacterCount {
    @Id
    private BigInteger userId;
    private int count;
    @Version
    private Long version; // 乐观锁版本号
    // getter、setter
}

服务层通过捕获乐观锁异常实现重试：

@Transactional
public List<PlayerCharacter> createCharacter(PlayerCharacterRequest character) {
    BigInteger userId = loggedInUserId;
    int retryTimes = 3;
    
    while (retryTimes-- > 0) {
        UserCharacterCount countEntity = userCharCountRepo.findByUserId(userId)
                .orElseGet(() -> new UserCharacterCount(userId, 0));
        
        if (countEntity.getCount() >= MAX_CHARACTERS_PER_USER) {
            throw new RuntimeException("角色数量已达上限");
        }
        
        countEntity.setCount(countEntity.getCount() + 1);
        try {
            userCharCountRepo.save(countEntity);
            break;
        } catch (OptimisticLockingFailureException e) {
            // 乐观锁冲突，重试
            continue;
        }
    }
    
    if (retryTimes < 0) {
        throw new RuntimeException("操作过于频繁，请稍后重试");
    }
    
    // 创建角色逻辑...
    charRepo.save(newChar);
    // 返回结果...
}

优势：无锁阻塞，性能更高；适合并发量不高、冲突概率低的场景。

二、数据库层面方案 #

1. 插入触发器（强约束）

在player_character表上创建插入触发器，插入前检查用户角色数量是否超过上限，若超过则抛出异常：
MySQL示例：

DELIMITER //
CREATE TRIGGER check_character_limit BEFORE INSERT ON player_character
FOR EACH ROW
BEGIN
    DECLARE current_count INT;
    SELECT COUNT(*) INTO current_count FROM player_character WHERE user_id = NEW.user_id;
    IF current_count >= 3 THEN -- 替换为你的MAX_CHARACTERS_PER_USER值
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '角色数量已达上限';
    END IF;
END //
DELIMITER ;

优势：数据库层面强制约束，无需修改应用代码；任何插入操作都会被拦截，彻底避免超量。
缺点：逻辑耦合在数据库，调试和修改不便；若上限值需要动态调整，需修改触发器。

2. 存储过程（原子性操作）

将检查计数、插入角色的逻辑封装为存储过程，利用数据库事务保证原子性：
MySQL示例：

DELIMITER //
CREATE PROCEDURE create_character(IN user_id BIGINT, IN char_name VARCHAR(50))
BEGIN
    DECLARE current_count INT;
    START TRANSACTION;
    -- 锁定用户的角色行（无则不锁，但结合计数检查）
    SELECT COUNT(*) INTO current_count FROM player_character WHERE user_id = user_id FOR UPDATE;
    IF current_count >= 3 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '角色数量已达上限';
    END IF;
    -- 插入角色
    INSERT INTO player_character(user_id, name) VALUES(user_id, char_name);
    COMMIT;
END //
DELIMITER ;

应用层直接调用存储过程即可，无需额外的锁逻辑。

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