Postgres: Opting in to weaker transaction isolation guarantees for a single table or a single read?

user_163832 0
Viewed 40

I'm writing a web app with Postgres 13 as the backend. Most requests are wrapped in a transaction, using the SERIALIZABLE isolation level.

For most things, this works great. However, there are some cases where I'd like some reads in the transaction to have less strict isolation.

For example, I'm introducing a global_flags table for infrequently-changed settings that any request might make use of:

await sqlAsync(`BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE`);

const batchSize = await sqlAsync(
    `SELECT value FROM global_flags WHERE name = 'batch_size'`);

// ... a bunch more work ...

await sqlAsync('COMMIT');

I'm a bit worried that when we manually make changes to global_flags entries, we might cause an increase in "serialization failure" errors for in-flight transactions. Is there a way to tell Postgres that I don't need as strong of a consistency guarantee for reads of the global_flags table?

postgresql
1 Answers

You needn't worry a bit.

If the one transaction does nothing except change that flag, and the other just reads the table (and doesn't try to write to it!), the two transactions will have a simple RW or WR dependency on each other, and there will be no cycles that cause a serialization error.

user_64643080
Related