PostgreSQL在存储函数中获取和释放锁

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

有什么解决办法吗？在函数完成之前显式释放锁的一些方法？或者运行某种子事务(可能通过在一个单独的函数中运行每个更新)？

更新

我承认没有解决办法。我将把每个更新写入一个单独的函数中，并在db之外进行协调。谢谢大家。

Answer 1

在Postgres 11或更高版本中，考虑允许事务控制的。请参见：

• Do stored procedures run in database transaction in Postgres?

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

您可能可以使用advisory locks来解决这个问题。但这不是一回事。所有相互竞争的交易都必须配合。不知道咨询锁的并发访问会破坏党的利益。

Dba.SE上的代码示例：

• Postgres UPDATE ... LIMIT 1

或者，您可以使用dblink进行“欺骗”自治事务：

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

或者你重新评估你的问题，把它分成几个单独的交易。

Answer 2

在pg11中，您现在有了PROCEDURE，它允许您通过COMMIT释放锁。我刚刚转换了一堆并行执行的函数，运行有很多死锁问题的ALTER TABLE ... ADD FOREIGN KEY ...，它工作得很好。

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

Answer 3

不可能。从文档:一旦获得，一个锁通常保持到事务结束。但是，如果在建立保存点之后获得锁，则如果将保存点回滚到保存点，则立即释放该锁。这与自保存点以来回滚取消命令的所有效果的原则是一致的。在PL/pgSQL异常块中获得的锁也是如此:从块中转义错误将释放在其中获得的锁。

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