A report runs two SELECTs in one Postgres transaction and occasionally sees inconsistent totals. Why?

Postgres defaults to Read Committed, where each statement sees data committed before it started. A transfer that commits between the two statements is visible to the second but not the first. Repeatable Read gives the whole transaction one snapshot.

What is the default isolation level in MySQL InnoDB, and how do plain reads behave in it?

REPEATABLE READ. Plain (non-locking) SELECTs read from a consistent snapshot established by the first read; locking reads (`FOR UPDATE`/`FOR SHARE`) and UPDATE/DELETE read the latest committed data.

Why run long reconciliation reports on a replica instead of the primary?

A long Repeatable Read transaction holds an old snapshot, preventing VACUUM from removing dead rows and competing for resources with user traffic. A replica isolates that load (if its staleness is acceptable).

Isolation Levels in Postgres vs MySQL — Transactions, Locking & Concurrency in Code

Scenario

Example app: a wallet service. A nightly reconciliation report reads all balances, then reads the total of today's transfers, and checks that the numbers add up. Transfers keep running while the report runs.

What you see in production

On Postgres, the reconciliation job raises a false alarm a few times a month: "balances don't match the ledger by 250.00". Rerunning it a minute later shows no problem. A colleague notes that the same code on MySQL never complained.

The naive code

The report runs several statements in one transaction at the database's default isolation level:

BEGIN;  -- Postgres default: READ COMMITTED; MySQL InnoDB default: REPEATABLE READ
SELECT sum(balance) FROM wallets;                          -- statement 1
SELECT sum(amount) FROM transfers WHERE day = current_date; -- statement 2 (a transfer committed in between!)
COMMIT;

with Session(engine) as s, s.begin():
    balances = s.scalar(select(func.sum(Wallet.balance)))
    moved = s.scalar(select(func.sum(Transfer.amount)).where(Transfer.day == date.today()))
    check(balances, moved)

@Transactional(readOnly = true)
public Report reconcile() {
    BigDecimal balances = wallets.sumBalances();
    BigDecimal moved = transfers.sumToday();      // may see transfers committed after statement 1
    return Report.of(balances, moved);
}

Reproduce it

A miniature multi-version store. Under Read Committed each statement sees the latest committed data; under Repeatable Read the whole transaction reads from the snapshot taken at its first statement:

Read Committed vs Repeatable Read during a concurrent transferTry it yourself

versions = []          # (commit_ts, wallets_total, transfers_today)
def commit(ts, wallets, transfers): versions.append((ts, wallets, transfers))
def read(snapshot_ts):
    return max((v for v in versions if v[0] <= snapshot_ts), key=lambda v: v[0])

commit(1, wallets=10_000, transfers=0)

def report(isolation):
    snapshot = 1                                   # first statement runs at ts=1
    balances = read(snapshot)[1]
    # meanwhile a deposit of 250 commits at ts=2: money arrives + a transfer row is written
    commit(2, wallets=10_250, transfers=250)
    stmt2_ts = 2 if isolation == "READ COMMITTED" else snapshot
    moved = read(stmt2_ts)[2]
    versions.pop()                                 # reset for the next run
    expected = 10_000 + moved
    status = "OK" if balances == expected else f"MISMATCH by {expected - balances}"
    print(f"{isolation:16} balances={balances} transfers={moved} -> {status}")

report("READ COMMITTED")
report("REPEATABLE READ")

Output

READ COMMITTED   balances=10000 transfers=250 -> MISMATCH by 250
REPEATABLE READ  balances=10000 transfers=0 -> OK

Root cause

Level	Each statement sees…	Postgres	MySQL InnoDB
Read Committed	data committed before that statement started	default	available
Repeatable Read	a snapshot from the first read of the transaction	snapshot isolation	default (plain reads use a snapshot; locking reads and writes see the latest data)
Serializable	as if transactions ran one at a time	SSI (aborts conflicting transactions)	locking reads (plain `SELECT` becomes `FOR SHARE`)

The report's two statements saw two different points in time under Read Committed. Same SQL, different defaults, different answers.

The fix

Run multi-statement read-only reports at Repeatable Read so they see one consistent snapshot:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT sum(balance) FROM wallets;
SELECT sum(amount) FROM transfers WHERE day = current_date;
COMMIT;

with engine.connect().execution_options(isolation_level="REPEATABLE READ") as conn:
    with conn.begin():
        balances = conn.scalar(select(func.sum(wallets.c.balance)))
        moved = conn.scalar(select(func.sum(transfers.c.amount)).where(transfers.c.day == date.today()))

@Transactional(readOnly = true, isolation = Isolation.REPEATABLE_READ)
public Report reconcile() { ... }

DB::transaction(function () {
    DB::statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');   // first statement inside the transaction
    /* report queries */
});

await prisma.$transaction([sumBalances, sumTransfers], {
  isolationLevel: Prisma.TransactionIsolationLevel.RepeatableRead,
});

Trade-offs

A higher level is not automatically safer for writes: MySQL Repeatable Read still allows lost updates when the application reads with a plain SELECT and writes later (next lesson).
Long-running Repeatable Read transactions keep an old snapshot alive, which holds back Postgres VACUUM — run big reports on a replica.
Moving between databases (or ORMs that set their own defaults) silently changes behaviour; set the level explicitly for code that depends on it.

Detect it in production

Postgres: SHOW default_transaction_isolation; — MySQL: SELECT @@transaction_isolation; — check both in every environment.
Flaky "mismatch" alerts that disappear on rerun are a classic sign of reading two points in time.
Log the isolation level in long-running jobs alongside their results.

Key points

Postgres defaults to Read Committed; MySQL InnoDB to Repeatable Read.
Read Committed: each statement gets a fresh view; Repeatable Read: one snapshot per transaction.
Use Repeatable Read (read-only) for multi-statement reports, and set isolation explicitly when correctness depends on it.