Split View: 트랜잭션 격리 수준과 실제 이상 현상 — 표준 정의가 구현과 다른 지점

Transaction Isolation Levels and the Anomalies You Actually Hit — Where the Standard Definition Diverges from the Implementation

• Introduction — Why Does the Data Still Go Wrong After Raising the Isolation Level
• The Four Levels the Standard Defined and the Three Anomalies
• The Read Committed of PostgreSQL Differs from the Standard
• Repeatable Read Is Really Snapshot Isolation
• The Repeatable Read of MySQL InnoDB and Gap Locks
• write skew — The Real Hole in Snapshot Isolation
• Choosing Among SERIALIZABLE, SELECT FOR UPDATE, and Optimistic Locking
• Closing — Raising the Isolation Level Without Retries Is Meaningless

Introduction — Why Does the Data Still Go Wrong After Raising the Isolation Level

There is a prescription that comes up whenever someone meets a concurrency bug: "raise the isolation level to Repeatable Read." And in a good number of cases that prescription does not fix the problem. Balances go negative, stock gets oversold, duplicate reservations keep appearing.

There are two reasons. First, the definitions you learned from the isolation level table differ from the actual implementations. The Read Committed of PostgreSQL cannot produce the dirty read the standard permits in the first place, and its Repeatable Read blocks most of the phantoms the standard permits as well. The Repeatable Read of MySQL InnoDB works in yet another way. Second, there is one anomaly that none of the four levels catches. Most real-world concurrency bugs are exactly that one.

This article first lays out the table from the standard, then uses SQL across two sessions to confirm how that table collapses in an actual database.

The Four Levels the Standard Defined and the Three Anomalies

The ANSI SQL standard defined isolation levels in terms of "what is permitted." The fact that it was defined as a list of prohibitions rather than as an algorithm becomes the seed of later confusion.

• dirty read — reading an uncommitted change made by another transaction
• non-repeatable read — reading the same row twice and getting different values
• phantom read — running the same query twice and getting a different number of rows

Adding one more that is absent from the standard but matters most in practice, the picture looks like this.

The rightmost column of the table is the crux. Every level except Serializable permits write skew. And most services run at Read Committed.

You check the current setting like this.

-- PostgreSQL
SHOW default_transaction_isolation;   -- default: read committed
SELECT current_setting('transaction_isolation');

-- Specify it per transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL
SELECT @@transaction_isolation;   -- default: REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

The first thing to be aware of is that the defaults differ. PostgreSQL is Read Committed, MySQL is Repeatable Read. This is the first place where behavior changes silently when you migrate from MySQL to PostgreSQL.

The Read Committed of PostgreSQL Differs from the Standard

PostgreSQL implements isolation with MVCC rather than with locks. Every read goes through a snapshot, and a snapshot only contains committed versions. That is why even if you ask for Read Uncommitted, it behaves as Read Committed. The syntax is accepted, but a dirty read is structurally impossible.

BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;

 transaction_isolation
-----------------------
 read uncommitted

The setting shows up as requested, but the internal behavior is the same as Read Committed. Not knowing this, some people propose "let us lower it to Read Uncommitted to gain performance" — in PostgreSQL that has no effect at all.

Under Read Committed a snapshot is taken fresh per statement. Even inside one transaction, two SELECTs see different snapshots.

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 10000

-- Session B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- Session A (inside the same transaction)
SELECT balance FROM accounts WHERE id = 1;   -- 5000  <-- the value changed
COMMIT;

So far this is textbook. But Read Committed has one more behavior that is not widely known. When an UPDATE meets a row locked by another transaction it waits, and once the other side commits it re-reads the updated latest version and re-evaluates the WHERE condition.

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
-- waiting, not committed

-- Session B
BEGIN;
UPDATE accounts SET balance = balance - 4000 WHERE id = 1;   -- waits

-- Session A
COMMIT;

Once the lock is released, session B re-reads the latest row where balance has become 7000 and updates it to 3000. The result is 3000, meaning both deductions are reflected. For an update that is based on the current value, like balance = balance - 4000, this re-evaluation is what prevents a lost update.

The problem is the pattern where the application reads a value, computes with it, and then writes the result as a constant.

-- What applications commonly do
SELECT balance FROM accounts WHERE id = 1;   -- reads 10000
-- the application computes 10000 - 4000 = 6000
UPDATE accounts SET balance = 6000 WHERE id = 1;   -- overwrites with a constant

