Isolation Levels in PostgreSQL

A transaction is a group of SQL statements handled as one unit. Isolation levels define what a transaction can see while other transactions run. They are part of ACID, which stands for atomicity, consistency, isolation, and durability. The ACID properties provide the background for the isolation levels explained below.

Atomicity

Atomicity means that a transaction commits all of its changes or none of them. If a statement fails, the transaction can be rolled back, and its changes are not committed. If the server stops before a transaction commits, its changes are not treated as committed after recovery.

Consistency

Consistency means that a transaction must follow the rules defined for the data. PostgreSQL can enforce these rules with primary keys, unique constraints, foreign keys, NOT NULL, and CHECK. For example, a table with CHECK (balance >= 0) rejects an update that would save a negative balance.

Isolation

Isolation controls what one transaction can see while another runs. In PostgreSQL, uncommitted changes made by one transaction are not visible to others. The isolation level controls how later committed changes are seen.

Isolation levels are often compared by the read problems they allow or prevent:

• A dirty read is a read of an uncommitted change.
• A nonrepeatable read occurs when the same row is read twice and a committed update changes its value between the reads.
• A phantom read occurs when the same condition is searched twice and a committed change adds or removes a matching row.

PostgreSQL accepts four isolation level names.

Durability

Once a transaction is committed, its changes are permanent and survive system crashes.

Isolation Levels in Action

The SQL below uses two sessions, A and B. Their commands are run in the order shown. The examples use this table:

CREATE TABLE accounts (
    id integer PRIMARY KEY,
    balance integer NOT NULL
);

INSERT INTO accounts (id, balance)
VALUES (1, 100), (2, 150);

READ UNCOMMITTED: dirty reads are prevented

In PostgreSQL, READ UNCOMMITTED works like READ COMMITTED. A transaction sees its own changes and data committed before each statement begins, but it does not see another transaction’s uncommitted changes.

Session A changes account 1’s balance from 100 to 500 but does not commit. Session B reads the account at READ UNCOMMITTED:

Before: account 1 has 100.

-- Session A
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;

-- Session B
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 100
COMMIT;

-- Session A
ROLLBACK;

While Session A is open: Session A sees 500. Session B reads the last committed value, 100.

After ROLLBACK: account 1 has 100 because Session A’s update was cancelled. If Session A had committed instead, the balance would be 500.

For comparison, the same test in MySQL shows what a dirty read looks like. Session A makes the same update, while Session B sets its isolation level before reading:

-- Session B in MySQL
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- 500
COMMIT;

While Session A is open in MySQL: Session A sees 500, and Session B also reads 500. The last committed balance is still 100.

After ROLLBACK in MySQL: account 1 has 100 again. Session B’s earlier read of 500 was dirty because the update was uncommitted when it was read.

The difference in this test is Session B’s read: PostgreSQL returns 100, while MySQL returns the uncommitted 500. PostgreSQL’s default level is READ COMMITTED, which also prevents dirty reads.

READ COMMITTED: later commits can be seen

At READ COMMITTED, each statement gets a new view of committed data. A transaction sees its own changes, and a later statement can see changes that other transactions have committed. This is PostgreSQL’s default level:

Before: account 1 has 100.

-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 100

-- Session B
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- 200
COMMIT;

After: account 1 has 200 because Session B committed its update. Session A read 100 and then 200 for the same account, so a nonrepeatable read occurred.

A phantom read can also occur at this level. Session A searches for accounts with balance > 100. Session B adds a matching account:

Before: account 1 has 200; account 2 has 150.

-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT id FROM accounts WHERE balance > 100 ORDER BY id;
-- 1, 2

-- Session B
BEGIN;
INSERT INTO accounts (id, balance) VALUES (3, 175);
COMMIT;

-- Session A
SELECT id FROM accounts WHERE balance > 100 ORDER BY id;
-- 1, 2, 3
COMMIT;

After: account 3 has 175 because Session B inserted it and committed. The query now returns accounts 1, 2, and 3. Account 3 is the phantom: it was absent from Session A’s first result and present in its second result.

REPEATABLE READ: the snapshot stays stable

At REPEATABLE READ, a transaction has a stable view of the database. It sees its own changes, but it does not see changes committed by other transactions after its snapshot is taken. The snapshot is fixed by the first statement that needs one:

Before: account 1 has 200.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- 200

-- Session B
BEGIN;
UPDATE accounts SET balance = 250 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- 200
COMMIT;

After: account 1 has 250 because Session B committed its update. Session A still read 200 because its snapshot was fixed. A new transaction sees 250. PostgreSQL also prevents phantom reads at REPEATABLE READ. However, two transactions can still make decisions from their own stable snapshots and together break a rule involving several rows.

SERIALIZABLE: transactions act as if run one at a time

Transactions can run at the same time, but the transactions that commit must have a result that could come from running them one at a time. If that is not possible, PostgreSQL rejects one transaction.

A workshop has room for two people, and one place is already booked:

CREATE TABLE workshop_bookings (
    id integer PRIMARY KEY,
    workshop_id integer NOT NULL
);

INSERT INTO workshop_bookings (id, workshop_id) VALUES (1, 10);

Before: workshop 10 has one booking and one place left.

Two people try to book the last place. Each transaction checks the number of bookings and adds one only if the count is below two:

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM workshop_bookings WHERE workshop_id = 10;
-- 1

-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM workshop_bookings WHERE workshop_id = 10;
-- 1

-- Session A
INSERT INTO workshop_bookings (id, workshop_id) VALUES (2, 10);

-- Session B
INSERT INTO workshop_bookings (id, workshop_id) VALUES (3, 10);

-- Session A
COMMIT;

-- Session B
COMMIT;

After: workshop 10 has two bookings, because only one new booking is committed. PostgreSQL rejects the other transaction with a serialization error. If both bookings were committed, three people would have places in a workshop that holds only two. The rejected transaction can be retried, but it must check the booking count again. At REPEATABLE READ, both transactions could commit and the workshop could be overbooked.