# Lesson 01: MVCC / Isolation

> Native Markdown source page: MVCC / Isolation

---

LLMS index: [llms.txt](/en/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** [dba2_01_mvcc_isolation.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_01_mvcc_isolation.html)
> **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip)
> **PDF pages:** 21

## Page 1: MVCC / Isolation {#source-page-001}

### 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 {#source-page-002}

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

## Page 3: Transactions and their Properties {#source-page-003}

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 {#source-page-004}

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 {#source-page-005}

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 {#source-page-006}

For demonstration purposes, we will use a separate database for each lesson.
```sql
=> CREATE DATABASE arch_isolation;
```
```sql
CREATE DATABASE
```
```sql
=> \c arch_isolation
```
```text
You are now connected to database "arch_isolation" as user "student".
```
We examine isolation levels using a table representing a traffic light:
```sql
=> CREATE TABLE lights(
  id integer GENERATED ALWAYS AS IDENTITY,
  lamp text,
  state text
);
```
```sql
CREATE TABLE
```
It is a pedestrian traffic light with two lamps:
```sql
=> INSERT INTO lights(lamp,state) VALUES
    ('red', 'on'), ('green', 'off');
```
```sql
INSERT 0 2
```
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 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:
```text
=> BEGIN;
```
```text
BEGIN
```
```text
=> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
```text
SET
```
To see the current level, check the parameter value:
```text
=> SHOW transaction_isolation;
```
```text
 transaction_isolation
-----------------------
 read committed
(1 row)
```
```text
=> COMMIT;
```
```text
COMMIT
```
Alternatively, we can specify the level directly in the BEGIN command:
```text
=> BEGIN ISOLATION LEVEL READ COMMITTED;
```
```text
BEGIN
```
```text
=> COMMIT;
```
```text
COMMIT
```
The Read Committed level is used by default:
```text
=> SHOW default_transaction_isolation;
```
```text
 default_transaction_isolation
-------------------------------
 read committed
(1 row)
```

## Page 7: Page 7 {#source-page-007}

So if this parameter has not been changed, we do not need to specify the level explicitly.
```text
=> BEGIN;
```
```text
BEGIN
```
```text
=> SHOW transaction_isolation;
```
```text
 transaction_isolation
-----------------------
 read committed
(1 row)
```
```text
=> COMMIT;
```
```text
COMMIT
```
### Read Committed and Dirty Reads
Let’s try to read dirty data. In the first transaction, we turn off the red lamp:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
Start a second session.
```sh
student$ psql arch_isolation
```
Here, open another transaction with the same Read Committed level.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
The second transaction does not see uncommitted changes.
Roll back the change.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
### Read Committed and Committed Changes
We have checked that a transaction does not see uncommitted changes. Let’s see what happens upon commit.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights ORDER BY id;
```

## Page 8: Page 8 {#source-page-008}

```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
Not visible yet.
```text
=> COMMIT;
```
```text
COMMIT
```
Let’s check again.
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
```
```text
=> COMMIT;
```
```text
COMMIT
```
So, at Read Committed level, statements in one transaction see committed changes in other transactions.
> [!NOTE]
> **Note**
> Note that repeating the same query within the same transaction can return different results.
Can we see changes committed during the execution of a statement? Let’s check it.
Currently both lamps are off. Run a long query and, while it’s running, turn on the lights in the second session:
```sql
=> SELECT *, pg_sleep(2) FROM lights ORDER BY id;
```
```sql
=> UPDATE lights SET state = 'on';
```
```sql
UPDATE 2
```
```text
 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.
```sql
=> CREATE FUNCTION get_state(lamp text) RETURNS text
LANGUAGE sql VOLATILE
RETURN (SELECT l.state FROM lights l WHERE l.lamp = get_state.lamp);
```
```sql
CREATE FUNCTION
```
Repeat the experiment, but now the query uses the function:
```sql
=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;
```
```sql
=> UPDATE lights SET state = 'off';
```
```sql
UPDATE 2
```
```text
 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.
```sql
=> ALTER FUNCTION get_state STABLE;
```
```sql
ALTER FUNCTION
```

## Page 9: Page 9 {#source-page-009}

```sql
=> SELECT *, get_state(lamp), pg_sleep(2) FROM lights ORDER BY id;
```
```sql
=> UPDATE lights SET state = 'on';
```
```sql
UPDATE 2
```
```text
 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.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
The second transaction sees that both lights are on...
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights;
```
```text
 id | lamp  | state
----+-------+-------
  2 | green | on
  1 | red   | on
(2 rows)
```
... it changes the state of each light...
```sql
=> UPDATE lights
SET state = CASE WHEN state = 'on' THEN 'off' ELSE 'on' END;
```
... but hangs, waiting for the first to complete.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
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.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
=> SELECT * FROM lights;
```
```text
 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 {#source-page-010}

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.
```text
=> BEGIN;
```
```text
BEGIN
```
The first transaction sets the red light into blinking mode. At the same time, the row is blocked:
```sql
=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
In the second transaction, the client sees that the red light is on...
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT state AS old_state FROM lights WHERE lamp = 'red';
```
```text
 old_state
-----------
 on
(1 row)
```
... and turns it off:
```text
=> -- binding parameter value
\bind 'off'
```
```sql
=> UPDATE lights SET state = $1 WHERE lamp = 'red';
```
The second transaction waits for the first to complete.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
COMMIT
```
The first session expects to see the red lamp blinking. Let’s check it:
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 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 {#source-page-011}

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 {#source-page-012}

Make sure there are no non-repeatable read anomalies.
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)
```
```sql
=> UPDATE lights SET state = 'on' WHERE lamp = 'red' RETURNING *;
```
```text
 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)
```
```sql
UPDATE 1
```
What is the value in the first transaction now?
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | off
(1 row)
```
At Repeatable Read level, rereading the modified row returns its original value.
```text
=> COMMIT;
```
```text
COMMIT
```
### Repeatable Read and Phantom Reads
Phantom rows should also be invisible. Let’s check it:
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
```sql
=> SELECT * FROM lights WHERE state = 'off';
```
```text
 id | lamp  | state
----+-------+-------
  2 | green | off
(1 row)
```
```sql
=> INSERT INTO lights(lamp,state) VALUES ('yellow', 'off')
    RETURNING *;
```
```text
 id |  lamp  | state
----+--------+-------
  3 | yellow | off
(1 row)
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM lights WHERE state = 'off';
```
```text
 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 {#source-page-013}

```text
=> COMMIT;
```
```text
COMMIT
```
Let’s remove the yellow lamp.
```sql
=> DELETE FROM lights WHERE lamp = 'yellow';
```
```sql
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.
```sql
=> SELECT * FROM lights WHERE lamp = 'red';
```
```text
 id | lamp | state
----+------+-------
  1 | red  | on
(1 row)
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'blink' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
The client queries the state of the red lamp...
```sql
=> SELECT state AS current_state FROM lights WHERE lamp = 'red';
```
```text
 current_state
---------------
 on
(1 row)
```
... and then changes it on the server:
```text
=> -- binding parameter value
\bind 'off'
```
```sql
=> UPDATE lights SET state = $1 WHERE lamp = 'red';
```
The second transaction waits for the first to complete.
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
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).
```text
=> ROLLBACK;
```
```text
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?
```sql
UPDATE lights SET state = 'on' WHERE state != 'on';
```
Can we be sure that the following command will turn off all the lamps?
```sql
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 {#source-page-014}

Let’s turn the red lamp on and check.
```sql
=> UPDATE lights SET state = 'on' WHERE lamp = 'red';
```
```sql
UPDATE 1
```
Initial state:
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | off
(2 rows)
```
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'on' WHERE state != 'on';
```
```sql
UPDATE 1
```
The first transaction turned on the green lamp; so within this transaction all lights are now on.
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | on
  2 | green | on
(2 rows)
```
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
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.
```sql
=> UPDATE lights SET state = 'off' WHERE state != 'off';
```
```sql
UPDATE 1
```
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
```
Now both transactions commit their changes...
```text
=> COMMIT;
```
```text
COMMIT
```
```text
=> COMMIT;
```
```text
COMMIT
```
And it turns out that...
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 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 {#source-page-015}

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 {#source-page-016}

Serializable is the highest isolation level, designed to prevent all concurrency anomalies.
```text
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'on' WHERE state != 'on';
```
```sql
UPDATE 1
```
The first transaction turned on the red lamp; so within this transaction all lights are now on.
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 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.
```text
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
```
```text
BEGIN
```
```sql
=> UPDATE lights SET state = 'off' WHERE state != 'off';
```
```sql
UPDATE 1
```
For the second transaction, both lamps are off:
```sql
=> SELECT * FROM lights ORDER BY id;
```
```text
 id | lamp  | state
----+-------+-------
  1 | red   | off
  2 | green | off
(2 rows)
```
Now both transactions commit their changes...
```text
=> COMMIT;
```
```text
COMMIT
```
```text
=> COMMIT;
```
```text
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.
> [!NOTE]
> **Important notice:**
> if Serializable isolation is used, all transactions must use this level. If you mix transactions of different levels, Serializable will behave like Repeatable Read.

## Page 17: Takeaways: SQL Standard {#source-page-017}

  - 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 {#source-page-018}

  - 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 {#source-page-019}

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-page-020}

![Source slide page 20: 1. Phantom Read at the Read Committed Level](images/page-020.png){#source-figure-020 caption="Source slide page 20: 1. Phantom Read at the Read Committed Level"}

```sql
=> CREATE DATABASE arch_isolation;
```
```sql
CREATE DATABASE
```
```sql
=> \c arch_isolation
```
```text
You are now connected to database "arch_isolation" as user "student".
```
Empty table:
```sql
=> CREATE TABLE t(id integer);
```
```sql
CREATE TABLE
```
Let’s start a Read Committed transaction in the second session and run a query.
```sql
=> \c arch_isolation
```
```text
You are now connected to database "arch_isolation" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
(0 rows)
```
Insert a new row into the table (changes are committed).
```sql
=> INSERT INTO t VALUES (1);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 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.
```text
=> COMMIT;
```
```text
COMMIT
```
### 2. At What Point in Time are the Data Visible?
Let’s open a Repeatable Read transaction:
```text
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```text
BEGIN
```
Insert a new row into the table (changes are committed):
```sql
=> INSERT INTO t VALUES (2);
```
```sql
INSERT 0 1
```
Check the table content in the open transaction:
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
  2
(2 rows)
```

## Page 21: Page 21 {#source-page-021}

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.
```sql
=> INSERT INTO t VALUES (3);
```
```sql
INSERT 0 1
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
  2
(2 rows)
```
```text
=> COMMIT;
```
```text
COMMIT
```
### 3. Transactional DDL
Open a transaction and remove table t.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> DROP TABLE t;
```
```sql
DROP TABLE
```
```sql
=> SELECT * FROM t;
```
```sql
ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^
```
The table is deleted.
Now roll back the transaction:
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```sql
=> SELECT * FROM t;
```
```text
 id
----
  1
  2
  3
(3 rows)
```
The deletion is rolled back, and the table is restored.