In this case the deduction from session A vanishes without a trace. Re-evaluation applies only to the WHERE condition; the constant in the SET clause is used as written. An ORM reading a whole entity, changing a field, and saving it is exactly this pattern.

Repeatable Read Is Really Snapshot Isolation

The Repeatable Read of PostgreSQL takes a snapshot once at the first statement of the transaction and sees only that one until the end. This is stronger than the Repeatable Read of the standard. Not only do the values of the same row not change, the number of rows matching a condition does not change either. In other words, phantoms are not visible.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE status = 'pending';   -- 42

-- Session B
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Session A
SELECT count(*) FROM orders WHERE status = 'pending';   -- 42  <-- unchanged
COMMIT;

The price is that the transaction dies on a write conflict. If you try to update a row that another transaction committed after your snapshot, PostgreSQL raises an error instead of waiting and re-evaluating.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 10000

-- Session B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- Session A
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;

ERROR:  could not serialize access due to concurrent update
SQLSTATE: 40001

This is the contract you must know before using Repeatable Read. Raising the isolation level puts the responsibility to retry on the application. Raising the isolation level without retry logic is merely a change that shows users a 500 error more often.

There is one more error that shows up alongside it under Repeatable Read.

ERROR:  could not serialize access due to concurrent delete

It occurs when a row you read was deleted by another transaction. The response is the same: retry.

The Repeatable Read of MySQL InnoDB and Gap Locks

The name is the same, but the Repeatable Read of InnoDB behaves differently. The differences sit in two places.

First, a plain SELECT sees the transaction snapshot, but locking reads and update statements see the latest committed version. So within a single transaction, a SELECT and an UPDATE can see different data.

-- Session A (MySQL, REPEATABLE READ)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 10000 (snapshot)

-- Session B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;          -- 10000 (still the snapshot)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 5000 (latest)
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
COMMIT;

In PostgreSQL this is the situation where the third statement would have raised a 40001 error, but InnoDB proceeds quietly. The absence of an error does not mean it is safe. If the application made a decision based on the 10000 from the first SELECT, that decision is already stale.

Second, InnoDB blocks phantoms with gap locks. A locking read or an update statement locks not just the index record but the gap between records as well. This is the next-key lock.

-- Session A
BEGIN;
SELECT * FROM orders WHERE user_id = 42 FOR UPDATE;
-- the user_id = 42 index range and the gaps around it are locked

-- Session B
INSERT INTO orders (user_id, status) VALUES (42, 'pending');   -- waits

Gap locks do block phantoms, but the price is high. Locks are taken even on values that do not exist, and the wider lock scope raises the probability of deadlock. In particular, if the condition column has no index, InnoDB locks every record it scans, so effectively the whole table gets locked. That is why frequent deadlocks in MySQL should send you to check the indexes first.

