JPA 并发 postgresql 计数器列，具有检索值

Question

先决条件

Postgresql

带有弹簧数据 JPA 的弹簧启动

问题

我有2张桌子。Products和ProductsLocationCounter.每个产品都有一个location_id和counter_value领域等。location_id也是ProductsLocationCounter的主键。 该ProductsLocationCounter旨在保留在添加新产品时按特定location_id分组的产品数量的计数器。 问题是我还需要将该时间点的计数器值附加到产品实体。

所以流程会像

1. create product
2. counter_value = get counter
3. increment counter
4. product.counter_value = counter_value

当然，这必须同时进行。 现在，我已经阅读/尝试了不同的解决方案。

1. 这篇 StackOverflow 帖子建议我应该让数据库处理并发，这对我来说听起来不错。但诀窍是我需要在同一笔交易中计数器的值。所以我创建了一个触发器

CREATE FUNCTION maintain_location_product_count_fun() RETURNS TRIGGER AS
$$
DECLARE
counter_var BIGINT;
BEGIN
IF TG_OP IN ('INSERT') THEN
select product_location_count.counter into counter_var from product_location_count WHERE id = new.location_id FOR UPDATE;
UPDATE product_location_count SET counter = counter + 1 WHERE id = new.location_id;
UPDATE products SET counter_value = counter_var WHERE location_id = new.location_id;
END IF;
RETURN NULL;
END
$$
LANGUAGE plpgsql;
CREATE TRIGGER maintain_location_product_count_trig
AFTER INSERT ON products
FOR EACH ROW
EXECUTE PROCEDURE maintain_location_product_count_fun();

并用并行流对其进行了测试

IntStream.range(1, 5000)
.parallel()
.forEach(value -> {
executeInsideTransactionTemplate(status -> {
var location = locationRepository.findById(location.getId()).get();
return addProductWithLocation(location)
});
});

counter_value列上没有重复。此触发器对于多线程应用是否安全？以前没有使用过触发器/postgresql 函数。不确定会发生什么

---

1. 我尝试的第二个解决方案是在ProductsLocationCounter实体findById方法上添加PESIMISTIC_WRITE，但我最终得到了cannot execute SELECT FOR UPDATE in a read-only transaction即使我以@Transactional注释的方法(默认情况下具有只读 false)执行代码。

2. 第三个是在同一语句中更新和检索计数器的值，但 spring jpa 不允许这样做(也不允许底层数据库)，因为更新语句只返回受影响的行数

是否有任何其他解决方案，或者我是否需要向触发器函数添加一些内容以使其线程安全？谢谢

Answer 1

这就是我实现所需目标的方式。

长话短说，我使用了一个sql函数，并在存储库中调用了它。我不再需要触发器了。

https://stackoverflow.com/a/74208072/3018285