> (Or you could use serializable mode, but that feels like using a hammer to swat a fly.)
Do you mean the serializable transaction isolation level? Because that doesn't work either. Here (finally) is a tiny
reprocase. You'll need 2 psql sessions (S1, S2):
S1: CREATE TABLE t (id integer):
S1: INSERT INTO t VALUES (1);
S1: BEGIN;
S1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
S1: SELECT id FROM t FOR UPDATE;
(So now there is a single, globally visible row that S1 has a lock on)
S2: BEGIN;
S2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
S2: SELECT id FROM t; -- returns 1 row, as expected
S2: SELECT id FROM t FOR UPDATE SKIP LOCKED; -- returns 0 rows, as expected
S1: ROLLBACK; -- S1's row lock is released
S2: SELECT id FROM t FOR UPDATE SKIP LOCKED; -- returns 1 row
...i.e. the row appears unlocked to S2 despite its transaction's snapshot being taken before the lock was released.
I'm going to use the suggestions made by you and others previously in this thread, so (for me at least) this is now
justacademic, but I'm still interested to know if the above behaviour is expected, and if I should have been able to
deduceit from the docs. The best I could find is:
https://www.postgresql.org/docs/11/sql-select.html
"With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an
inconsistentview of the data, so this is not suitable for general purpose work, but can be used to avoid lock
contentionwith multiple consumers accessing a queue-like table."
Thanks for your (and everyone else's) help,
Steve.
