Transactions and Isolation Levels in PostgreSQL

1. Overview

When multiple requests read and write to the same table concurrently, the result can be incorrect even if each SQL statement is correct. The isolation level determines how many changes from other transactions a transaction can “see”.

In this article, we’ll see how PostgreSQL implements isolation levels. You’ll be able to reproduce lost update and write skew errors. By the end, you’ll know how to choose the right level, configure it in Spring Boot, and write proper retry logic.

2. Transactions in PostgreSQL

A transaction groups multiple statements into a unit. Either all succeed, or none take effect:

CREATE TABLE account (
    id      BIGINT PRIMARY KEY,
    owner   TEXT NOT NULL,
    balance NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO account VALUES (1, 'An', 1000), (2, 'Binh', 500);

BEGIN;
UPDATE account SET balance = balance - 300 WHERE id = 1;
UPDATE account SET balance = balance + 300 WHERE id = 2;
COMMIT;

If the second UPDATE fails, you call ROLLBACK and An’s balance remains unchanged. When there’s no BEGIN, PostgreSQL runs in autocommit mode: each statement is a separate transaction.

PostgreSQL uses MVCC (Multi-Version Concurrency Control). Each UPDATE statement creates a new version of the row instead of overwriting it. Each transaction reads data through a snapshot. Thus, read operations don’t block write operations, and vice versa. The isolation level determines when the snapshot is taken: at the start of each statement, or once for the entire transaction.

3. Anomalies and Four Isolation Levels

The SQL standard defines the following phenomena:

• Dirty read: reading data that another transaction hasn’t committed.
• Non-repeatable read: reading the same row twice but getting different values.
• Phantom read: re-running the same conditional query with rows appearing or disappearing in the result.
• Lost update: two transactions both read and write to the same row, and one write gets overwritten.
• Write skew: two transactions read common data, then each writes to different rows, breaking a business constraint.

The table below describes how PostgreSQL handles each phenomenon:

Level              Dirty read  Non-repeatable  Phantom  Lost update  Write skew
READ UNCOMMITTED   No          Yes             Yes      Yes          Yes
READ COMMITTED     No          Yes             Yes      Yes          Yes
REPEATABLE READ    No          No              No       No (*)       Yes
SERIALIZABLE       No          No              No       No (*)       No (*)
(*) Transaction is aborted with SQLSTATE 40001; the application must retry.

There are two differences from the SQL standard you should remember:

• READ UNCOMMITTED in PostgreSQL works exactly like READ COMMITTED. You’ll never encounter dirty read.
• REPEATABLE READ blocks phantom read too, because it’s essentially snapshot isolation.

The default level is READ COMMITTED. You can check it with:

SHOW default_transaction_isolation;

 default_transaction_isolation
-------------------------------
 read committed

4. READ COMMITTED

At this level, each statement takes a new snapshot. Open two psql sessions and run the following interleaved steps:

-- Session A
BEGIN;
SELECT balance FROM account WHERE id = 1;   -- 700.00

-- Session B
UPDATE account SET balance = 900 WHERE id = 1;  -- autocommit

-- Session A
SELECT balance FROM account WHERE id = 1;   -- 900.00
COMMIT;

Within the same transaction, session A sees two different values. This is a non-repeatable read.

4.1. Lost Update

The most common error in applications is the pattern of “read in Java, calculate, then write back”:

BigDecimal balance = jdbc.queryForObject(
        "SELECT balance FROM account WHERE id = ?", BigDecimal.class, 1L);
jdbc.update("UPDATE account SET balance = ? WHERE id = ?",
        balance.subtract(new BigDecimal("100")), 1L);

Suppose two threads run this code and both read 1000. Both will write 900. The result is that one deduction is lost.

There are two ways to fix this while keeping READ COMMITTED.

Method 1: let the database calculate; write it as an atomic statement.

UPDATE account SET balance = balance - 100 WHERE id = 1;

When encountering a row being updated by another transaction, PostgreSQL waits for that transaction to commit. Then it reads the latest version of the row and applies the subtraction.

Method 2: lock the row immediately upon read using SELECT ... FOR UPDATE.

BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- application checks business rules, calculates...
UPDATE account SET balance = 900 WHERE id = 1;
COMMIT;

The second transaction will be blocked at SELECT ... FOR UPDATE until the first transaction commits. Thus it always reads the new value.

5. REPEATABLE READ

At this level, the snapshot is taken once at the first statement of the transaction. All subsequent reads see the same data state.

If you try to update a row that another transaction has modified and committed after the snapshot was taken, PostgreSQL will reject it:

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

-- Session B
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- commit

-- Session A
UPDATE account SET balance = balance - 200 WHERE id = 1;

ERROR:  could not serialize access due to concurrent update

Lost update no longer happens silently. Instead, session A must ROLLBACK and start over. Error code 40001 (serialization_failure) signals the application to retry.

6. SERIALIZABLE and Write Skew

REPEATABLE READ still allows write skew. Consider the following classic example: a hospital requires at least one on-call doctor at all times.

CREATE TABLE doctor (
    id      BIGINT PRIMARY KEY,
    name    TEXT NOT NULL,
    on_call BOOLEAN NOT NULL
);
INSERT INTO doctor VALUES (1, 'Lan', true), (2, 'Minh', true);

Both doctors request off-call duty, each in a separate transaction:

-- Both A and B run:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor WHERE on_call;  -- both see 2

-- A
UPDATE doctor SET on_call = false WHERE id = 1;
-- B
UPDATE doctor SET on_call = false WHERE id = 2;

COMMIT;  -- A: OK
COMMIT;  -- B: OK  -> no one on call!

The two transactions write to different rows, so no write conflict is detected. If we switch to SERIALIZABLE, PostgreSQL uses Serializable Snapshot Isolation (SSI). SSI tracks read-write dependencies between transactions and aborts one of them:

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.

The error can appear at UPDATE or at COMMIT. The error code is still 40001.

Note: SERIALIZABLE doesn’t acquire additional row locks. It only detects conflicts and aborts transactions. With SSI, PostgreSQL may abort even transactions that don’t truly conflict. Thus, under high concurrent write load, the retry rate can increase significantly.

7. Using Isolation Levels in Spring Boot

Add the following dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

The simplest way is to declare the isolation level on @Transactional:

@Service
public class ReportService {

    private final JdbcTemplate jdbc;

    public ReportService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public BigDecimal totalBalance() {
        BigDecimal total = jdbc.queryForObject(
                "SELECT sum(balance) FROM account", BigDecimal.class);
        Long count = jdbc.queryForObject(
                "SELECT count(*) FROM account", Long.class);
        // both queries use the same snapshot -> consistent data
        return total;
    }
}

Spring calls Connection.setTransactionIsolation(...) before starting the transaction. The PostgreSQL driver translates it to SET TRANSACTION ISOLATION LEVEL ....

7.1. Retrying on Serialization Failure

Retry must be outside the transaction. Retry inside is pointless, since the transaction is already marked as failed. Using TransactionTemplate helps avoid the self-invocation pitfall of @Transactional:

@Service
public class OnCallService {

    private static final int MAX_ATTEMPTS = 3;

    private final JdbcTemplate jdbc;
    private final TransactionTemplate tx;

    public OnCallService(JdbcTemplate jdbc, PlatformTransactionManager tm) {
        this.jdbc = jdbc;
        this.tx = new TransactionTemplate(tm);
        this.tx.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE);
    }

    public void goOffCall(long doctorId) {
        for (int attempt = 1; ; attempt++) {
            try {
                tx.executeWithoutResult(status -> doGoOffCall(doctorId));
                return;
            } catch (ConcurrencyFailureException e) {
                if (attempt >= MAX_ATTEMPTS) {
                    throw e;
                }
            }
        }
    }

    private void doGoOffCall(long doctorId) {
        Integer onCall = jdbc.queryForObject(
                "SELECT count(*) FROM doctor WHERE on_call", Integer.class);
        if (onCall == null || onCall < 2) {
            throw new IllegalStateException("At least one doctor must be on call");
        }
        jdbc.update("UPDATE doctor SET on_call = false WHERE id = ?", doctorId);
    }
}

