RE: Row locks, SKIP LOCKED, and transactions

> (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 repro case. 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 just academic, but I'm still interested to know if the above behaviour is expected, and if I should have been able to deduce it 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 inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table."

Thanks for your (and everyone else's) help,

Steve.