-- Inspect the current lock state (MySQL 8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

Finding gap locks burdensome, many teams lower MySQL to Read Committed as well. It is genuinely a common choice at large-scale services, and in that case phantoms become the responsibility of the application.

write skew — The Real Hole in Snapshot Isolation

Now we move to the rightmost column of the table. When two transactions read the same set and update different rows, snapshot isolation detects no conflict at all. There is no overlapping write, so strictly speaking there is no conflict. And yet the invariant breaks.

The on-call doctor example is a classic. The rule is "at least one doctor must remain on call."

CREATE TABLE doctors (
  id       int PRIMARY KEY,
  name     text NOT NULL,
  on_call  boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Kim', true), (2, 'Lee', true);

-- Session A: doctor Kim takes herself off call
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2, condition passes

-- Session B: doctor Lee takes himself off call at the same time
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2, condition passes

-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;

-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;

SELECT count(*) FROM doctors WHERE on_call = true;

 count
-------
     0

Both transactions succeeded and there was no error, yet the number of on-call doctors became zero. Each updated a different row, so there is no write conflict, and snapshot isolation has no means of catching it.

Bugs with the same structure repeat in practice. Checking stock then deducting it, checking for duplicate seat reservations, validating an account balance total, checking for duplicate signups without a unique constraint — these are all write skew. Raising the isolation level from Read Committed to Repeatable Read fixes not one of them.

The SERIALIZABLE of PostgreSQL does catch this. It is implemented not with locks but with SSI, that is, Serializable Snapshot Isolation. On top of snapshot isolation it tracks read-write dependencies between transactions, and when it detects a pattern that cannot be explained by any serial order, it aborts one side.

-- If you repeat the scenario above with both sessions at SERIALIZABLE
COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
SQLSTATE: 40001

There are three things to watch out for.

1. The error can arrive at commit time. Even when nothing goes wrong mid-transaction it can fail at COMMIT, so the retry scope has to be the entire transaction.
2. The guarantee only holds if every participating transaction is SERIALIZABLE. Raising only one side means nothing at all.
3. For a read-only transaction, declaring SET TRANSACTION READ ONLY DEFERRABLE reduces both the tracking overhead and the probability of being aborted.

The SERIALIZABLE of MySQL is not SSI. It works by turning plain SELECTs into shared locking reads, which is closer to two-phase locking. It is safe, but the loss of concurrency is far greater.

Choosing Among SERIALIZABLE, SELECT FOR UPDATE, and Optimistic Locking

There are three options, and each has its right place.

First, when the rows to lock are clearly identified, pessimistic locking is the simplest and most predictable.

BEGIN;
SELECT stock FROM products WHERE id = 77 FOR UPDATE;
-- from here no other transaction can lock the same row
UPDATE products SET stock = stock - 1 WHERE id = 77;
COMMIT;

If you also need to block the case where the row does not exist, FOR UPDATE is not enough, because you cannot lock a row that is not there. In that case you use a unique constraint or an advisory lock.

-- Explicit application-level lock (released automatically when the transaction ends)
SELECT pg_advisory_xact_lock(hashtext('reserve-seat:' || 'A12'));

To control the wait time, use the options together.

SELECT * FROM products WHERE id = 77 FOR UPDATE NOWAIT;        -- error immediately
SELECT * FROM products WHERE id = 77 FOR UPDATE SKIP LOCKED;   -- skips locked rows

SKIP LOCKED is especially useful for implementing queue tables. Several workers can pick up different jobs from the same table.

-- Safely pick up one item from a job queue
UPDATE jobs SET status = 'running', picked_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

Second, when conflicts are rare and user interaction sits in the middle of the transaction, optimistic locking is better, because you cannot hold a lock while a screen stays open.

-- Detect conflicts only, using a version column
UPDATE documents
SET title = 'new title',
    version = version + 1
WHERE id = 5 AND version = 12;
-- if zero rows were updated, someone changed it first

Third, when the invariant spans several rows or several tables so that you cannot pin down what to lock, SERIALIZABLE is the answer. The on-call doctor example above is exactly that case. Note, though, that on a high-throughput path the abort rate rises, so it is best to watch the abort rate as a metric while narrowing the scope where you apply it.

Where possible, converting it into a database constraint is the most robust. Move an invariant you were protecting with an isolation level into a constraint and you no longer need retries either.

-- Block duplicate reservations with a constraint rather than an isolation level
CREATE UNIQUE INDEX uq_seat_reservation
  ON reservations (showtime_id, seat_no)
  WHERE cancelled_at IS NULL;

-- Block overlapping time ranges with a constraint
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

Closing — Raising the Isolation Level Without Retries Is Meaningless

You only need to remember three things.

First, every isolation level above Read Committed imposes a retry obligation on the application. Without code that catches SQLSTATE 40001 and 40P01 and re-runs the entire transaction, raising the isolation level is a change that increases incidents.

import time, random
import psycopg

RETRYABLE = {"40001", "40P01"}  # serialization_failure, deadlock_detected

def run_in_tx(conn, fn, max_attempts=5):
    for attempt in range(max_attempts):
        try:
            with conn.transaction():
                return fn(conn)
        except psycopg.errors.Error as e:
            if e.sqlstate not in RETRYABLE or attempt == max_attempts - 1:
                raise
            # Exponential backoff plus jitter. Prevents colliding again at the same instant.
            time.sleep((2 ** attempt) * 0.05 * (0.5 + random.random()))

Only database work belongs inside the retry function. If sending mail or calling an external API is mixed in, the side effects repeat on every retry.

Second, most concurrency bugs are not non-repeatable read or phantom read but write skew. In the isolation level table, that column is empty only at Serializable. If raising to Repeatable Read does not fix it, suspect that the problem you are facing is write skew.

Third, if you have an invariant you are trying to protect with an isolation level, first examine whether it can be expressed as a constraint. Unique indexes and EXCLUDE constraints always hold regardless of isolation level, need no retries, and cannot be accidentally bypassed by a teammate who joins later.