The code above works as follows:

• setIsolationLevel(ISOLATION_SERIALIZABLE) applies to every call to executeWithoutResult.
• Spring translates SQLSTATE 40001 (and deadlock 40P01) to subclasses of ConcurrencyFailureException. This way you don’t have to read the driver’s error codes.
• On retry, the new transaction takes a new snapshot. It sees the other doctor has gone off call, so it throws IllegalStateException. The business constraint is preserved.

Note: Spring Boot 3 auto-configures JdbcTransactionManager. This class translates errors occurring at commit into DataAccessException. If you manually declare a DataSourceTransactionManager bean, commit errors become TransactionSystemException and the retry loop above won’t catch them.

8. Testing Write Skew with Testcontainers

To precisely reproduce the interleaved order, we use two JDBC Connection objects directly in the same thread. This approach doesn’t depend on thread scheduling. Add test dependencies:

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>

Test class:

@Testcontainers
class WriteSkewTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @BeforeEach
    void setUp() throws SQLException {
        try (Connection c = connect(); Statement st = c.createStatement()) {
            st.execute("DROP TABLE IF EXISTS doctor");
            st.execute("CREATE TABLE doctor (id BIGINT PRIMARY KEY, name TEXT, on_call BOOLEAN)");
            st.execute("INSERT INTO doctor VALUES (1, 'Lan', true), (2, 'Minh', true)");
        }
    }

    @Test
    void repeatableReadAllowsWriteSkew() throws SQLException {
        runConcurrently(Connection.TRANSACTION_REPEATABLE_READ);
        assertThat(countOnCall()).isZero();
    }

    @Test
    void serializableRejectsWriteSkew() throws SQLException {
        assertThatThrownBy(() -> runConcurrently(Connection.TRANSACTION_SERIALIZABLE))
                .isInstanceOf(SQLException.class)
                .extracting(e -> ((SQLException) e).getSQLState())
                .isEqualTo("40001");
        assertThat(countOnCall()).isEqualTo(1);
    }

    private void runConcurrently(int level) throws SQLException {
        try (Connection a = begin(level); Connection b = begin(level)) {
            assertThat(countOnCall(a)).isEqualTo(2);
            assertThat(countOnCall(b)).isEqualTo(2);
            offCall(a, 1);
            a.commit();
            offCall(b, 2);
            b.commit();
        }
    }

    private Connection begin(int level) throws SQLException {
        Connection c = connect();
        c.setAutoCommit(false);
        c.setTransactionIsolation(level);
        return c;
    }

    private Connection connect() throws SQLException {
        return DriverManager.getConnection(
                postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
    }

    private void offCall(Connection c, long id) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "UPDATE doctor SET on_call = false WHERE id = ?")) {
            ps.setLong(1, id);
            ps.executeUpdate();
        }
    }

    private int countOnCall(Connection c) throws SQLException {
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery("SELECT count(*) FROM doctor WHERE on_call")) {
            rs.next();
            return rs.getInt(1);
        }
    }

    private int countOnCall() throws SQLException {
        try (Connection c = connect()) {
            return countOnCall(c);
        }
    }
}

