01 Lesson 01: MVCC / Isolation

Page 1: MVCC / Isolation

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.

Page 2: Topics

• Transactions and their Properties
• Isolation Levels in SQL Standard
• Implementation in PostgreSQL

Page 3: Transactions and their Properties

Transaction is a sequence of operations

• 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
  • 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

Speaker notes

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.

Page 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

Speaker notes

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.

Page 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

Speaker notes

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.

Page 6: Isolation Level Control

For demonstration purposes, we will use a separate database for each lesson.

=> CREATE DATABASE arch_isolation;

CREATE DATABASE

=> \c arch_isolation

You are now connected to database "arch_isolation" as user "student".

We examine isolation levels using a table representing a traffic light:

=> CREATE TABLE lights(
  id integer GENERATED ALWAYS AS IDENTITY,
  lamp text,
  state text
);

CREATE TABLE

It is a pedestrian traffic light with two lamps:

=> INSERT INTO lights(lamp,state) VALUES
    ('red', 'on'), ('green', 'off');

INSERT 0 2

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

One way to set the isolation level is with the SET TRANSACTION command at the start of a transaction:

=> BEGIN;

BEGIN

=> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET

To see the current level, check the parameter value:

=> SHOW transaction_isolation;

 transaction_isolation
-----------------------
 read committed
(1 row)

=> COMMIT;

COMMIT

Alternatively, we can specify the level directly in the BEGIN command:

=> BEGIN ISOLATION LEVEL READ COMMITTED;

BEGIN

=> COMMIT;

COMMIT

The Read Committed level is used by default:

=> SHOW default_transaction_isolation;

 default_transaction_isolation
-------------------------------
 read committed
(1 row)

Page 7: Page 7

So if this parameter has not been changed, we do not need to specify the level explicitly.

=> BEGIN;

BEGIN

=> SHOW transaction_isolation;

 transaction_isolation
-----------------------
 read committed
(1 row)

=> COMMIT;

COMMIT

Read Committed and Dirty Reads

Let’s try to read dirty data. In the first transaction, we turn off the red lamp:

=> BEGIN;

BEGIN

=> UPDATE lights SET state = 'off' WHERE lamp = 'red';

UPDATE 1

Start a second session.

student$ psql arch_isolation

Here, open another transaction with the same Read Committed level.

=> BEGIN;

BEGIN

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

The second transaction does not see uncommitted changes. Roll back the change.

=> ROLLBACK;

ROLLBACK

=> ROLLBACK;

ROLLBACK

Read Committed and Committed Changes

We have checked that a transaction does not see uncommitted changes. Let’s see what happens upon commit.

=> BEGIN;

BEGIN

=> UPDATE lights SET state = 'off' WHERE lamp = 'red';

UPDATE 1

=> BEGIN;

BEGIN

=> SELECT * FROM lights ORDER BY id;

Page 8: Page 8

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

Not visible yet.

=> COMMIT;

COMMIT

Let’s check again.

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)

=> COMMIT;

COMMIT

So, at Read Committed level, statements in one transaction see committed changes in other transactions.

=> SELECT *, pg_sleep(2) FROM lights ORDER BY id;

=> UPDATE lights SET state = 'on';

UPDATE 2

 id | lamp  | state | pg_sleep
----+-------+-------+----------
  1 | red   | off   |
  2 | green | off   |
(2 rows)

So, if another transaction commits changes during a statement’s execution, those changes are not visible. The statement sees data as it existed at the start of its execution. However, if a query calls a volatile function that itself executes a query, then the internal query may return data inconsistent with the main query’s snapshot.

=> CREATE FUNCTION get_state(lamp text) RETURNS text
LANGUAGE sql VOLATILE
RETURN (SELECT l.state FROM lights l WHERE l.lamp = get_state.lamp);

CREATE FUNCTION

Repeat the experiment, but now the query uses the function:

=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;

=> UPDATE lights SET state = 'off';

UPDATE 2

 id | lamp  | state | get_state | pg_sleep
----+-------+-------+-----------+----------
  1 | red   | on    | on        |
  2 | green | on    | off       |
(2 rows)

The right option here is to declare the function as stable.

=> ALTER FUNCTION get_state STABLE;

ALTER FUNCTION

Page 9: Page 9

=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;

=> UPDATE lights SET state = 'on';

UPDATE 2

 id | lamp  | state | get_state | pg_sleep
----+-------+-------+-----------+----------
  1 | red   | off   | off       |
  2 | green | off   | off       |
(2 rows)

Key takeaway: pay close attention to function volatility categories at the Read Committed isolation level. Using default values can easily lead to inconsistent data.

Read Committed and Lost Updates

What happens when two transactions try to change the same row? Currently both lights are on. The first transaction turns off the red light, but is not completed yet.

=> BEGIN;

BEGIN

=> UPDATE lights SET state = 'off' WHERE lamp = 'red';

UPDATE 1

The second transaction sees that both lights are on…

