在注意适当的并发性的同时，如何在触发过程中更新汇总表？ [英] How to update an aggregate table in a trigger procedure while taking care of proper concurrency?

问题描述

为说明起见，假设我要更新表格ProductOffers及其价格。对该表的更改形式为：添加新的ProductOffer，更改现有ProductOffer的价格。

For illustration, say I'm updating a table ProductOffers and their prices. Mutations to this table are of the form: add new ProductOffer, change price of existing ProductOffer.

基于上述更改，我想更新一个Product-table

Based on the above changes, I'd like to update a Product-table which holds pricing info per product aggregated over all offers.

使用基于行的更新/插入触发器来实现这一点似乎是合乎逻辑的，其中触发器运行一个创建/更新产品行。

It seems logical to implement this using a row-based update/insert trigger, where the trigger runs a procedure creating/updating a Product row.

我想适当地实现并发更新（从而触发）。即：同时更新同一产品的productOffers，可能会导致错误的汇总值（因为多个触发的过程会同时尝试插入/更新同一产品行）

I'd like to properly implement concurrent updates (and thus triggers). I.e.: updating productOffers of the same Product concurrently, would potentially lead to wrong aggregate values (because multiple triggered procedures would concurrently attempt to insert/update the same Product-row)

似乎我不能在product-table上使用基于行的锁定（即： select ..用于更新），因为不能保证已经存在特定的产品行。相反，一旦ProductOffer触发了该过程，就必须创建（而不是更新）围绕Product行的第一次。 Afaik，行锁定不能与要插入的新行一起使用，这完全有道理。

It seems I cannot use row-based locking on the product-table (i.e.: select .. for update) because it's not guaranteed that a particular product-row already exists. Instead the first time around a Product row must be created (instead of updated) once a ProductOffer triggers the procedure. Afaik, row-locking can't work with new rows to be inserted, which totally makes sense.

那我离开哪里了？我是否需要推出自己的乐观锁定方案？这将需要包括：

So where does that leave me? Would I need to roll my own optimistic locking scheme? This would need to include:

• 检查行不存在=>如果已经存在，则创建新行失败。 （如果2个触发器同时尝试创建一行，则有可能）。


• 检查行是否存在并具有version = x =>更新行，但如果row.version！= x，则失败。之后再试

上述工作还是其他更好/更现成的解决方案？

Would the above work, or any better / more out-of-the-box solutions?

编辑：

对于将来的引用：找到了可以确切说明什么的官方示例我想完成：示例39-6。维护汇总表的PL / pgSQL触发过程

For future ref: found official example which exactly illustrates what I want to accomplish: Example 39-6. A PL/pgSQL Trigger Procedure For Maintaining A Summary Table

推荐答案

事情比您想象的要简单得多，多亏了 ACID 。

Things are much simpler than you think they are, thanks to the I an ACID.

您设想的触发器将与触发它的数据修改在同一事务中运行，并且对聚合表的每次修改都会首先使用 EXCLUSIVE 锁定。

The trigger you envision will run in the same transaction as the data modification that triggered it, and each modification to the aggregate table will first lock the row that it wants to update with an EXCLUSIVE lock.

因此，如果两个并发事务在聚合表的同一行上导致 UPDATE ，第一个事务将获得该锁并继续进行，而第二个事务将必须等到第一个事务提交（或回滚）后才能获得该行上的锁并对其进行修改。

So if two concurrent transactions cause an UPDATE on the same row in the aggregate table, the first transaction will get the lock and proceed, while the second transaction will have to wait until the first transaction commits (or rolls back) before it can get the lock on the row and modify it.

因此，更新汇总表中同一行的数据修改将有效地进行序列化，这可能会损害性能，但可以保证exa ct结果。

So data modifications that update the same row in the aggregate table will effectively be serialized, which may hurt performance, but guarantees exact results.

这篇关于在注意适当的并发性的同时，如何在触发过程中更新汇总表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！