sql - 建议锁定或 NOWAIT 以避免等待锁定的行？

Question

在我的 Rails 4 应用程序中，我对 Postgres 9.4 数据库进行了以下查询:

@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"
) 

非常受 this related answer on dba.SE 启发.

我只想让我的查询找到并更新第一个(随机地，使用 LIMIT)行，其中 available = true 并将其更新为 available = false，并且我需要在执行此操作时锁定该行，但不需要等待释放前一个锁定而发出新请求，因为有许多并发调用将使用此查询。

但我还看到了 FOR UPDATE 的 NOWAIT 选项。我不确定我是否理解使用 pg_try_advisory_xact_lock() 和 NOWAIT 选项之间的区别，在我看来它们实现了相同的目标:

• NOWAIT's goal :

  To prevent the operation from waiting for other transactions to commit, use the NOWAIT option.

• pg_try_advisory_xact_lock's goal
  不是等待上一个事务释放锁，仍然可以做下一个事务，只操作下一个select更新'还没有加锁'的行。

哪个更适合我的需求？

Answer 1

FOR UPDATE NOWAIT 只有在您坚持锁定特定行时才是一个好主意，而这不是 你需要什么。您只需要任何 符合条件的可用(未锁定)行。重要的区别在于(quoting the manual for Postgres 9.4):

With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately.

相同的查询很可能会尝试锁定相同的任意选择。 FOR UPDATE NOWAIT 只会抛出异常(这将回滚整个事务，除非您捕获错误)并且您必须重试。

我在 dba.SE 上引用的答案中的解决方案结合了普通 FOR UPDATE 和 pg_try_advisory_lock() :

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 9.5 中新的FOR UPDATE SKIP LOCKED，它实现了相同的无需额外函数调用的行为。

The manual for Postgres 9.5比较这两个选项，进一步解释差异:

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.

在 Postgres 9.4 或更早版本上，您的下一个最佳选择是结合使用 pg_try_advisory_xact_lock(id) 和 FOR UPDATE，如引用文献中所示回答:

• Postgres UPDATE … LIMIT 1

(还有一个带有 FOR UPDATE SKIP LOCKED 的实现。)

在旁边

严格来说，您的选择是任意的，而不是真正的随机选择。这可能是一个重要的区别。
您查询的审核版本在 my answer to your other question 中.