Transaction Isolation Levels

[cmcgowanprovidertrust]

Is River compatible with all Postgres isolation levels? Looking through the documentation I have not seen any discussion around this so far.

I became curious about this after stumbling upon the blog post about using advisory locks to support unique jobs https://riverqueue.com/blog/uniqueness-with-advisory-locks. I was wondering how this technique managed to work across different isolation levels, as I had also read another piece that said transaction-level advisory locks would face write skew problems under Repeatable Read (see https://dansvetlov.me/postgres-anomalies/#write-skew-with-disjoint-write-sets).

Looking through the code (on the older versions that still had this technique) I did not see anything that seemed to avoid the Repeatable Read anomaly, but I am also a Go novice so I could very well be missing something. But it got me wondering whether or not River officially supported non-default isolation levels.

[brandur]

River's not extensively tested at all isolation levels, but I would think it should generally work as well (or a little better) than most Postgres code as it tries to follow all known best practices as well as we're able to.

For example, using read committed for many years in production, the only anomaly I've ever experienced personally is non-repeatable read, and River should largely have that covered as it locks rows that it may later modify.

Looking through the code (on the older versions that still had this technique) I did not see anything that seemed to avoid the Repeatable Read anomaly, but I am also a Go novice so I could very well be missing something.

As you seem to have noticed already, the library no longer uses advisory locks for unique operations, but rather a unique index, which is generally a more widely understood known quantity, and less susceptible to contention.

Just since you're the one that started the discussion, I want to flip the onus of analysis back to you — can you try to describe a specific scenario in River (e.g. lock work -> work -> complete work) that you're thinking would be at risk of a transaction anomaly and why? I may be able to take a closer look at that and advise further from there.

I would expect some trouble at tighter isolation levels like SERIALIZABLE, but more the usual type of trouble that you tend to have there like increased contention that might lead to serialization failures. That doesn't sound like that's the type of problem you're talking about though.

[cmcgowanprovidertrust]

Thanks for the reply! I also expect that River works quite well in most circumstances. I don't actually run River myself, but I will sometimes come check on the documentation and code as a way to improve my own understanding of Postgres best practices. I find it quite helpful as a reference implementation in that way.

You are right that I am not concerned about increased serialization failures, which I'd expected when going higher than READ COMMITTED. I was more curious about situations where different features may require specific isolation levels for correctness. Like how the PgBouncer documentation notes that transaction pooling mode is only compatible with certain River Client patterns.

I'm not currently aware of any other scenarios that would be impacted, but I can walk through my concern with the older unique job implementation:

Consider a system with high concurrency. Two River Clients C1 and C2 simultaneously try to insert the same unique job. Each client opens a transaction (T1 and T2), acquires an advisory_xact lock, check for an existing unique job, and insert if none is found. T1 acquires the lock first and T2 blocks. In this situation, I think the outcome depends on the isolation level like this.

• Under READ_COMMITTED: T1 finds no existing job and inserts. T2 becomes unblocked and runs its SELECT. It sees the new row inserted by T1 and exits. Both clients finish cleanly and only one unique job has been created.
• Under SERIALIZABLE: T1 finds no existing job and inserts. T2 becomes unblocked and runs its SELECT, but does not see the row inserted by T1 (since it is using a snapshot pinned to a time prior to T1's commit). So T2 tries to insert as well. Postgres errors because it detects a serialization anomaly. We still correctly end with just one unique job, though C2 will see an error it needs to handle.
• Under REPEATABLE READ: T1 finds no existing job and inserts. T2 becomes unblocked and runs its SELECT, but does not see the row inserted by T1 (since it is using a snapshot pinned to a time prior to T1's commit). So T2 tries to insert as well. Postgres does not error here, since each write operation touched a disjoint set of rows. Both clients finish cleanly, but two duplicate unique jobs have been created.

To me it feels like REPEATABLE READ specifically can be a bit of a footgun in any situation where two transactions want to read some shared data then write to disjoint data. But besides unique jobs, I am not really sure where else that kind of scenario might arise. Perhaps in some of the more complex features like Workflows?