Expected results of the two tests:

repeatableReadAllowsWriteSkew  -> PASSED (both commit, 0 doctors on call)
serializableRejectsWriteSkew   -> PASSED (B aborted with 40001, 1 doctor on call remains)

The second test doesn’t assert whether the error occurs at B’s UPDATE or COMMIT. It only checks for code 40001, since the timing of when SSI detects the conflict may vary across PostgreSQL versions.

9. Common Pitfalls and When Not to Use

• Raise the level but don’t retry. At REPEATABLE READ and SERIALIZABLE, error 40001 is normal behavior, not a bug. Without retry, users will randomly get 500 errors.
• Retry inside @Transactional. Failed transactions must be rolled back and restarted from scratch. Place retry at the outer call layer.
• Call external APIs within a retriable transaction. Emails or payment commands may be sent multiple times. Execute side effects only after a successful commit.
• Set SERIALIZABLE system-wide “just to be safe”. This incurs SSI tracking overhead and many retries. Use it only for code paths with constraints spanning multiple rows.
• Mix levels in the same operation. SSI only protects when all involved transactions run SERIALIZABLE. A READ COMMITTED transaction writing to the same table can still break the constraint.
• Long-running REPEATABLE READ transactions. The old snapshot prevents VACUUM from cleaning dead row versions, causing table bloat. Keep transactions short.

Note: For constraints expressible via schema, such as CHECK, UNIQUE, or exclusion constraints, prefer using the constraint. It’s cheaper and more reliable than raising the isolation level.

10. Conclusion

PostgreSQL defaults to READ COMMITTED. This level suffices for most applications, provided you write atomic updates or use SELECT ... FOR UPDATE to avoid lost updates.

REPEATABLE READ provides a consistent snapshot throughout the transaction, suitable for reporting. SERIALIZABLE prevents write skew as well. Both levels require the application to retry on SQLSTATE 40001.

In Spring Boot, declare the level via @Transactional(isolation = ...) or TransactionTemplate, catch ConcurrencyFailureException outside the transaction, and verify behavior with Testcontainers using deterministic operation ordering.