The REPEATABLE_READ transaction isolation level of PostgreSQL 12 prevents dirty reads, non-repeatable reads, and phantom reads. In contrast to the READ_COMMITTED isolation level, the REPEATABLE_READ isolation level prevents non-repatable reads and phantom reads.
I guess that this comes with a cost, otherwise one would just make both equal. How does postgres guarantee that those 3 read phenomena don't occur?
CodePudding user response:
READ COMMITTED
and REPEATABLE READ
are using the same technology: a snapshot that determines which of the versions of a row in the table a transaction can see. The difference is that with READ COMMITTED
, the snapshot is taken at the start of each statement, so that each new statement can see everything that has been committed before, while a REPEATABLE READ
transaction uses the same snapshot for all statements.
There are two consequences:
if anything,
REPEATABLE READ
is cheaper thanREAD COMMITTED
, because it takes fewer snapshotsREPEATABLE READ
provides even higher isolation than required by the SQL standard – the database does not seem to change at all
The price you are paying for REPEATABLE READ
is different:
you risk serialization errors, which force you to repeat the transaction
VACUUM
cannot clean up rows marked dead after theREPEATABLE READ
transaction started