필사 모드: The Complete Guide to Transaction Isolation Levels: The Part the Application Owns, Not the Database

• Introduction
• 1. What PostgreSQL Actually Implements
• 2. Which Level Should You Choose
• 3. The Serialization-Failure Retry Layer
• 4. The Row-Lock Ladder and SKIP LOCKED
• 5. Advisory Locks — Mutual Exclusion Outside the Data
• 6. The Real Cost of a Long Transaction
• 7. Where This Differs from MySQL 8.4 InnoDB
• 8. Observability — What to Watch at All Times
• Quiz: Check Your Understanding
• Conclusion
• References
• Further reading

Introduction

This blog already has Transaction Isolation Levels and the Anomalies You Actually Hit. That post shows the standard four levels and three anomalies, snapshot isolation, and write skew, all through two-session SQL. Call it the post that covers the theory of isolation levels.

This post is the layer laid on top of that: the operational contract. Teams that understand isolation levels precisely still cause incidents in production. The reason is that an isolation level only covers the database half of the deal. The other half belongs to the application: who retries when a serialization failure hits, how strong a lock to take, how multiple consumers split a queue table, and what happens to the database when a transaction stays open too long. This post covers that list.

The reference engine is PostgreSQL 18. Because the actual behavior of isolation levels varies enormously across engines, points where MySQL 8.4 InnoDB differs are called out by engine name separately in section 7. An explanation that blurs the two engines together is worse than no explanation at all.

1. What PostgreSQL Actually Implements

Let us nail down the facts precisely before anything else. Everything stated here has been confirmed against the PostgreSQL 18 documentation.

PostgreSQL can be asked for all four standard isolation levels, but internally implements only three. In the documentation's own words, "PostgreSQL's Read Uncommitted mode behaves like Read Committed." That is because this is the only sensible way to map the standard isolation levels onto a multiversion concurrency control architecture. In other words, a dirty read never happens in PostgreSQL, under any setting.

The second fact that matters. The documentation's isolation-level table marks the phantom-read cell as "permitted by the standard, but does not occur in PostgreSQL." The body text explains it this way: "PostgreSQL's Repeatable Read implementation does not allow phantom reads. Since the standard defines only which anomalies must not occur at a given isolation level, a stronger guarantee than the standard requires is permitted."

So anyone who memorized the standard's table and concludes "it is Repeatable Read, so phantoms must happen" in PostgreSQL is wrong. The opposite conclusion, "Repeatable Read must be safe," is just as wrong. What Repeatable Read fails to block is not a phantom but a serialization anomaly. The documentation's definition is: "a state where the result of successfully committing several transactions is inconsistent with every possible ordering of running those same transactions one at a time."

The default isolation level is decided by default_transaction_isolation, and the default value is read committed.

2. Which Level Should You Choose

There are three practically usable options, and each carries a different contract.

Read Committed — Each statement sees a snapshot taken at its own start time. Even within the same transaction, different statements can see different data. This is enough for most short OLTP transactions, and its biggest advantage is that it carries no retry burden. In exchange, any "read, decide, then write" logic run at this level requires explicit locking.

Repeatable Read — The entire transaction sees a single snapshot. This suits work that reads several tables to build one consistent report. In exchange, if an update conflict occurs, the transaction aborts with a could not serialize access due to concurrent update error, and the application must retry.

Serializable — In the documentation's words, this is implemented with a technique called "Serializable Snapshot Isolation," layering a serialization-anomaly check on top of snapshot isolation. It can replace an explicit-locking design in domains where an invariant spans multiple rows (a balance total, seat duplication, a total inventory count). The price is a retry rate and reduced predictability.

The documentation's recommendations for Serializable are, in effect, the conditions for using it at all: declare the transaction READ ONLY whenever possible, keep transactions short, do not leave a session sitting idle in transaction for long, and use a connection pool to control the number of concurrent connections. For a read-only reporting transaction, you can use SERIALIZABLE READ ONLY DEFERRABLE; this transaction blocks at the start until it is established that it cannot possibly conflict, so it is never aborted by a serialization failure.

