How PostgreSQL Manages Transaction Isolation

In PostgreSQL 8.0, transaction isolation controls what one transaction sees while other transactions change the database. PostgreSQL uses multiversion concurrency control (MVCC): it keeps different versions of rows so a query can read a consistent snapshot, or view of data, without routinely waiting for a writer. Each snapshot stays fixed, so it does not include changes that other transactions commit later.

PostgreSQL 8.0 accepts four SQL isolation-level names but implements two distinct behaviors. The default, READ COMMITTED, takes a fresh snapshot for each command. The 8.0 SERIALIZABLE behavior keeps a stable snapshot for a transaction, though concurrent outcomes are not guaranteed to match running transactions one at a time.

The 8.0 documentation pages cited here are for the PostgreSQL 8.0 branch and identify themselves as 8.0.26. PostgreSQL 8.0.0 was released on January 19, 2005, so those pages should not be treated as a line-by-line verification of the original 8.0.0 manual. The practical distinctions below describe the documented 8.0-era behavior.

PostgreSQL 8.0 maps four isolation names to two behaviors

The SQL standard defines four isolation levels by whether a transaction can see three kinds of changes: data another transaction has not committed, changes to a row it has already read, and changes to which rows match a query. PostgreSQL 8.0 accepts all four level names, but maps them as follows:

The PostgreSQL 8.0 isolation documentation explains that the standard sets minimum protections for each level. A database can prevent more of these effects than the standard requires for a requested level. As a result, PostgreSQL 8.0’s mapping does not contradict the standard’s descriptions.

For practical purposes, choose between the two behaviors rather than assuming each SQL label selects a distinct mechanism.

READ COMMITTED takes a new snapshot for each command

At READ COMMITTED, each command sees data committed before that command began. A later command in the same transaction takes a new snapshot, so it can see commits that were invisible to an earlier command. The transaction also sees its own earlier changes.

This behavior suits many operations on known rows. If two transactions update the same row, one can wait for the other and then apply its update to the newer row version. But a complex command that updates rows based on a search condition can produce an inconsistent view. When another transaction changes a row this command is updating, PostgreSQL can apply the update to that newer row version while the command still sees its original snapshot for related rows.

For example, if a business rule depends on a balance across several rows, separate commands at READ COMMITTED can see different committed states. Use the default when each command can safely work from its own snapshot. When several commands must reason from one shared view, consider the stable-snapshot behavior and plan for its retry requirements.

The 8.0 SERIALIZABLE mode keeps a snapshot, with limits

In PostgreSQL 8.0, SERIALIZABLE establishes a snapshot at the transaction’s first query or data-modification command. Later queries in that transaction continue to see that view, along with the transaction’s own changes.

That stable view does not guarantee that every update will succeed. If this transaction tries to update a row that another transaction changed or deleted after the snapshot was established, PostgreSQL can wait for that other transaction to finish, then abort the update with an error such as could not serialize access due to concurrent update. The application must roll back and retry the whole transaction, starting its reads and writes again. A read-only transaction does not encounter this update-conflict failure, according to the 8.0 manual.

The SERIALIZABLE name also needs a version-specific qualification. PostgreSQL 8.0’s mode did not guarantee that the committed results were possible if the transactions had run one at a time in some order. The manual illustrates the gap with two transactions that each sum values in one group and insert the result into another group. Both transactions can commit even though no order of running them one after the other would produce that outcome. PostgreSQL 8.0 did not use predicate locking, which would coordinate writes that could change which rows another transaction’s query finds.

Use the 8.0 stable-snapshot mode when successive reads need the same view, but do not rely on its name alone to protect a business rule that depends on several rows or on which rows a query finds. The 8.0 consistency guidance describes explicit coordination for cases that need stronger protection.

Set the level before the transaction reads or writes

Set the isolation level at the start of the transaction, before its first query or command that changes data. For example:

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Read data and apply related changes here.

COMMIT;

PostgreSQL 8.0 does not allow a transaction to change its isolation level after it has run its first query or command that changes data. Outside an explicit transaction block, each statement runs in its own transaction, so a SET TRANSACTION command issued on its own does not set the level for a later statement. The 8.0 SET TRANSACTION documentation covers these timing rules.

If the application chooses the stable-snapshot mode, it should restart the entire transaction after a serialization error. Retrying only the failed statement leaves earlier reads based on the old snapshot, so they do not use the fresh view.

Use locks when a check needs explicit coordination

A transaction can use row or table locks to coordinate work that snapshots alone do not protect. SELECT FOR UPDATE locks selected rows against concurrent updates. LOCK TABLE can coordinate access at table scope. PostgreSQL also locks rows automatically when a transaction updates or deletes them, and holds those locks until commit or rollback.

Locks make other transactions wait when their actions conflict. They can also contribute to deadlocks, in which transactions wait on one another’s locks; PostgreSQL detects a deadlock and aborts one transaction. Transactions that acquire locks in a consistent order reduce that risk, but still need to retry if PostgreSQL aborts one because of a deadlock. The 8.0 explicit-locking documentation describes these behaviors and cautions that SELECT FOR UPDATE can cause disk writes.

For a check involving multiple tables, the 8.0 consistency guidance describes taking a SHARE-mode or stronger table lock to prevent concurrent data changes while the transaction checks the rule. In a stable-snapshot transaction, acquire necessary locks before the first query or data change establishes the snapshot. A later lock can block new writes, but it does not refresh the transaction’s existing view.

Later PostgreSQL versions changed the meaning of SERIALIZABLE

PostgreSQL 9.1 added true serializable isolation and kept the earlier snapshot-based behavior under REPEATABLE READ, as stated in the 9.1 release notes. That change matters when applying old documentation or maintaining software written for PostgreSQL 8.0: the 8.0 meaning of SERIALIZABLE does not describe the guarantee provided by that name in later versions.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com