Postgres has 3 transaction isolation levels.
Read Committed
Repeatable Read
Serializable
Here I will be highlighting few points gleaned across the web which we might otherwise overlook.There is detailed explanation here.
Repeatable Read
Repeatable read though it sounds odd does not guarantee that reads are repeatable in a transaction.Phantom reads are allowed.This is as per the SQL standard.
Though in Postgres, the name and its implementation are in line i.e a statement(select) returns the same set of rows when run multiple times in the same transaction.(However the changes done in the same transaction though uncommitted are visible to successive statements.)
A repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.If it were to get into such a case, repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent updateThis is because, repeatable read has its snapshot fixed at the start of the transaction and if it has to update some rows and meanwhile what it saw initially has changed by the time it updates, it will fail as per the below contract.
The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database.
There could be some edge cases which you can find in the doc.
Serializable
The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently.
So when multiple transactions execute concurrently and if they get into a situation where in the updates of one of them could have resulted in inconsistency as they are executing concurrently and which would not have occurred if they executed one after the other, then one of the transaction is rolled back.
Serializable vs Repeatable Read:
Setup:
CREATE SCHEMA test;
CREATE TABLE test.purchase (id bigint not null);Process A: BEGIN TRANSACTION ISOLATION LEVEL Repeatable Read;
Process A: INSERT INTO test.purchase(ID) VALUES (10);
Process A: Select sum(ID) From test.purchase where ID between 1 and 100;
Process B: BEGIN TRANSACTION ISOLATION LEVEL Repeatable Read;
Process B: INSERT INTO test.purchase(ID) VALUES (10);
Process B: Select sum(ID) From test.purchase where ID between 1 and 100;
Process B: COMMIT;
Process A: COMMIT;
The above works perfectly fine in Repeatable Read.If you run the same set of statements using Serializable isolation level you get below error when Process A Commits.
Process A: COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.