-- A nightly report that needs a fully consistent snapshot, with no retries
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT ...;
COMMIT;

3. The Serialization-Failure Retry Layer

Once you decide to use Repeatable Read or higher, retrying is not optional — it is mandatory. The documentation stresses this twice: "Applications using this level must be prepared to retry transactions due to serialization failures." And: "it is important to have a generalized approach for handling serialization failures, because it is very difficult to predict exactly which transaction contributing to a read/write dependency will need to be rolled back."

The key identifier is SQLSTATE 40001. A serialization failure always comes back with this value. A retry layer needs three properties.

First, the entire transaction must be re-executed. Re-running only the failed statement is meaningless, because the snapshot itself has been invalidated.

Second, the retry count must have a cap, with exponential backoff. Without a cap, a moment of heavy contention triggers a stampede where retries provoke more retries.

Third, side effects must not live outside the transaction. If you called an external API or published a message inside the transaction, it fires again, duplicated, on every retry. Push external calls to after commit, or convert them into a table write inside the transaction using the outbox pattern.

-- How to check for a retryable error on the server side
-- 40001: serialization_failure, 40P01: deadlock_detected
DO $do$
BEGIN
  -- In a real application, this logic must live in the client layer
  PERFORM 1;
EXCEPTION
  WHEN serialization_failure OR deadlock_detected THEN
    RAISE NOTICE 'retryable: %', SQLSTATE;
END;
$do$;

It is practical to handle deadlocks (40P01) in the same retry layer. Both errors share the same property: "doing it again can succeed."

One caution. Do not put the retry layer inside a database function. An exception-handling block inside a function is a subtransaction, so the outer transaction's snapshot stays exactly as it was. The retry must live at the layer that started the transaction — that is, in the application code.

4. The Row-Lock Ladder and SKIP LOCKED

Making "read, decide, then write" logic safe under Read Committed requires explicit locking. PostgreSQL offers four levels of row lock, with the documented syntax FOR lock_strength [ OF from_reference ] [ NOWAIT | SKIP LOCKED ].

Ordered from strongest to weakest:

• FOR UPDATE — The strongest lock. Blocks other transactions' UPDATE, DELETE, and all four kinds of locking SELECT.
• FOR NO KEY UPDATE — A weaker exclusive lock. Does not block FOR KEY SHARE. This is also the lock an UPDATE takes automatically when it does not touch a unique-index column.
• FOR SHARE — A shared lock. Blocks UPDATE, DELETE, FOR UPDATE, and FOR NO KEY UPDATE, but does not block another FOR SHARE or FOR KEY SHARE.
• FOR KEY SHARE — The weakest shared lock. Blocks only DELETE and an UPDATE of the key value. This is the lock a foreign-key check uses internally.

Here is the row-lock conflict relationship as a table. An X marks a conflict.

In practice, what matters most is not lock strength but the wait policy. In the documentation's own words, NOWAIT reports an error, without waiting, if a selected row cannot be locked immediately. SKIP LOCKED skips rows that cannot be locked immediately. The documentation states of SKIP LOCKED that "skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table."

This is the standard pattern for using a database as a work queue.

-- A queue where multiple workers each pick up different jobs
BEGIN;

WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status = 'PENDING'
  ORDER BY created_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
   SET status = 'RUNNING', started_at = now()
  FROM picked p
 WHERE j.id = p.id
RETURNING j.id, j.payload;

COMMIT;

Without SKIP LOCKED, all ten workers queue up on the same first row. With it, each picks up a different row.

The documentation also flags one trap. Under Read Committed, combining ORDER BY with a locking clause can return results out of sort order. The sort is applied first, then the lock wait happens, and by the time the wait clears, the value of the sort column may already have changed. Under Repeatable Read or higher, the same situation instead becomes a 40001 serialization failure.

5. Advisory Locks — Mutual Exclusion Outside the Data

