Transaction isolation

[tamlyn]

I love what you're doing here. It's very timely for me as I'm looking to implement a ledger in a system backed by Postgres but, unlike you, this will be my first time!

Could you help me understand the appropriate transaction isolation level to use? Does the locking mean it is safe even at default READ COMMITTED, or would REPEATABLE READ or SERIALIZABLE be required?

[pgr0ss]

I believe it is safe to use at the default READ COMMITTED isolation level. Any updates to the ledger use locking to ensure they run one at a time for affected accounts.

The read story is a little different, though. Subsequent reads from the ledger tables may see different results, even within the same database transaction, if other ledger operations have committed (i.e. you don't get a snapshot of the whole database at the beginning of a database transaction). For that, you would need repeatable read:

This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction. Thus, successive SELECT commands within a single transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started.

https://www.postgresql.org/docs/17/transaction-iso.html#XACT-REPEATABLE-READ

Please let me know if this helps clarify, or if there are certain scenarios or usage patterns you are worried about.