PostgreSQL 获取并释放存储函数中的 LOCK

Question

>我有一个函数，需要在多个大表上执行长时间更新。在更新期间，一次需要锁定2-3个表，以独占模式。

由于并非所有表都需要同时锁定，

因此理想情况下，我只想锁定当时正在更新的那些表，然后在完成后删除锁定。

例如。

-- 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 的正常方法是提交事务，但这在函数中是不可能的。

有什么解决方案吗？在功能完成之前显式释放锁的某种方法？或者运行某种子事务（也许通过在单独的函数中运行每个更新）？

更新

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

Answer 1

在 Postgres 11 或更高版本中，考虑一个允许事务控制的PROCEDURE。看：

• 存储过程是否在 Postgres 中的数据库事务中运行？

<小时 />

有了函数，就没有办法了。Postgres中的函数是原子的（总是在事务中），锁在事务结束时释放。

您可以使用咨询锁解决此问题。但这些不是一回事。所有竞争交易都必须配合使用。不知道咨询锁的并发访问将破坏群。

dba.SE 上的代码示例：

• 邮政更新...限制 1

或者你可能会遇到一些与dblink"作弊"的自主交易：

• 如何在PostgreSQL中进行大型非阻塞更新？
• Postgres 是否支持嵌套或自治事务？

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

Answer 2

在 pg11 中，您现在有 PROCEDURE s，可让您通过 COMMIT 释放锁。我刚刚转换了一堆并行执行的函数，这些函数运行ALTER TABLE ... ADD FOREIGN KEY ...有很多死锁问题，效果很好。

https://www.postgresql.org/docs/current/sql-createprocedure.html

Answer 3

不可能。从文档：一旦获得，锁通常会一直保持到交易结束。但是，如果在建立保存点后获取了锁，则在回滚到保存点时会立即释放锁。这与 ROLLBACK 取消自保存点以来命令的所有效果的原则一致。对于在PL/pgSQL异常块中获取的锁也是如此：从块中获取的错误转义会释放在其中获取的锁。

http://www.postgresql.org/docs/9.3/static/explicit-locking.html