控制PostgreSQL锁等待的持续时间

Question

我有一张叫deposits的桌子

在进行定金时，表被锁定，因此查询看起来类似于：

SELECT * FROM deposits WHERE id=123 FOR UPDATE

我假设FOR UPDATE正在锁定表，这样我们就可以在不需要另一个线程踩数据的情况下操作它。

但是，当其他存款试图获得表的锁时，问题就会发生。所发生的情况是，在锁定表和调用psql_commit()之间的某个位置--某些东西失败了--将锁保存在愚蠢的长时间中。有几件事我需要帮助解决：

1. 随后试图获得锁的查询应该会失败，我已经尝试过用NOWAIT实现这一点，但我更喜欢使用超时方法(因为等待可能是可以的，而不是等待“愚蠢的时间”)。
2. 理想情况下，我会在传递时将其关闭，并且我的初始查询只保留一定时间的锁，这是否有可能使用postgresql？
3. 还有其他魔法函数吗?我可以插入查询(类似于NOWAIT)，它只会等待锁4秒钟后才会失败？
4. 由于代码库的单块代码特性，这不仅仅是改变全局信任的问题，它还需要一种基于每次查询的解决方案。

谢谢你们的帮助，伙计们，我会不停地闲逛，但运气不太好。这是psql的一个不存在的函数，因为我发现：http://www.postgresql.org/message-id/40286F1F.8050703@optusnet.com.au

Answer 1

我假设UPDATE是在锁定表，这样我们就可以操纵它，而不必再对数据执行线程了。

不是的。FOR UPDATE只锁定这些行，因此另一个试图锁定它们的事务(使用FOR SHARE、FOR UPDATE、UPDATE或DELETE)阻塞，直到事务提交或回滚。

如果您想要一个阻止插入/更新/删除的完整表锁，那么您可能需要LOCK TABLE ... IN EXCLUSIVE MODE。

1. 随后试图获得锁的查询应该会失败，我已经尝试过使用NOWAIT来实现这一点，但我更倾向于使用超时方法(因为它可能可以等待，而不是等待“愚蠢的时间”)。

见 setting。这是在9.3中添加的，在旧版本中是不可用的。

旧版本的粗略近似可以用statement_timeout实现，但这可能导致不必要地取消语句。如果statement_timeout是1s，并且一个语句在锁上等待950 1s，那么它可能会得到锁并继续执行，但是会被超时立即取消。不是你想要的。

没有设置lock_timeout的查询级别方法，但是您可以而且应该只是：

SET LOCAL lock_timeout = '1s';

在您BEGIN事务之后。

1. 理想情况下，我会在传递时将其关闭，并且我的初始查询只保留一定时间的锁，这是否有可能使用postgresql？

有一个语句超时，但是锁处于事务级别。没有事务超时功能。

如果您正在运行单语句事务，可以在运行该语句之前设置一个statement_timeout，以限制它可以运行多长时间。这与限制锁的时间并不完全一样，因为它可能等待900 1s允许的1s才能锁定，实际上只能保持100 1s的锁，然后在超时时被取消。

1. 还有其他魔法函数吗?我可以插入查询(类似于NOWAIT)，它只会等待锁4秒钟后才会失败？

不是的。你必须：

开始；设置本地lock_timeout = '4s'；选择…；提交；

1. 由于代码库的单块代码特性，这不仅仅是改变全局信任的问题，它还需要一种基于每次查询的解决方案。

对此，SET LOCAL是合适的，也是首选的。

在查询文本中没有办法做到这一点，它必须是一个单独的语句。

您链接到的邮件列表帖子是一种从未实现过(至少在公共PostgreSQL版本中)且不存在的假想语法的建议。

在这种情况下，您可能需要考虑“乐观并发控制”，通常称为“乐观锁定”。它为您提供了更大的控制锁定行为的代价，增加了查询重复率和需要更多的应用程序逻辑。