=> BEGIN;

BEGIN

=> SELECT * FROM lights;

 id | lamp  | state
----+-------+-------
  2 | green | on
  1 | red   | on
(2 rows)

… it changes the state of each light…

=> UPDATE lights
SET state = CASE WHEN state = 'on' THEN 'off' ELSE 'on' END;

… but hangs, waiting for the first to complete.

=> COMMIT;

COMMIT

UPDATE 2

When the first transaction is committed, the second proceeds with its update, and we expect that both lights will be off in the second session. Let’s check it.

=> COMMIT;

COMMIT

=> SELECT * FROM lights;

 id | lamp  | state
----+-------+-------
  2 | green | off
  1 | red   | on
(2 rows)

Why did it happen? The UPDATE command in the second transaction locks the tuples one by one. First, it locks the green light and changes its state, then waits for the first transaction to release the lock on the red light. At the same time, the command in the second transaction should not see changes made after it started. On the other hand, it should not lose changes committed by other transactions. Therefore, after the lock is released, it rereads the row it is trying to update.

Page 10: Page 10

So, the first transaction turned off the red light, and the second turned it back on. As a result, the second transaction managed to succeed because it saw inconsistent data: some for one point in time, some for another. But if a change is not made in a single SQL command, the update is lost.

=> BEGIN;

BEGIN

The first transaction sets the red light into blinking mode. At the same time, the row is blocked:

=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';

UPDATE 1

In the second transaction, the client sees that the red light is on…

=> BEGIN;

BEGIN

=> SELECT state AS old_state FROM lights WHERE lamp = 'red';

 old_state
-----------
 on
(1 row)

… and turns it off:

=> -- binding parameter value
\bind 'off'

=> UPDATE lights SET state = $1 WHERE lamp = 'red';

The second transaction waits for the first to complete.

=> COMMIT;

COMMIT

UPDATE 1

=> COMMIT;

COMMIT

The first session expects to see the red lamp blinking. Let’s check it:

=> SELECT * FROM lights WHERE lamp = 'red';

 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)

In this case, the second transaction wrote its changes over the changes of the first transaction. At the Read Committed level, the server cannot prevent this, as the UPDATE command actually contains a predefined constant.

Page 11: 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

Speaker notes

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.

Page 12: Repeatable Read and Non-Repeatable Reads

Make sure there are no non-repeatable read anomalies.

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT * FROM lights WHERE lamp = 'red';

 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)

=> UPDATE lights SET state = 'on' WHERE lamp = 'red' RETURNING *;

 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)

UPDATE 1

What is the value in the first transaction now?

=> SELECT * FROM lights WHERE lamp = 'red';

 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)

At Repeatable Read level, rereading the modified row returns its original value.

=> COMMIT;

COMMIT

Repeatable Read and Phantom Reads

Phantom rows should also be invisible. Let’s check it:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT * FROM lights WHERE state = 'off';

 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)

=> INSERT INTO lights(lamp,state) VALUES ('yellow', 'off')
    RETURNING *;

 id |  lamp  | state
----+--------+-------
  3 | yellow | off
(1 row)

INSERT 0 1

=> SELECT * FROM lights WHERE state = 'off';

 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)

Indeed, the transaction does not see the inserted row which satisfies the original condition. PostgreSQL’s Repeatable Read isolation level is stricter than required by the SQL standard.

Page 13: Repeatable Read and Lost Updates

=> COMMIT;

COMMIT

Let’s remove the yellow lamp.

=> DELETE FROM lights WHERE lamp = 'yellow';

DELETE 1

The transaction should always see the same data as it sees at the start, so it cannot reread the changed row during an update. Let’s reproduce the same experiment as we made at the Read Committed isolation level. Currently the red lamp is on.

=> SELECT * FROM lights WHERE lamp = 'red';

 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)

=> BEGIN;

BEGIN

=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';

UPDATE 1

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

The client queries the state of the red lamp…

=> SELECT state AS current_state FROM lights WHERE lamp = 'red';

 current_state
---------------
 on
(1 row)

… and then changes it on the server:

=> -- binding parameter value
\bind 'off'

=> UPDATE lights SET state = $1 WHERE lamp = 'red';

The second transaction waits for the first to complete.

=> COMMIT;

COMMIT

ERROR:  could not serialize access due to concurrent update

In the second transaction, we get an error. The row was changed; it is impossible to update an outdated version (this would be a lost update, which is not allowed), and it is also impossible to see the current version (this would violate isolation level).

=> ROLLBACK;

ROLLBACK

Thus, a lost update is not allowed at the Repeatable Read level.

Repeatable Read and Other Anomalies

Can we be sure that the following command will turn on all the lamps?

UPDATE lights SET state = 'on' WHERE state != 'on';

Can we be sure that the following command will turn off all the lamps?

UPDATE lights SET state = 'off' WHERE state != 'off';

If one transaction turns lamps on and another turns them off, what is the state of the table after executing both transactions concurrently?

