PolarDB:Transaction isolation

Concurrency phenomena

Dirty read

A transaction reads data written by a concurrent uncommitted transaction.

Nonrepeatable read

A transaction re-reads data it has previously read and finds that data has been modified by another transaction that committed since the initial read.

Phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

Serialization anomaly

The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.

Isolation levels

The following table shows which phenomena each isolation level allows or prevents, as defined by the SQL standard and as implemented in PolarDB for PostgreSQL.

PolarDB for PostgreSQL accepts all four standard isolation level requests but internally implements only three distinct levels. The standard permits higher guarantees than the minimum required, which is why the Repeatable Read implementation also prevents phantom reads.

Read Uncommitted behaves identically to Read Committed in PolarDB for PostgreSQL. This is the only sensible mapping of the standard isolation levels to PostgreSQL's Multi-Version Concurrency Control (MVCC) architecture. If your application requests Read Uncommitted, it runs at Read Committed.

To set the isolation level for a transaction, use SET TRANSACTION.

Read Committed isolation level

Read Committed is the default isolation level in PostgreSQL. Under this level:

• SELECT (without FOR UPDATE/FOR SHARE) sees only data committed before the query began — never uncommitted data, and never changes committed by concurrent transactions during query execution.

• SELECT does see the effects of earlier updates in its own transaction, even if not yet committed.

• Two successive SELECT commands in the same transaction may return different data if another transaction commits changes between them.

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE search for target rows committed as of the command start time. If a target row has been updated or deleted by a concurrent transaction that is still in progress, the command waits for that transaction to commit or roll back:

• If the concurrent transaction rolls back, the waiting command proceeds on the original row.

• If the concurrent transaction commits and deleted the row, the waiting command ignores that row.

• If the concurrent transaction commits and updated the row, the waiting command re-evaluates the WHERE clause against the updated version and proceeds if the row still matches.

INSERT with ON CONFLICT DO UPDATE guarantees that each proposed row will either insert or update, unless an unrelated error occurs. INSERT with ON CONFLICT DO NOTHING may skip a row if a conflict arises from a transaction whose effects are not yet visible to the INSERT snapshot.

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

Repeatable Read isolation level

Repeatable Read sees only data committed before the transaction began — never uncommitted data, and never changes committed by concurrent transactions during the transaction. Unlike Read Committed, the snapshot is taken at the start of the first non-transaction-control statement in the transaction, not at the start of each individual statement. Successive SELECT commands in the same transaction always see the same data.

This level prevents all phenomena in the table above except serialization anomalies, which exceeds the SQL standard's minimum requirement for Repeatable Read.

Applications using this level must be prepared to retry transactions due to serialization failures.

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE search for rows committed as of the transaction start time. If a target row has been updated or deleted by a concurrent transaction:

• If the concurrent transaction rolls back, the repeatable read transaction proceeds on the original row.

• If the concurrent transaction commits and actually updated or deleted the row (not just locked it), the repeatable read transaction is rolled back with:

  ERROR:  could not serialize access due to concurrent update

When this error occurs, abort the current transaction and retry from the beginning. On the second attempt, the transaction starts with a snapshot that includes the previously-committed change, so there is no logical conflict.

Only updating transactions need to retry; read-only transactions never encounter serialization conflicts.

Repeatable Read gives each transaction a consistent snapshot of the database, but that snapshot is not necessarily consistent with any serial (one-at-a-time) ordering of concurrent transactions. For example, a read-only transaction may see a control record updated to show a batch as completed, but miss a detail record that is logically part of that batch, because it read an earlier revision of the control record. Use explicit locks to enforce business rules that require strict serial ordering.

Repeatable Read is implemented using Snapshot Isolation, a technique described in academic database literature. Behavior and performance may differ from systems that use traditional locking.

Serializable isolation level

Serializable provides the strictest transaction isolation. It emulates serial transaction execution: as if all committed transactions had run one after another rather than concurrently.

Serializable works exactly like Repeatable Read, with one addition: it monitors for conditions that could cause a concurrent set of serializable transactions to produce a result inconsistent with any possible serial ordering. This monitoring adds no blocking beyond what Repeatable Read already requires, but it does add overhead. When a potential serialization anomaly is detected, one transaction is allowed to commit and the others are rolled back with:

ERROR:  could not serialize access due to read/write dependencies among transactions

Applications using this level must be prepared to retry transactions on serialization failures (SQLSTATE '40001').

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

SELECT SUM(value) FROM mytab WHERE class = 1;

SELECT SUM(value) FROM mytab WHERE class = 2;