MVCC
Isolation
16
Copyright
© Postgres Professional, 2017–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Transactions and their Properties
Isolation Levels in SQL Standard
Implementation in PostgreSQL
3
Transactions and their Properties
Transaction is a sequence of operations
that transitions the database from one consistent state
to another,
provided that the transaction is completed successfully,
without interference from other transactions
atomicity guarantees that either all operations within the transaction succeed,
or none of them make any visible changes
consistency is defined by integrity constraints and application semantics
incomplete isolation leads to correctness violations (anomalies)
during concurrent transaction execution
Consistency
Atomicity
Isolation
A transaction is a sequence of operations performed by an application that
transitions the database from one consistent state to another (consistency),
provided that the transaction is completed successfully (atomicity), without
interference from other transactions (isolation).
A correct (consistent) state is defined both by integrity constraints set in the
database and by application semantics.
Full isolation guarantees that application operations are not affected by any
other concurrent transactions. However, when weaker isolation is applied,
a transaction that operates correctly alone may produce incorrect results in
the presence of concurrent transactions, since statements from different
transactions can be interleaved during execution. Such incorrect situations
are called anomalies.
Full isolation is difficult to implement and it often results in lower system
throughput. Since relaxed isolation is standard practice, it is important to
understand its potential implications.
We will cover the fourth ACID property (durability) in Write-Ahead Log
lesson.
4
Read Uncommitted
Should prevent anomalies
lost update:
a transaction can overwrite updates made by other transactions
that were committed after it started
Not implemented in PostgreSQL
functions as Read Committed
The SQL standard defines four levels of transaction isolation
in terms of anomalies that may occur during concurrent transaction
execution.
The weakest level is Read Uncommitted, which, as the name implies,
allows reading even uncommitted data.
According to the standard, this level should prevent only one anomaly:
●
Lost update. If T1 has started, and then another T2 modifies and
commits rows, T1 might overwrite those changes.
This level was designed for minimal overhead, but PostgreSQL can operate
at a stricter level with no loss of performance. Therefore, Read
Uncommitted is not implemented in PostgreSQL, and we will not discuss it
further.
5
Read Committed
Should prevent anomalies
lost update
dirty read:
a query sees uncommitted changes made by other transactions
Implementation in PostgreSQL
used by default
Each subsequent isolation level is stricter than the previous one. Therefore,
the Read Committed level must prevent not only lost updates, but another
anomaly as well:
●
Dirty read. T1 can read rows that have been modified by T2 but not yet
committed. If T2 rolls back its changes, T1 will have read data that never
actually existed.
There are many other anomalies that are allowed at the Read Committed
level. The developer should be always aware of potential issues and
manually apply locks when necessary.
In PostgreSQL (as in many other DBMSes), this isolation level is used by
default as a compromise between isolation strictness and performance.
Note that in PostgreSQL, the Read Committed level can lead to lost
updates, an example will be provided in demonstration.
7
Repeatable Read
Should prevent anomalies
lost update
dirty read
non-repeatable read:
reading the same row again returns different data
if it was modified and committed by another transaction
Implementation in PostgreSQL
also prevents phantom read:
when a transaction re-executes a query, the returned set of rows can change
if another transaction has inserted and committed new rows that satisfy the same
condition
to prevent anomalies, transactions may be aborted
According to the standard, in addition to lost update and dirty read, the
Repeatable Read isolation level should also prevent the anomaly we
observed at the Read Committed level:
●
Non-Repeatable Read. After T1 reads a row, T2 modifies or deletes that
row and commits the changes. When T1 reads the same row again, it
sees that the row has been modified or deleted.
PostgreSQL's implementation is stricter than the standard requires and it
also prevents the anomaly.
●
Phantom Read. T1 reads a set of rows that satisfy a certain condition.
T2 then inserts rows that also satisfy this condition. If T1 repeats the
query, it will receive a different set of rows.
In reality, there are other anomalies (not covered in the standard) that are
allowed in PostgreSQL at the Repeatable Read level.
Another key difference from Read Committed is that this level may abort
a transaction to prevent an anomaly (such a transaction must be retried).
This never happens at the Read Committed level: when there is a choice
between correctness and performance, performance is always prioritized
over correctness.
9
Serializable
Should prevent any anomalies
the result of concurrent execution matches the result of executing transactions in some
serial order
Implementation in PostgreSQL
to prevent anomalies, transactions may be aborted
for correct operation, the Serializable level must be used by all transactions
the level does not work on standbys
Complete elimination of all anomalies is achieved when commands
executed in concurrent transactions produce the same result as if the
transactions were run sequentially (one finishes, the next begins) in some
order.
In PostgreSQL, this level is implemented and works correctly if all
transactions in the system use the Serializable isolation level.
However, it is important to note that this implementation has certain
limitations:
●
Queries cannot be executed on standbys.
●
Transactions may be aborted more frequently than strictly necessary
(thereby reducing performance as they must be retried).
11
Takeaways: SQL Standard
Lost Update Dirty Read
Non-
Repeatable
Read
Phantom
Read
Other
Anomalies
Read Uncommitted ― possible possible possible possible
Read Committed ― ― possible possible possible
Repeatable Read ― ― ― possible possible
Serializable ― ― ― ― ―
Let’s summarize the information.
The SQL standard defines four transaction isolation levels:
Read Uncommitted, Read Committed, Repeatable Read, Serializable
(by default).
The table shows which anomalies can occur at each isolation level.
Note that eliminating three anomalies defined in the standard is not sufficient
to guarantee the Serializable isolation level.
12
Takeaways: PostgreSQL
* not always prevented
Lost Update Dirty Read
Non-
Repeatable
Read
Phantom
Read
Other
Anomalies
Read Uncommitted
Read Committed * ― possible possible possible
Repeatable Read ― ― ― ― possible
Serializable ― ― ― ― ―
In practice, DBMSes, including PostgreSQL, do not implement the standard
requirements literally (they were formulated when isolation theory was not
yet thoroughly studied by researchers). The isolation level names have
remained, but their implementation is stricter than the standard requires.
PostgreSQL uses snapshot isolation combined with MVCC. This results in
the following characteristics:
●
Dirty reads are completely prevented, with no loss of performance.
●
The Read Committed level is used by default and is applied in most
systems. It often requires manual locking to ensure correctness.
●
The Repeatable Read level prevents not only non-repeatable reads but
also phantom reads (though it does not guarantee full isolation). This
level is convenient for reporting, as it allows multiple queries to see
consistent data, and read‑only transactions are never aborted.
●
The Serializable level is also fully implemented, but it has certain
limitations.
13
Practice
1. Create an empty table.
Make sure phantom read anomaly is not prevented at the Read
Committed isolation level.
2. Open a transaction at the Repeatable Read isolation level
(and do not execute any commands in it yet).
In another session, delete a row from the table.
Is the deleted row visible in the open transaction?
Will the deleted row be visible if, after opening the transaction,
you execute a database query without accessing any tables?
3. Check that the DROP TABLE command is transactional.
