PostgreSQL获取并释放存储函数内部的LOCK [英] PostgreSQL obtain and release LOCK inside stored function

问题描述

我有一个功能，需要对多个大表执行长时间的更新.在更新过程中，一次需要锁定2-3个表.

I have a function that needs to perform a long update on multiple large tables. During the update 2-3 tables at a time need to be locked in EXCLUSIVE mode.

由于并非所有表都需要同时锁定，因此理想情况下，我只想锁定当时正在更新的那些表，然后在完成后将其删除.

Since not all the tables need to be locked at the same time, ideally I'd want to LOCK only those tables I'm updating at the time, and then remove the lock once I'm done.

例如.

-- Lock first pair of tables
LOCK TABLE tbl1_a IN EXCLUSIVE MODE;
LOCK TABLE tbl1_b IN EXCLUSIVE MODE;

-- Perform the update on tbl1_a and tbl1_b

-- Release the locks on tbl1_a and tbl1_b
--  HOW???

-- Proceed to the next pair of tables
LOCK TABLE tbl2_a IN EXCLUSIVE MODE;
LOCK TABLE tbl2_b IN EXCLUSIVE MODE;

不幸的是，在plpgsql中没有等效的UNLOCK语句.删除LOCK的通常方法是提交事务，但这在函数内部是不可能的.

Unfortunately, there's no the equivalent of UNLOCK statement in plpgsql. The normal way to remove LOCK is to COMMIT the transaction, but that is not possible inside a function.

有什么解决办法吗?有什么方法可以在函数完成之前显式释放锁?还是运行某种子事务(也许通过在单独的函数中运行每个更新)?

Is there any solution for this? Some way to explicitly release the lock before function is done? Or run some kind of sub-transaction (perhaps by running each update in a separate function)?

更新

我接受没有解决方案.我将每个更新写入一个单独的函数中，并从数据库外部进行协调.谢谢大家.

I accepted that there is no solution. I'll write each update into a separate function and coordinate from outside the db. Thanks everyone.

推荐答案

没有没办法. Postgres中的函数是原子的(总是在事务内部)，并且锁在事务结束时释放.而且还没有自主交易.

There is no way. Functions in Postgres are atomic (always inside a transaction) and locks are released at the end of a transaction. And there are no autonomous transactions (yet).

您也许可以使用 咨询锁定 .但是那些不是同一回事.所有竞争性交易都必须继续进行.并没有通知锁的并发访问会破坏该方.

You might be able to work around this with advisory locks. But those are not the same thing. All competing transactions have to play along. Concurrent access that is not aware of advisory locks will spoil the party.

dba.SE上的代码示例:

Code example on dba.SE:

• Postgres更新...限制1

或者您可能会通过dblink来欺骗"自主事务:

Or you might get somewhere with "cheating" autonomous transactions with dblink:

• 我如何做大PostgreSQL中的非阻塞更新?
• Postgres是否支持嵌套或自治事务?

• How do I do large non-blocking updates in PostgreSQL?
• Does Postgres support nested or autonomous transactions?

或者您重新评估您的问题并将其分解为几个单独的事务.

Or you re-assess your problem and split it up into a couple of separate transactions.

这篇关于PostgreSQL获取并释放存储函数内部的LOCK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！