Page 14: Page 14

Let’s turn the red lamp on and check.

=> UPDATE lights SET state = 'on' WHERE lamp = 'red';

UPDATE 1

Initial state:

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> UPDATE lights SET state = 'on' WHERE state != 'on';

UPDATE 1

The first transaction turned on the green lamp; so within this transaction all lights are now on.

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

The second transaction does not see these uncommitted changes and considers the green light to be off. Therefore, it turns off the red light; so within this transaction all lights are now off.

=> UPDATE lights SET state = 'off' WHERE state != 'off';

UPDATE 1

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)

Now both transactions commit their changes…

=> COMMIT;

COMMIT

=> COMMIT;

COMMIT

And it turns out that…

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | on
(2 rows)

… one light is on and the other is off. This is an example of a concurrency anomaly known as write skew, which is possible even if there are no dirty, non-repeatable, and phantom reads.

Page 15: 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

Speaker notes

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).

Page 16: Serializable

Serializable is the highest isolation level, designed to prevent all concurrency anomalies.

=> BEGIN ISOLATION LEVEL SERIALIZABLE;

BEGIN

=> UPDATE lights SET state = 'on' WHERE state != 'on';

UPDATE 1

The first transaction turned on the red lamp; so within this transaction all lights are now on.

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)

The second transaction does not see uncommitted changes: within this transaction the red light is still off, and it turns off the green light.

=> BEGIN ISOLATION LEVEL SERIALIZABLE;

BEGIN

=> UPDATE lights SET state = 'off' WHERE state != 'off';

UPDATE 1

For the second transaction, both lamps are off:

=> SELECT * FROM lights ORDER BY id;

 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)

Now both transactions commit their changes…

=> COMMIT;

COMMIT

=> 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.

And the second transaction gets an error. Indeed, if these transactions were executed serially, the following two results are possible: If the first transaction is executed first, and then the second, all lamps will be off. If the second transaction is executed first, and then the first, all lamps will be on. The intermediate state cannot be obtained, so one of the transactions fails.

Page 17: Takeaways: SQL Standard

• Non-
• Repeatable
• Phantom
• Other
• Anomalies
• Lost Update
• Dirty Read
• Read
• Read
• Read Uncommitted ― possible possible possible possible
• Read Committed ― ― possible possible possible
• Repeatable Read ― ― ― possible possible
• Serializable ― ― ― ― ―

Speaker notes

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.

Page 18: Takeaways: PostgreSQL

• Non-
• Repeatable
• Phantom
• Other
• Anomalies
• Lost Update
• Dirty Read
• Read
• Read
• Read Uncommitted
• Read Committed * ― possible possible possible
• Repeatable Read ― ― ― ― possible
• Serializable ― ― ― ― ―
• * not always prevented

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/transaction-iso

Page 19: 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.

Page 20: 1. Phantom Read at the Read Committed Level

{#source-figure-020 caption=“Source slide page 20: 1. Phantom Read at the Read Committed Level”}

=> CREATE DATABASE arch_isolation;

CREATE DATABASE

=> \c arch_isolation

You are now connected to database "arch_isolation" as user "student".

Empty table:

=> CREATE TABLE t(id integer);

CREATE TABLE

Let’s start a Read Committed transaction in the second session and run a query.

=> \c arch_isolation

You are now connected to database "arch_isolation" as user "student".

=> BEGIN;

BEGIN

=> SELECT * FROM t;

 id
----
(0 rows)

Insert a new row into the table (changes are committed).

=> INSERT INTO t VALUES (1);

INSERT 0 1

=> SELECT * FROM t;

 id
----
  1
(1 row)

The Read Committed transaction sees the newly added row. The difference from a non-repeatable read anomaly (where a transaction sees committed changes in already existing rows) is that here the transaction sees rows that did not exist before.

=> COMMIT;

COMMIT

2. At What Point in Time are the Data Visible?

Let’s open a Repeatable Read transaction:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

Insert a new row into the table (changes are committed):

=> INSERT INTO t VALUES (2);

INSERT 0 1

Check the table content in the open transaction:

=> SELECT * FROM t;

 id
----
  1
  2
(2 rows)

Page 21: Page 21

Why are both rows visible? This is because Repeatable Read (and Serializable) transactions see the data as of the start of the first command, not when BEGIN command was executed. If we now insert more rows to the table, the result of a repeated query will no longer change.

=> INSERT INTO t VALUES (3);

INSERT 0 1

=> SELECT * FROM t;

 id
----
  1
  2
(2 rows)

=> COMMIT;

COMMIT

3. Transactional DDL

Open a transaction and remove table t.

=> BEGIN;

BEGIN

=> DROP TABLE t;

DROP TABLE

=> SELECT * FROM t;

ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^

The table is deleted. Now roll back the transaction:

=> ROLLBACK;

ROLLBACK

=> SELECT * FROM t;

 id
----
  1
  2
  3
(3 rows)

The deletion is rolled back, and the table is restored.