Advisory Locks or Nowait to Avoid Waiting for Locked Rows

Advisory locks or NOWAIT to avoid waiting for locked rows?

~~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):

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

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.

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

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

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

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.

On Postgres 9.4 or older 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

(Also with an implementation with FOR UPDATE SKIP LOCKED.)

Aside

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.

Put pg_try_advisory_xact_lock() in a nested subquery?

I updated my referenced answer with more explanation and links.

In Postgres 9.5 (currently beta) the new SKIP LOCKED is a superior solution:

Postgres UPDATE … LIMIT 1

Let me simplify a few things in your query first:

Straight query

UPDATE opportunities s
SET    opportunity_available = false
FROM  (
   SELECT id
   FROM   opportunities
   WHERE  deal_id = #{@deal.id}
   AND    opportunity_available
   AND    pg_try_advisory_xact_lock(id)
   LIMIT  1
   FOR    UPDATE
   ) sub
WHERE     s.id = sub.id
RETURNING s.prize_id, s.id;

All the double quotes were just noise with your legal, lower-case names.
Since opportunity_available is a boolean column you can simplify opportunity_available = true to just opportunity_available
You don't need to return * from the subquery, just id is enough.

Typically, this works as is. Explanation below.

Avoid advisory lock on unrelated rows

To be sure, you could encapsulate all predicates in a CTE or a subquery with the OFFSET 0 hack (less overhead) before you apply pg_try_advisory_xact_lock() in the next query level:

UPDATE opportunities s
SET    opportunity_available = false
FROM (
   SELECT id
   FROM  ( 
      SELECT id
      FROM   opportunities
      WHERE  deal_id = #{@deal.id}
      AND    opportunity_available
      AND    pg_try_advisory_xact_lock(id)
      OFFSET 0
      ) sub1
   WHERE  pg_try_advisory_xact_lock(id)
   LIMIT  1
   FOR    UPDATE
   ) sub2
WHERE     s.id = sub.id
RETURNING s.prize_id, s.id;

However, this is typically much more expensive.

You probably don't need this

There aren't going to be any "collateral" advisory locks if you base your query on an index covering all predicates, like this partial index:

CREATE INDEX opportunities_deal_id ON opportunities (deal_id)
WHERE opportunity_available;

Check with EXPLAIN to verify Postgres actually uses the index. This way, pg_try_advisory_xact_lock(id) will be a filter condition to the index or bitmap index scan and only qualifying rows are going to be tested (and locked) to begin with, so you can use the simple form without additional nesting. At the same time, your query performance is optimized. I would do that.

Even if a couple of unrelated rows should get an advisory lock once in a while, that typically just doesn't matter. Advisory locks are only relevant to queries that actually use advisory locks. Or do you really have other concurrent transactions that also use advisory locks and target other rows of the same table? Really?

The only other problematic case would be if massive amounts of unrelated rows get advisory locks, which can only happen with a sequential scan and is very unlikely even then.

Strange deadlock PostgreSQL deadlock issue with SELECT FOR UPDATE

The test case plays out like this:

Thread-1 runs the SELECT and acquires the record lock.
Thread-2 runs the SELECT and enters the lock's wait queue.
Thread-1 runs the UPDATE / COMMIT and releases the lock.
Thread-2 acquires the lock. Detecting that the record has changed since its SELECT, it rechecks the data against its WHERE condition. The check fails, and the row is filtered out of the result set, but the lock is still held.

This behaviour is mentioned in the FOR UPDATE documentation:

...rows that satisfied the query conditions as of the query snapshot will be locked, although they will not be returned if they were updated after the snapshot and no longer satisfy the query conditions.

This can have some unpleasant consequences, so a superfluous lock isn't that bad, all things considered.

Probably the simplest workaround is to limit the lock duration by committing after every iteration of acquire. There are various other ways to prevent it from holding this lock (e.g. SELECT ... NOWAIT, running in a REPEATABLE READ or SERIALIZABLE isolation level, SELECT ... SKIP LOCKED in Postgres 9.5).

I think the cleanest implementation using this retry-loop approach would be to skip the SELECT altogether, and just run an UPDATE ... WHERE locked = false, committing each time. You can tell if you acquired the lock by checking cur.rowcount after calling cur.execute(). If there is additional information you need to pull from the lock record, you can use an UPDATE ... RETURNING statement.

But I would have to agree with @Kevin, and say that you'd probably be better off leveraging Postgres' built-in locking support than trying to reinvent it. It would solve a lot of problems for you, e.g.:

Deadlocks are automatically detected
Waiting processes are put to sleep, rather than having to poll the server
Lock requests are queued, preventing starvation
Locks would (generally) not outlive a failed process

The easiest way might be to implement acquire as SELECT FROM my_locks FOR UPDATE, release simply as COMMIT, and let the processes contend for the row lock. If you need more flexibility (e.g. blocking/non-blocking calls, transaction/session/custom scope), advisory locks should prove useful.

WAIT or NOWAIT? That is the question. PosgreSQL vs ORACLE

There is possibility to configure lock timeout at posgreSQL server configuration.

Change parameter lock_timeout = '30s' in pgsql/11/data/postgresql.conf
After that reload configuration or restart postgreSQL

Controlling duration of PostgreSQL lock waits

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

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.

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

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_timeout setting. 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_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.
There's no query-level way to set lock_timeout, but you can and should just:
SET LOCAL lock_timeout = '1s';
after you BEGIN a transaction.
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 statement timeout, but locks are held at transaction level. There's no transaction timeout feature.
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.
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;
```
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 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.
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.

How to list all locked rows of a table?

Is it possible? Probably yes, but it is the Greatest Mystery of Postgres. I think you would need to write your own extension for it (*).

However, there is an easy way to work around the problem. You can use very nice Postgres feature, advisory locks. Two arguments of the function pg_try_advisory_lock(key1 int, key2 int) you can interpret as: table oid (key1) and row id (key2). Then

select pg_try_advisory_lock(('master'::regclass)::integer, 123456)

locks row 123456 of table master, if it was not locked earlier. The function returns boolean.

After update the lock has to be freed:

select pg_advisory_unlock(('master'::regclass)::integer, 123456)

And the nicest thing, list of locked rows:

select classid::regclass, objid
from pg_locks
where locktype = 'advisory'

Advisory locks may be complementary to regular locks or you can use them independently. The second option is very temptive, as it can significantly simplify the code. But it should be applied with caution because you have to make sure that all updates (deletes) on the table in all applications are performed with this locking.

(*) Mr. Tatsuo Ishii did it (I did not know about it, have just found).

Advisory Locks or Nowait to Avoid Waiting for Locked Rows