Sometimes what needs locking is not a row at all. Requirements like "this batch job must run only one at a time" or "only one of this external API call per tenant" are the classic case. Building a lock-flag column and marking it with an UPDATE for this creates table bloat and is hard to clean up after a failure.

PostgreSQL's advisory locks were designed for exactly this purpose. The documentation describes them as locks "that have application-defined meanings," where "the system does not enforce their use" so the application must use them correctly, and it lists their advantages: faster than a table flag, no table bloat created, and automatic cleanup when the session ends.

There are two levels. Session-level locks are held until explicitly released or the session ends, and they survive a transaction rollback. Transaction-level locks are released automatically when the transaction ends. For short mutual exclusion, the transaction level is the safe choice.

-- A transaction-level advisory lock: returns false immediately if it cannot be acquired
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('nightly-settlement'));
-- true: proceed. false: another instance is already running, so exit
COMMIT;

There is one trap the documentation warns about. Used together with LIMIT, SQL's order of evaluation can cause you to acquire more locks than intended. You must apply LIMIT inside a subquery first, and call the lock function outside it.

6. The Real Cost of a Long Transaction

This is the topic most often left out of isolation-level discussions: what happens when a transaction is left open for a long time.

PostgreSQL's MVCC leaves the existing row version in place on an update and creates a new version instead. An old version can only be reclaimed once "there is no transaction left that can see this version." But if there is even one open transaction, every row version that died after that transaction's snapshot cannot be reclaimed anywhere in the database.

Here is the result. Because of a single psql session someone opened in the morning and left running while they went to lunch, dead rows pile up in a completely unrelated orders table, indexes bloat, sequential scans have to read more and more pages, and queries slow down across the board. And if transaction ID consumption keeps going, it eventually triggers a forced vacuum to guard against wraparound.

The line of defense is three timeouts. Per the PostgreSQL 18 documentation, all three default to 0, meaning disabled. A server left on default settings has no line of defense here at all.

• statement_timeout — Aborts a statement that runs longer than the specified time.
• lock_timeout — Aborts a statement if the time spent waiting to acquire a lock on a table, index, row, or other object exceeds the specified time.
• idle_in_transaction_session_timeout — Terminates a session that is sitting idle, waiting for a client command, while it has a transaction left open.

Since PostgreSQL 17 there is also transaction_timeout. It terminates the session if the entire transaction runs longer than the specified time, and its default is likewise 0.

-- Database-wide defaults. Relax them separately for the batch account
ALTER DATABASE appdb SET statement_timeout = '30s';
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE batch_worker SET statement_timeout = '30min';

This is how you find currently open, long-running transactions.

SELECT pid, state, now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       wait_event_type, wait_event, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

7. Where This Differs from MySQL 8.4 InnoDB

From here on, read every statement with the engine name attached. The differences between the two engines are not trivial.

The default isolation level differs. Per the MySQL 8.4 documentation, InnoDB's default isolation level is REPEATABLE READ. PostgreSQL 18's default is Read Committed. That means the same application code produces different default behavior depending on which engine it is attached to.

The locking behavior of Repeatable Read differs. In InnoDB, SELECT ... FOR UPDATE, SELECT ... FOR SHARE, UPDATE, and DELETE behave differently depending on the search condition. A search that specifies a unique value against a unique index locks only the index record found, not the gap in front of it. For any other search condition, InnoDB locks the scanned index range with a gap lock or a next-key lock, blocking other sessions from inserting into that range. In other words, InnoDB blocks phantoms by locking gaps on locking reads. PostgreSQL has no concept of a gap lock at all; it blocks phantoms with snapshots instead.

Read Committed also behaves differently. Under InnoDB's Read Committed, gap locking is disabled and survives only for foreign-key constraint checks and duplicate-key checks. In addition, when an UPDATE statement encounters an already-locked row, InnoDB performs a "semi-consistent read," returning the latest committed version to the MySQL layer and using that value to decide whether the WHERE condition matches. PostgreSQL has no equivalent behavior.

Read Uncommitted means something different. Under InnoDB's Read Uncommitted, SELECT runs without locking and may use an earlier version of a row. That means a dirty read actually happens. It does not happen in PostgreSQL.

