Postgres Repeatable Read vs Serializable

Question

I'm trying to understand whether the repeatable read isolation level is good enough for my scenario in an application that uses Postgres, but the docs are making it difficult to understand which is best suited.

I have an application that starts a transaction that first reads the current value of a single row based on the primary key of that row, calculates what the new state of that row should be after receiving an event from a message queue, and then updates the state of that row in the database.

Given that there are multiple instances of the application deployed, and that multiple events on the message queue can be received for the same primary key at the same time, it's possible that 2 transactions could attempt to enter the above described transaction at the same time. Is repeatable read isolation good enough for this case, or do I need to consider using serializable? My assumption is that if the first transaction attempts to commit its result to the database whilst the second transaction is in progress, the second transaction will fail with a conflict because it sees that the row it was attempting to update was modified by the first transaction - is this correct?

And a follow-up question: I'd like to understand how much more 'expensive' it is to use serializable transaction isolation vs repeatable read - I want to understand the underlying mechanism of what's going on within postgres - i.e. is there locking going on, and how does that affect performance of other queries running at the same time?

Answer 1

REPEATABLE READ is sufficient for your case. It will by definition prevent a “lost update”.

SERIALIZABLE is quite a bit more expensive than REPEATABLE READ, which is the “cheapest” of all isolation levels. More locks will be taken (SI locks that don't block anything, but can cause a transaction to abort), and these locks have to survive a commit. It is impossible to name a figure how much more expensive it will be; that depends on your workload.

Answer 2

Why not stick with the default transaction isolation level of read committed which everyone expects and understands best?

Use optimistic locking, for example

update <table>
  set <column> = <new value>
  where id = <id>
  and <column> = <old value>

If that update fails, it means someone else got there before you and you should try again. Depending on your exact use case, there may be optimisations you can make to prevent this from happening a lot.

Or maybe you can make your statement atomic depending on the exact function or expression you are updating your columns with?

update <table>
  set <column> = <expression>(<column>)
  where id = <id>