Can PostgreSQL Serializable be safely mixed with lower isolation levels?

Question

I'm trying to figure out what the behaviour is when mixing Serialisable and lower levels of isolation, but not having much luck. Specifically, we have a reorder-queue-items transaction that is currently taking the table lock to ensure no new items will be added to it in the meantime. Will making it Serialised and reading the head of the queue (to ensure a dependency on the result) provide the same level of guarantee, even if other transactions operate at Repeatable Read?

Using PostgreSQL 9.5.

PS. I know there's another question with a similar title, but it's over 4 years old, much less specific in what it asks, and the only answer is essentially unsourced given the cited materials, and doesn't truly answer the question.

Answer 1

To guarantee serializability, all participating transactions should use the SERIALIZABLE isolation level.

But I am not certain that serializable isolation is the solution to your problem. It won't block anybody from reading from or writing to the queue, it will make some transactions fail, and the failing transaction might well be the one that is trying to reorder the queue.

I think that a lock is the way to go in such a case. Using serializable transactions will affect performance just as bad, the difference being that rather than waiting, you have to keep retrying transactions until reordering succeeds.