The concept of ACID regarding transactions is familiar to many programmers. It stands for four properties associated with database transactions.

The first of these is Atomicity, which means that a transaction is always executed either completely or not at all.

Consistency means that a transaction cannot violate database integrity rules. A transaction takes the DB from one valid state to another. One of those are foreign key and primary key constraints. This prevents, for example, the existence of a row with a foreign key that references a non-existent primary key.

Durability ensures that the changes made by a committed transaction are permanent. This is usually implemented with a separate transaction log, which guarantees that the data persists even if the database system crashes after the transaction has been committed.

I saved Isolation for last because, based on my experience interviewing software developers, this is an area relatively unfamiliar to many.

Since the best cure for a lack of knowledge is information, this blog post explains what isolation levels are, specifically from the perspective of a PostgreSQL database. I have also created interactive simulators to go through different scenarios with different isolation levels.

Isolation in Brief

Isolation defines how a transaction perceives other concurrent transactions during its execution. It represents a trade-off between database performance and the prevention of concurrency anomalies. A higher isolation level typically means that transactions may block each other’s progress or even lead to transaction failures to ensure data integrity.

To understand these levels, we must first take a brief detour into how PostgreSQL stores data using the MVCC (Multi-Version Concurrency Control) model.

MVCC

In a PostgreSQL database, records—or more simply, rows—are versioned rather than overwritten. When data is modified, the database creates a new version of the row instead of replacing the old one.

This is managed using internal metadata fields, specifically Transaction IDs:

• $xmin$ Stores the ID of the transaction that inserted the row version
• $xmax$ Stores the ID of the transaction that deleted or updated it.

From a query perspective, this mechanism ensures that a row cannot “disappear” in the middle of a read operation; the database simply checks these IDs to determine which version of the row is visible to your specific transaction. Consequently, data being added by a parallel process won’t disrupt an ongoing transaction. A new version remains invisible until the transaction that created it has officially committed.

Understanding this versioning is essential as we move forward to examine the different isolation levels, which we will review from weakest to strongest.

Isolation level 1: Read Uncommitted (Dirty Read)

This one is simple: no sane database — PostgreSQL included — allows a transaction to read uncommitted data. PostgreSQL accepts READ UNCOMMITTED, but it behaves the same as READ COMMITTED.

Isolation level 2: Read Committed

This is the most important level to understand, as it is the default setting in PostgreSQL.

At this level, each query within a transaction sees only the data committed at the moment the query begins. While this prevents “dirty reads,” it allows for two specific anomalies:

Non-repeatable Read: If you read the same row twice within the same transaction, you might get different results if another transaction committed changes to that row in between your two queries.

Phantom Read: This occurs when a query (such as an aggregate or a range scan) returns a different set of rows on a second execution because another transaction committed an INSERT or DELETE in the meantime. A “phantom” row has appeared or disappeared.

Whether these anomalies are a problem depends entirely on your application logic. A typical risk is making a functional decision or a calculation based on data that changes before the transaction finishes.

While there are ways to solve these issues at this isolation level—such as using specific locking clauses—we will return to those at the very end of this post.

To better understand how this works in practice, you can use the following simulator to test how transactions behave when running side-by-side. Keep in mind how Postgresql uses Transaction IDs to check rows visibility in a transaction.

3. Isolation level: Repeatable Read

With Repeatable Read, all reads within a transaction see the database as of a single snapshot (a consistent version of data). This prevents non-repeatable reads and, in PostgreSQL, also prevents phantom reads within the transaction. (The SQL standard allows phantoms under Repeatable Read, but PostgreSQL’s implementation does not.)

However, this isolation level changes PostgreSQL’s behavior in an important way: some transactions may fail because PostgreSQL detects that completing them would violate the guarantees of the isolation level. This can happen, for example, when two concurrent transactions attempt to update the same row. In that case, once one transaction commits, the other may be forced to abort with a serialization-related error. The application must be prepared to decide whether the transaction can be retried.

It is useful to compare this with Read Committed. Under Read Committed, concurrent updates are typically resolved by waiting: one transaction waits for the other to commit or roll back, and then proceeds. After the lock is released, the second transaction can apply its update (assuming the update conditions still match).

PostgreSQL’s MVCC model also introduces a related anomaly under snapshot-based isolation: write skew. In write skew, two transactions read overlapping conditions and then perform updates that do not touch the same rows, but together violate a business rule. The database does not necessarily prevent this under Repeatable Read. This is often easiest to understand by testing practical examples.

4. Isolation level: Serializable

The strongest isolation level is Serializable. It guarantees that concurrent transactions behave as if they were executed one-by-one in some order. In other words, the result must be equivalent to some serial execution; concurrent interleavings must not produce a different outcome.

In PostgreSQL, this is implemented using Serializable Snapshot Isolation (SSI). PostgreSQL tracks read/write dependencies (including predicate-level reads) to detect when concurrency could lead to a non-serializable outcome. When such a conflict is detected, PostgreSQL aborts one of the transactions to preserve serializability.

Serializable provides the strongest safety guarantees, but it is also the most expensive isolation level in terms of overhead and the likelihood of transaction retries. Just like Repeatable Read (and even more so), it requires the application to handle transaction aborts and implement a retry strategy when a serialization failure occurs.

Interlude

Hopefully, the sections above explain why developers need to understand the different transaction isolation levels—and when it makes sense to use each of them.

At this point, it’s worth returning once more to the earlier discussion about Read Committed. There are practical techniques that let us keep using Read Committed while still avoiding many of its common pitfalls.

Read Committed and locks

You might be getting a little tired of new concepts by now, but PostgreSQL gives us several tools that can help us avoid moving to higher isolation levels—and, importantly, avoid the transaction aborts that stronger levels (Repeatable Read / Serializable) may require you to handle with retry logic.

If we want to ensure that data we read cannot change during our transaction, we can add a locking clause to the query, such as FOR SHARE.

This prevents the selected rows from being modified while the lock is held. Any concurrent transaction that tries to update or delete those rows must wait until the current transaction completes.

A similar approach is FOR UPDATE, which takes a stronger lock. In addition to blocking updates and deletes, it also blocks other transactions from acquiring certain weaker locks on the same rows (including FOR SHARE-type locks). In other words: FOR UPDATE is more restrictive, and should be used only when you truly intend to update the locked rows (or when you intentionally want to serialize access).

Locks can also be used to prevent phantom reads, but only under specific access patterns. If two transactions protect a shared “guard row” (or some other shared resource) using SELECT … FOR UPDATE, then inserts/updates that would otherwise create phantoms can be forced to wait—effectively serializing the critical section.

It’s also possible to use advisory locks to coordinate application-level critical sections. Advisory locks are powerful, but since they require careful design and consistent usage across the codebase, I’ll leave them as optional further reading.

To better understand how locks behave in practice, you can follow their effects in the interactive simulator below.

Thanks and further reading

The inspiration for building an interactive simulator came from the TheOtherBrian1 excellent site “Postgres Locks Explained”.

PostgreSQL documentation on isolation levels and locking:

• https://www.postgresql.org/docs/current/transaction-iso.html
• https://www.postgresql.org/docs/current/explicit-locking.html

AI tools (Gemini, ChatGPT) were used to improve grammar and clarity of the text. The interactive simulators were built with the help of Claude Code.