咨询锁或NOWAIT以避免等待锁定的行？ [英] Advisory locks or NOWAIT to avoid waiting for locked rows?

问题描述

在我的Rails 4应用程序中，我有一个对Postgres 9.4 数据库的查询：

In my Rails 4 app, I have this query to a Postgres 9.4 database:

@chosen_opportunity = Opportunity.find_by_sql(
  " UPDATE \"opportunities\" s
    SET opportunity_available = false
    FROM (
          SELECT \"opportunities\".*
          FROM   \"opportunities\"
          WHERE  ( deal_id = #{@deal.id}
          AND    opportunity_available = true 
          AND    pg_try_advisory_xact_lock(id) )
          LIMIT  1
          FOR    UPDATE
          ) sub
    WHERE       s.id = sub.id
    RETURNING   sub.prize_id, sub.id"
) 

很多灵感来自 dba的相关答案.SE 。

我只想让我的查询查找和更新第一个（随机， LIMIT ）row其中 available = true 并将其更新为 available = false ，我需要锁定行这是因为有很多并发调用会使用此查询。

I just want my query to find and update the first (randomly, with LIMIT) row where available = true and update it to available = false, and I need to lock the row while doing this, but without making new requests waiting for the release of the previous lock as there are many concurrent calls that will use this query.

但是，我还看到 NOWAIT 选项 FOR UPDATE 。我不知道我理解使用 pg_try_advisory_xact_lock（）和 NOWAIT 选项之间的区别，他们似乎对我达到相同的目标：

But I also saw the NOWAIT option to FOR UPDATE. I'm not sure I understand the difference between using pg_try_advisory_xact_lock() and the NOWAIT option, they seem to me to achieve the same goal:

• NOWAIT 的目标：

为了防止操作等待其他事务提交，请使用 NOWAIT 选项。

pg_try_advisory_xact_lock 的目标

不是等待前一个事务释放锁，并且仍然能够执行另一个事务，并且只操作下一个选择以更新'not

pg_try_advisory_xact_lock's goal
is not to wait for the previous transaction to release the lock and still be able to do another transaction and only operate the next select for update the 'not yet locked' rows.

哪一个更适合我的需要？

Which one is better suited to my need?

推荐答案

FOR UPDATE NOWAIT 如果你坚持锁定一个特定的行，这是 不 你需要什么。您只需要任何限定，可用（未锁定）行。重要的区别在于此（引用手册for Postgres 9.4 ）：

FOR UPDATE NOWAIT is only a good idea if you insist on locking a particular row, which is not what you need. You just want any qualifying, available (unlocked) row. The important difference is this (quoting the manual for Postgres 9.4):

使用 NOWAIT

相同的查询很可能会尝试锁定相同的任意挑。 FOR UPDATE NOWAIT 将只会释放一个异常（这将回滚整个事务，除非你陷阱的错误），你必须重试。

Identical queries will very likely try to lock the same arbitrary pick. FOR UPDATE NOWAIT will just bail out with an exception (which will roll back the whole transaction unless you trap the error) and you have to retry.

我在dba.SE中引用的答案中的解决方案结合使用 FOR UPDATE 与 pg_try_advisory_lock（） ：

The solution in my referenced answer on dba.SE uses a combination of plain FOR UPDATE in combination with pg_try_advisory_lock():

pg_try_advisory_lock 类似于p g_advisory_lock ，除了
函数不会等待锁可用。它将
立即获得锁并返回true，或如果
的锁无法立即获取则返回false。

pg_try_advisory_lock is similar to pg_advisory_lock, except the function will not wait for the lock to become available. It will either obtain the lock immediately and return true, or return false if the lock cannot be acquired immediately.

因此，您的最佳选项是第三个选项：Postgres中的新 FOR UPDATE SKIP LOCKED 9.5（目前为beta版），它实现相同的行为而不需要额外的函数调用。

So your best option is ... the third alternative: the new FOR UPDATE SKIP LOCKED in Postgres 9.5 (currently beta), which implements the same behavior without additional function call.

Postgres 9.5的手册比较了两个选项，更多地解释了区别：

The manual for Postgres 9.5 compares the two options, explaining the difference some more:

要阻止操作等待其他事务到
commit，请使用 NOWAIT 或 SKIP LOCKED 选项。使用 NOWAIT ，
语句报告错误，而不是等待，如果所选行
不能立即锁定。使用 SKIP LOCKED ，将跳过
不能立即锁定的任何选定行。

To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped.

在等待新版本时，下一个最佳选项是使用 pg_try_advisory_xact_lock（id）与 FOR UPDATE ，如引用的答案中所示：

While still waiting for the new version, your next best option is to use pg_try_advisory_xact_lock(id) in combination with FOR UPDATE like demonstrated in the referenced answer:

• Postgres UPDATE ... LIMIT 1

• Postgres UPDATE … LIMIT 1

FOR UPDATE SKIP LOCKED 。）

严格来说，不是真正随机选择。这是一个重要的区别。

您的查询的审核版本在我对您的其他问题的回答。

Strictly speaking you get arbitrary, not truly random picks. That can be an important distinction.
An audited version of your query is in my answer to your other question.

这篇关于咨询锁或NOWAIT以避免等待锁定的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！