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?