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):…
Categories
How Database Isolation Levels Control Concurrency
When several requests update a database at once, their transactions can overlap. A transaction is a group of database operations treated as one unit, such as checking an account balance and then transferring money. An isolation level sets the rules for what each transaction can see while other transactions run and what conflicts the database prevents.
Those rules determine whether a transaction reads a change that another transaction later rolls back, sees different values when it repeats a query, or commits a result that no one-at-a-time order could produce. The four standard levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Their names do not guarantee identical behavior across database products, so choose a level based on the correctness rule your code must protect and verify how your database implements it.
The concurrency problems isolation levels address
A transaction can make a decision using data another transaction is changing. Isolation levels govern which resulting inconsistencies the database allows.
A dirty read occurs when one transaction reads another transaction’s change before the writer commits it. If the writer rolls back, the reader has used data that never became part of the database’s committed state.
A nonrepeatable read happens when a transaction reads a row, another transaction changes and commits it, and the first transaction reads the row again and gets a different value. A phantom read happens when a repeated query finds a different set of rows because another transaction inserted or removed rows that match the query’s condition.
A serialization anomaly occurs when transactions commit outcomes that cannot be explained by any order in which the transactions ran one at a time. This broader problem can arise even when each transaction sees a stable view of its data.
What the four standard levels protect
The table describes the SQL standard’s minimum protections. A database product can provide stronger protections at a named level, and some products do not offer every level.
| Isolation level | Dirty reads | Nonrepeatable reads | Phantom reads | What it means in practice |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Allows the weakest isolation. Some products do not actually expose dirty reads. |
| Read Committed | Prevented | Possible | Possible | Each query reads committed data, but a later query in the same transaction can see changes committed in between. |
| Repeatable Read | Prevented | Prevented | Possible under the standard | Protects rows already read from changing during the transaction, but the standard permits a repeated query to return a different set of rows. |
| Serializable | Prevented | Prevented | Prevented | Makes concurrent transactions produce results equivalent to some one-at-a-time order. |
Read Uncommitted gives a transaction the least protection from concurrent changes. It is a poor fit when a decision depends on accurate committed data. Product support also varies: PostgreSQL treats Read Uncommitted as Read Committed, and Oracle does not offer Read Uncommitted.
Read Committed prevents dirty reads, but successive queries may see different committed data. For example, a transaction can read an account balance, pause, then read the account again after another transaction commits an update. The second query returns the new value.
Repeatable Read protects rows a transaction has read from changes during that transaction, but its guarantees depend on the database. The SQL standard permits phantom reads at this level. PostgreSQL’s implementation also prevents phantom reads, yet PostgreSQL documents that other serialization anomalies can still occur under Repeatable Read.
Serializable requires the database to preserve the effect of some valid one-at-a-time transaction order. The database does not have to run transactions one after another. It can block conflicting work, or detect a conflict and reject a transaction. When the database rejects a transaction because of a serialization conflict, the code that started it must handle the error and safely retry the work.
A stable snapshot can still produce a bad outcome
A transaction can keep seeing the same data and still make a decision that conflicts with another transaction’s decision.
Suppose two doctors are on call, and the rule says at least one must remain on call. Two transactions each read that both doctors are on call. One transaction marks the first doctor off call, while the other marks the second doctor off call. If both commit, no doctor remains on call. Neither transaction saw a row it had read change, but their combined result violates the rule.
Write skew occurs when transactions rely on a relationship between multiple rows and change separate rows based on the same initial snapshot. A snapshot, a view of data from one point in time, does not necessarily guarantee serializable results. PostgreSQL documents that its Repeatable Read level can allow serialization anomalies and that Serializable isolation detects dangerous concurrent patterns, sometimes aborting a transaction so the application can retry it.
For an invariant such as “at least one doctor must remain on call,” identify how concurrent transactions could each make a locally valid change that breaks the rule when combined. Then protect the invariant with a suitable database constraint or locking strategy, or use Serializable isolation and handle transaction retries. The right choice depends on the database and how the data is structured.
How databases enforce isolation
Isolation levels describe what transactions are allowed to observe. Database engines use different mechanisms to enforce those rules.
With locking, a transaction holds locks that restrict other transactions’ reads or writes. For example, SQL Server’s lock-based Repeatable Read keeps shared locks on rows a transaction has read until the transaction ends. Serializable can also use key-range locks, which prevent another transaction from inserting rows into a range being queried. These locks can make conflicting requests wait.
With multi-version concurrency control (MVCC), the database keeps older row versions so a reader can see committed data from an earlier point in time while another transaction updates the current row. This can reduce interference between readers and writers. The database must use storage to keep those versions, and MVCC does not automatically prevent every serialization anomaly. Databases can combine versioning and locks: MySQL InnoDB, for example, uses both.
A key detail is how long a snapshot lasts. A statement-level snapshot gives each query a view of data from the start of that query. A transaction-level snapshot gives queries in the same transaction a consistent view from the same point in time. In SQL Server, Read Committed Snapshot Isolation uses statement-level snapshots, while Snapshot isolation uses a transaction-level snapshot. If two queries in one transaction must see the same snapshot, statement-level snapshots alone do not provide that guarantee.
Check the behavior your database actually provides
The same isolation-level name can have different effects across database products, settings, and query types. PostgreSQL 18 defaults to Read Committed, treats Read Uncommitted as Read Committed, and gives Repeatable Read a transaction-level snapshot. MySQL 8.4 InnoDB defaults to Repeatable Read and uses gap or next-key locks for certain range operations. SQL Server Read Committed can use locks or, when Read Committed Snapshot Isolation is enabled, statement-level row versions. Oracle defaults to Read Committed and gives queries statement-level consistency.
These differences matter when code relies on a particular snapshot scope or lock behavior. Before changing an isolation level, check the database’s default, its versioning or snapshot settings, the transaction boundaries in the code, and any locking hints. For product-specific behavior, consult the relevant PostgreSQL documentation, MySQL InnoDB manual, SQL Server locking and row versioning guide, or Oracle documentation.
Choose isolation from the rule your data must preserve
Start with the rule the database must always preserve, called an invariant, such as “an account balance must not go below zero.” Then trace how two transactions could read data and make conflicting changes at the same time. A transaction that updates one row may need different protection from a transaction that bases a decision on several rows or on the absence of matching rows.
Choose the least restrictive mechanism that reliably protects that invariant. A database constraint, a locking strategy, or a transaction design that makes concurrent updates conflict on the same row may address the problem. Serializable isolation is appropriate when the required result must match a one-at-a-time ordering, but the application must be ready for the database to block or reject conflicting work. The database’s implementation determines the cost. SQL Server’s locking approach can make transactions wait for each other, while PostgreSQL’s Serializable implementation monitors dependencies and can require retries.
What I'm buildingDelegate 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