控制 PostgreSQL 锁等待的持续时间

Controlling duration of PostgreSQL lock waits

提问by FaddishWorm

I have a table called deposits

我有一张桌子叫 deposits

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

存款时，表被锁定，所以查询看起来像：

SELECT * FROM deposits WHERE id=123 FOR UPDATE

SELECT * FROM deposits WHERE id=123 FOR UPDATE

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

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

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:

但是，当其他存款试图获得该表的锁时，就会出现问题。发生的情况是，在锁定表和调用psql_commit()某些失败并保持锁定很长时间之间的某处。有几件事我需要帮助解决：

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

2. 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?

3. 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?

4. 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

1. 尝试获取锁的后续查询应该会失败，我已经尝试过实现这一点，NOWAIT但更喜欢超时方法（因为等待可能没问题，只是不要等待“愚蠢的时间”）

2. 理想情况下，我会在通过时将其关闭，并使我的初始查询仅在一定时间内保持锁定，这可能与 postgresql 一起使用吗？

3. 是否有其他一些神奇的功能我可以添加到查询上（类似于 NOWAIT），它只会在失败之前等待锁定 4 秒？

4. 由于代码库的痛苦单一的意大利面条式代码性质，它不仅仅是更改全局配置的问题，它有点需要是基于每个查询的解决方案

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/[email protected]

感谢你们的帮助，我会继续探索，但我运气不佳。这是psql不存在的功能吗，因为我发现了这个：http: //www.postgresql.org/message-id/[email protected]

回答by Craig Ringer

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

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

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

不。FOR UPDATE只锁定那些行，使另一个事务试图锁定他们（用FOR SHARE，FOR UPDATE，UPDATE或DELETE）阻塞，直到你的事务提交或回滚。

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

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

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')

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

   Crude approximations for older versions can be achieved with statement_timeout, but that can lead to statements being cancelled unnecessarily. If statement_timeoutis 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.

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

   SET LOCAL lock_timeout = '1s';

   after you BEGINa transaction.

2. 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?

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

   If you're running single-statement transactions you can just set a statement_timeoutbefore 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.

3. 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?

   No. You must:

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

4. 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 LOCALis suitable, and preferred, for this.

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

   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.

1. 尝试获取锁的后续查询应该会失败，我曾尝试使用 NOWAIT 实现这一点，但更喜欢超时方法（因为等待可能没问题，只是不要等待“愚蠢的时间”）

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

   可以使用 实现旧版本的粗略近似statement_timeout，但这可能导致语句被不必要地取消。如果statement_timeout是 1s 并且语句在锁定上等待 950 毫秒，则它可能会获得锁定并继续，但会立即因超时而被取消。不是你想要的。

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

   SET LOCAL lock_timeout = '1s';

   在你BEGIN交易之后。

2. 理想情况下，我会在通过时将其关闭，并使我的初始查询仅在一定时间内保持锁定，这可能与 postgresql 一起使用吗？

   有一个语句超时，但锁是在事务级别持有的。没有事务超时功能。

   如果您正在运行单语句事务，您可以statement_timeout在运行语句之前设置 a以限制它可以运行的时间。但是，这与限制它可以保持锁定的时间并不完全相同，因为它可能会等待 900 毫秒（允许 1 秒的锁定时间），实际上只保持锁定 100 毫秒，然后被超时取消。

3. 是否有其他一些神奇的功能我可以添加到查询上（类似于 NOWAIT），它只会在失败之前等待锁定 4 秒？

   不，您必须：

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

4. 由于代码库的痛苦单一的意大利面条式代码性质，它不仅仅是更改全局配置的问题，它有点需要是基于每个查询的解决方案

   SET LOCAL是合适的，并且是优选的。

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

   您链接到的邮件列表帖子是对从未实现（至少在公共 PostgreSQL 版本中）并且不存在的虚构语法的提议。

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.

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