基于JPA与PostgreSQL实现并发计数并返回计数值的方案问询

一、当前触发器方案的安全性与优化 #

你当前的触发器方案在并发场景下是安全的——因为使用了SELECT ... FOR UPDATE，会对product_location_count中对应location_id的行加排他锁，并发请求会串行处理，不会出现计数重复的问题。但原触发器可以优化写法，提升性能：

原触发器是AFTER INSERT，插入商品后再更新counter_value，可以改成BEFORE INSERT，直接给新插入的商品赋值，避免额外的UPDATE操作：

CREATE FUNCTION maintain_location_product_count_fun() RETURNS TRIGGER AS
$$
DECLARE
counter_var BIGINT;
BEGIN
  IF TG_OP IN ('INSERT') THEN
    -- 锁定对应location的计数器行，确保并发串行处理
    SELECT counter INTO counter_var FROM product_location_count WHERE id = NEW.location_id FOR UPDATE;
    -- 直接给新商品的counter_value赋值，插入时一并写入
    NEW.counter_value := counter_var;
    -- 递增计数器
    UPDATE product_location_count SET counter = counter + 1 WHERE id = NEW.location_id;
  END IF;
  RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER maintain_location_product_count_trig
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE PROCEDURE maintain_location_product_count_fun();

这个优化后的触发器保持了原有的并发安全性，同时减少了一次数据库写操作，性能更优。

二、解决JPA悲观锁报错问题 #

你遇到的cannot execute SELECT FOR UPDATE in a read-only transaction报错，大概率是事务被默认设置为只读模式导致的，解决方法如下：

1. 明确设置事务为非只读
   如果使用TransactionTemplate，需手动关闭只读模式：

   transactionTemplate.setReadOnly(false);
   transactionTemplate.execute(status -> {
     // 调用带悲观锁的查询方法
     var counter = locationCounterRepository.findById(location.getId(), LockModeType.PESSIMISTIC_WRITE).get();
     long currentCount = counter.getCounter();
     // 递增计数器
     counter.setCounter(currentCount + 1);
     locationCounterRepository.save(counter);
     // 创建商品并赋值counter_value
     Product product = new Product();
     product.setLocationId(location.getId());
     product.setCounterValue(currentCount);
     return productRepository.save(product);
   });
   

2. 在Repository中正确定义悲观锁方法
   自定义带锁的查询方法，确保锁机制生效：

   @Repository
   public interface ProductsLocationCounterRepository extends JpaRepository<ProductsLocationCounter, Long> {
       @Lock(LockModeType.PESSIMISTIC_WRITE)
       Optional<ProductsLocationCounter> findById(Long id);
   }
   

三、替代方案：使用PostgreSQL RETURNING子句 #

利用PostgreSQL的UPDATE ... RETURNING特性，可以在一条原子语句中完成计数器递增并返回旧值，无需触发器：

1. 在Repository中定义自定义更新方法

   @Repository
   public interface ProductsLocationCounterRepository extends JpaRepository<ProductsLocationCounter, Long> {
       @Modifying
       @Query(value = "UPDATE product_location_count SET counter = counter + 1 WHERE id = :locationId RETURNING counter - 1", nativeQuery = true)
       Long incrementAndGetOldCount(@Param("locationId") Long locationId);
   }
   

2. 业务代码中调用并创建商品

   transactionTemplate.execute(status -> {
       // 原子操作：递增计数器并获取更新前的旧值
       Long oldCount = locationCounterRepository.incrementAndGetOldCount(location.getId());
       // 创建商品并赋值counter_value
       Product product = new Product();
       product.setLocationId(location.getId());
       product.setCounterValue(oldCount);
       return productRepository.save(product);
   });
   

   这个方案依赖PostgreSQL的原生特性，操作是原子性的，并发场景下同样安全。

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