Serializable is implemented differently. InnoDB's Serializable implicitly converts every plain SELECT into SELECT ... FOR SHARE whenever autocommit is off — it is lock-based. PostgreSQL's Serializable is based not on locks but on a serialization-anomaly check, and aborts with 40001 on conflict.

Even the name of the system variable that sets the isolation level differs. MySQL uses transaction_isolation; PostgreSQL uses default_transaction_isolation.

8. Observability — What to Watch at All Times

Concurrency problems are hard to reproduce, so it is better to have the metrics switched on ahead of time.

Wait events. wait_event_type in pg_stat_activity tells you what a backend is waiting for. The values the documentation defines include Lock (a heavyweight lock on an object visible in SQL), LWLock (a lightweight lock protecting an internal data structure), BufferPin, IO, IPC, Client, Timeout, Activity, and Extension. For a concurrency investigation, Lock is the one that matters.

Deadlock detection. PostgreSQL does not check for a deadlock on every single lock wait, because that would be expensive. Instead it waits deadlock_timeout and only then checks. The default is 1 second. Turning on log_lock_waits logs lock waits against that same threshold, letting you catch long waits that never actually escalated into a deadlock.

-- Log lock waits (measured against deadlock_timeout)
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

Lock slots. max_locks_per_transaction defaults to 64. The documentation says "the default of 64 has historically proven sufficient, but you might need to raise it if you have queries that touch many tables in a single transaction," citing a query against a parent table with many children as an example. This is a limit that workloads actually hit when querying a table with hundreds of partitions.

Metrics specific to Serializable. If you use Serializable, you also need to watch the parameters around predicate locks. max_pred_locks_per_transaction defaults to 64, max_pred_locks_per_page defaults to 2, and max_pred_locks_per_relation defaults to -2. When predicate locks run short, lock granularity is promoted from a page up to the entire relation, and serialization failures spike.

Quiz: Check Your Understanding

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  INSERT INTO orders (...) VALUES (...);
  -- The HTTP call to the payment gateway happens here
  UPDATE inventory SET qty = qty - 1 WHERE sku = 'A-1';
COMMIT;

SELECT id FROM jobs
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

Conclusion

An isolation level is not "a dial that gets safer the higher you turn it." It is a contract between the database and the application. The terms of the contract are these: the database guarantees that a defined set of anomalies will not occur. In exchange, the application retries serialization failures, keeps transactions short, and never lets a side effect cross outside the transaction boundary.

If you do not honor the application-side terms of this contract, incidents keep happening no matter how high you raise the isolation level. Conversely, if you honor them, a few lines of explicit locking on top of Read Committed are enough to make most domains safe.

If you want to reproduce real anomalies with two sessions, you can experiment in the PostgreSQL Playground.

References

• PostgreSQL 18, Transaction Isolation: https://www.postgresql.org/docs/18/transaction-iso.html (retrieved 2026-08-15)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (retrieved 2026-08-15)
• PostgreSQL 18, SELECT (Locking Clause): https://www.postgresql.org/docs/18/sql-select.html (retrieved 2026-08-15)
• PostgreSQL 18, Client Connection Defaults: https://www.postgresql.org/docs/18/runtime-config-client.html (retrieved 2026-08-15)
• PostgreSQL 18, Lock Management: https://www.postgresql.org/docs/18/runtime-config-locks.html (retrieved 2026-08-15)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (retrieved 2026-08-15)
• MySQL 8.4, Transaction Isolation Levels: https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html (retrieved 2026-08-15)

Further reading

• Previous: The Complete Guide to SQL Execution Plans — how the optimizer picks a plan
• Next: The Complete Guide to Zero-Downtime Schema Changes — the lock level each DDL takes
• Transaction Isolation Levels and the Anomalies You Actually Hit — theory and reproducing the anomalies
• Diagnosing and Preventing Deadlocks — how to pin down the two queries from the log
• PostgreSQL Playground — two-session concurrency experiments