控制PostgreSQL锁定等待的持续时间 [英] Controlling duration of PostgreSQL lock waits

问题描述

我有一个名为deposits

存款后，表被锁定，因此查询看起来像这样:

When a deposit is made, the table is locked, so the query looks something like:

SELECT * FROM deposits WHERE id=123 FOR UPDATE

我假设FOR UPDATE正在锁定表，以便我们可以在没有其他线程踩踏数据的情况下对其进行操作.

I assume FOR UPDATE is locking the table so that we can manipulate it without another thread stomping on the data.

但是，当其他存款试图获取表的锁时，会出现问题.发生的事情是，在锁定表和调用psql_commit()之间的某处发生了故障，并将锁定保持了很长的时间.我需要解决以下几件事:

The problem occurs though, when other deposits are trying to get the lock for the table. What happens is, somewhere in between locking the table and calling psql_commit() something is failing and keeping the lock for a stupidly long amount of time. There are a couple of things I need help addressing:

1. 随后尝试获取锁的查询应该失败，我尝试使用NOWAIT实现此操作，但希望使用超时方法(因为可以等待，但不必等待愚蠢的时间" ')

1. Subsequent queries trying to get the lock should fail, I have tried achieving this with NOWAIT but would prefer a timeout method (because it may be ok to wait, just not wait for a 'stupid amount of time')

理想情况下，我会在通过时取消此操作，并让我的初始查询仅将锁保持一定的时间，而使用postgresql可以吗?

Ideally I would head this off at the pass, and have my initial query only hold the lock for a certain amount of time, is this possible with postgresql?

我是否可以在查询中添加其他魔术功能(类似于NOWAIT)，该功能仅等待锁定4秒钟才能失败?

Is there some other magic function I can tack onto the query (similar to NOWAIT) which will only wait for the lock for 4 seconds before failing?

由于代码库具有令人难以置信的整体意大利面条式代码性质，它不仅仅是更改全局配置的问题，还有点需要基于查询的解决方案

Due to the painfully monolithic spaghetti code nature of the code base, its not simply a matter of changing global configs, it kinda needs to be a per-query based solution

感谢您的帮助，我会继续四处张望，但运气不佳.这是psql的不存在的函数，因为我发现了这一点: http://www.postgresql.org/message-id/[e-mail]

Thanks for your help guys, I will keep poking around but I haven't had much luck. Is this a non-existing function of psql, because I found this: http://www.postgresql.org/message-id/[e-mail]

推荐答案

我认为FOR UPDATE锁定了表，以便我们可以在没有其他线程踩数据的情况下对其进行操作.

I assume FOR UPDATE is locking the table so that we can manipulate it without another thread stomping on the data.

不. FOR UPDATE仅锁定这些行，以便尝试锁定它们的另一个事务(使用FOR SHARE，FOR UPDATE，UPDATE或DELETE)会阻塞，直到事务提交或回滚为止

Nope. FOR UPDATE locks only those rows, so that another transaction that attempts to lock them (with FOR SHARE, FOR UPDATE, UPDATE or DELETE) blocks until your transaction commits or rolls back.

如果您希望使用整个表锁来阻止插入/更新/删除操作，则可能需要LOCK TABLE ... IN EXCLUSIVE MODE.

If you want a whole table lock that blocks inserts/updates/deletes you probably want LOCK TABLE ... IN EXCLUSIVE MODE.

随后试图获取锁的查询应该失败，我尝试使用NOWAIT实现此目的，但希望使用超时方法(因为可以等待，只是不等待愚蠢的时间")

Subsequent queries trying to get the lock should fail, I have tried achieving this with NOWAIT but would prefer a timeout method (because it may be ok to wait, just not wait for a 'stupid amount of time')

请参见 lock_timeout设置.它是在9.3中添加的，在较早的版本中不可用.

See the lock_timeout setting. This was added in 9.3 and is not available in older versions.

使用statement_timeout可以实现较早版本的粗略近似，但是这可能导致不必要地取消语句.如果statement_timeout为1s，并且一条语句在锁上等待950ms，则它可能会获得锁并继续执行，直到被超时立即取消.不是你想要的.

Crude approximations for older versions can be achieved with statement_timeout, but that can lead to statements being cancelled unnecessarily. If statement_timeout is 1s and a statement waits 950ms on a lock, it might then get the lock and proceed, only to be immediately cancelled by a timeout. Not what you want.

没有查询级的方法来设置lock_timeout，但是您可以并且应该:

There's no query-level way to set lock_timeout, but you can and should just:

SET LOCAL lock_timeout = '1s';

您进行BEGIN交易之后.

理想情况下，我会在传递时取消此操作，并让我的初始查询仅将锁保持一定的时间，而使用postgresql可以吗?

Ideally I would head this off at the pass, and have my initial query only hold the lock for a certain amount of time, is this possible with postgresql?

有一个 statement 超时，但是锁保持在 transaction 级别.没有交易超时功能.

There is a statement timeout, but locks are held at transaction level. There's no transaction timeout feature.

如果您正在运行单语句事务，则可以在运行语句之前设置statement_timeout来限制它可以运行多长时间.但是，这与限制其持有锁的时间并不完全相同，因为它可能会等待900毫秒(允许的1秒钟)获得该锁，实际上只持有100毫秒，然后在超时之前被取消.

If you're running single-statement transactions you can just set a statement_timeout before running the statement to limit how long it can run for. This isn't quite the same thing as limiting how long it can hold a lock, though, because it might wait 900ms of an allowed 1s for the lock, only actually hold the lock for 100ms, then get cancelled by the timeout.

我是否可以在查询中添加其他魔术功能(类似于NOWAIT)，该功能仅等待锁定4秒钟才能失败?

Is there some other magic function I can tack onto the query (similar to NOWAIT) which will only wait for the lock for 4 seconds before failing?

不.您必须:

BEGIN;
SET LOCAL lock_timeout = '4s';
SELECT ....;
COMMIT;

由于代码库具有令人痛苦的整体式意大利面条式代码特性，它不只是更改全局配置的问题，还有点需要基于查询的解决方案

Due to the painfully monolithic spaghetti code nature of the code base, its not simply a matter of changing global configs, it kinda needs to be a per-query based solution

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

SET LOCAL is suitable, and preferred, for this.

无法在查询文本中执行此操作，它必须是单独的语句.

There's no way to do it in the text of the query, it must be a separate statement.

您链接到的邮件列表帖子是关于一种虚构语法的建议，该构想从未实现(至少在PostgreSQL的公共发行版中)并且不存在.

The mailing list post you linked to is a proposal for an imaginary syntax that was never implemented (at least in a public PostgreSQL release) and does not exist.

在这种情况下，您可能需要考虑乐观并发控制"，通常称为乐观锁定".它使您可以更好地控制锁定行为，但代价是查询重复率增加，并且需要更多的应用程序逻辑.

In a situation like this you may want to consider "optimistic concurrency control", often called "optimistic locking". It gives you greater control over locking behaviour at the cost of increased rates of query repetition and the need for more application logic.

这篇关于控制PostgreSQL锁定等待的持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！