锁定引用表的行postgres [英] Locking row of a referenced table postgres
问题描述
我有一个表 A ，它由表 B 引用，也就是说 A 的模式如下所示：
I have a table A which is referenced by a table B that is to say A's schema looks like this:
Table A
(
id int,
name varchar,
)
$ c> B 的模式是：
While Table B's schema is:
Table B
(
id int,
a_id int,
val int
)
我有一段代码在表 B 中创建一个记录。但是，在竞态条件的情况下，在两个并行事务的情况下，我有一个条件在该块失败，结果两个记录创建在表 B 的一个。
这个事务块看起来非常类似这里（在Rails）：
I have a piece of code that creates a record in table B. But, in cases of race conditions say, in case of two parallel transactions, I have a condition in that block which fails and as a result two records are created in table B instead of one.
The transaction block looks very similar to this (in Rails):
ActiveRecord::Base.transaction do
# a here is an ActiveRecord Object of Model A
b = B.new(a_id: a.id, val: value) # value is -ve
raise ActiveRecord::Rollback unless b.save
# this method calculates the sum of val's of all associated records b of a.
# i.e. find all records from B where b.a_id = a.id and find the sum of val
# column
sum = calculateSum(a)
# below condition fails in race conditions
raise ActiveRecord::Rollback if sum <= 0
end
一个解决方案是保持一个集中的密钥哈希，其密钥为 A 的 id ，在进入块之前（在我的应用程序中），我继续等待这个锁被释放。这个解决方案肯定会工作，但我想如果Postgres已经提供了更好的解决方案。
One solution to this would be to keep a centralized hash of locks whose key would be A's id and before entering the block (in my application) I keep on waiting for this lock to be released. This solution would definitely work but I was thinking if Postgres already provides any better solution.
编辑：没有这样的约束， A 的应该只有一个 B 的记录。 A可以有许多 B 的。只是在我提到的代码块中有一个检查在两个并行事务的情况下失败。
There is no such constraint that A's should have only one B's record. A can have many B's. It's just that in the block of code that I mentioned has a check that fails in case of two parallel transactions.
推荐答案
这种并发问题的一般解决方案是将整个块放在 SERIALIZABLE 事务。简单地说，这保证你的事务的行为就好像他们有对数据库的独占访问。主要的缺点是，你可以在任何时候触发序列化失败，从一个简单的 SELECT ，你应该准备重试事务，如果发生这种情况。有维基上的示例，它似乎与您的案例非常相似，应该让你更好地了解这些交易在实践中的行为。
The most general solution to concurrency issues like this is to put your whole block within a SERIALIZABLE transaction. Put simply, this guarantees that your transactions behave as if they had exclusive access to the database. The main downside is that you may trigger a serialisation failure at any point, with from a simple SELECT, and you should be prepared to retry the transaction if this happens. There is an example on the wiki which appears to be very similar to your case, which should give you a better idea of how these transactions behave in practice.
除此之外，我想你需要明确锁定某些东西。一种可能性是通过 SELECT FOR UPDATE 语句锁定 A 中的整个记录，这将阻止竞争过程您的应用程序，以及任何尝试在 B 中插入引用行的任何内容。这里的缺点是，你可能阻塞（或被阻塞）一些不相关的操作，如在不同的引用表中插入，或者更新 A 本身。
Other than that, I think you'll need to explicitly lock something. One possibility would be to lock the whole record in A via a SELECT FOR UPDATE statement, which will block competing processes in your application, as well as anything else trying to insert a referencing row in B. The drawback here is that you might block (or be blocked by) some unrelated operation, like an insert in a different referencing table, or an update of A itself.
更好的方法可能是取出咨询锁 A.id 。这基本上等同于您的集中式散列，但是这些锁具有由Postgres管理的优点，并且在提交/回滚时自动释放。需要注意的是，因为你要取出任意整数的锁，你想确保你不会碰到一些其他进程，碰巧锁定相同的整数为一些不相关的原因。
A better approach might be to take out an advisory lock on A.id. This is basically equivalent to your centralised hash, but these locks have the advantage of being managed by Postgres, and automatically released on commit/rollback. The caveat is that, because you're taking out locks on arbitrary integers, you want to be sure that you don't collide with some other process which happens to be locking that same integer for some unrelated reason.
您可以使用 pg_advisory_xact_lock（）的双参数版本来处理此问题，并使用其中一个输入来标识锁的类型。我发现一个有用的策略是将每个锁类型的调用包装在自己的函数中，并使用该函数的 oid 作为类型标识符，例如：
You can handle this by using the two-argument version of pg_advisory_xact_lock(), and using one of the inputs to identify the type of lock. Rather than maintaining a bunch of lock type constants somewhere on the client side, I find that a useful strategy is to wrap the call for each lock type in its own function, and use that function's oid as the type identifier, e.g:
CREATE FUNCTION lock_A_for_insert_into_B(a_id int) RETURNS VOID LANGUAGE sql AS $$
SELECT pg_advisory_xact_lock('lock_A_for_insert_into_B(int)'::regprocedure::int, a_id)
$$
这篇关于锁定引用表的行postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
