Transactions & Isolation Levels

Atomicity: all statements commit or none do. Consistency: a transaction moves the database from one valid state to another (constraints, triggers; partly the application's duty). Isolation: concurrent transactions do not see each other's intermediate state, to a degree set by the isolation level. Durability: committed data survives a crash.

• Atomicity and durability come from the write-ahead log (WAL in PostgreSQL, redo/undo logs in InnoDB): changes are logged and fsynced at commit, undone on rollback or crash recovery.
• Isolation comes from locks and/or MVCC.
• Consistency is enforced by constraints (PK, FK, CHECK, UNIQUE); business invariants spanning rows need the right isolation or explicit locks.
• Durability depends on settings: synchronous_commit=off (PostgreSQL) or innodb_flush_log_at_trx_commit=2 trade durability for speed.

• Dirty read: reading another transaction's uncommitted change.
• Non-repeatable read: reading the same row twice and getting different values because another transaction committed an update in between.
• Phantom read: re-running a range query and seeing new or missing rows because of another transaction's committed insert/delete.
• Lost update: two transactions read the same value, both compute a new one, the second write overwrites the first.
• Write skew: two transactions read overlapping data, each updates a different row based on what it read, and together they violate an invariant (classic: two doctors both go off-call because each saw the other still on-call).

-- Write skew under REPEATABLE READ / snapshot isolation
-- T1: SELECT count(*) FROM oncall WHERE on_duty;   -- 2
-- T2: SELECT count(*) FROM oncall WHERE on_duty;   -- 2
-- T1: UPDATE oncall SET on_duty=false WHERE doctor='A'; COMMIT;
-- T2: UPDATE oncall SET on_duty=false WHERE doctor='B'; COMMIT;  -- nobody on call

Standard table: READ UNCOMMITTED allows dirty, non-repeatable and phantom reads; READ COMMITTED allows non-repeatable and phantom; REPEATABLE READ allows phantoms; SERIALIZABLE allows none. Real engines differ.

• PostgreSQL: default READ COMMITTED (each statement sees a fresh snapshot). READ UNCOMMITTED behaves as READ COMMITTED (no dirty reads ever). REPEATABLE READ is snapshot isolation: one snapshot per transaction, no phantoms in practice, but write skew is possible and conflicting updates fail with could not serialize access due to concurrent update. SERIALIZABLE uses SSI and may abort with SQLSTATE 40001.
• MySQL InnoDB: default REPEATABLE READ with a consistent snapshot for plain SELECTs; locking reads (FOR UPDATE, UPDATE) read the latest committed data and use next-key (gap) locks to block phantoms. SERIALIZABLE turns plain SELECTs into LOCK IN SHARE MODE.
• Oracle has no REPEATABLE READ; SQL Server needs READ_COMMITTED_SNAPSHOT to get MVCC reads.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- PostgreSQL, per transaction
SHOW transaction_isolation;

Multi-version concurrency control keeps several versions of a row so readers never block writers and writers never block readers. Each transaction reads the version visible to its snapshot.

• PostgreSQL: an UPDATE writes a new tuple version in the heap, marking the old one with xmax; visibility is decided by xmin/xmax against the snapshot. Old versions (dead tuples) accumulate until VACUUM removes them, causing table and index bloat and transaction id wraparound risk (freeze).
• InnoDB: the row is updated in place in the clustered index; previous versions are reconstructed from undo logs via roll pointers. A long-running transaction keeps the history list long, slowing reads and growing the undo tablespace.
• Common production problem: a forgotten idle in transaction session or a long analytic query pins the oldest snapshot, so VACUUM or purge cannot clean up.

SELECT pid, state, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start LIMIT 5;

Read-modify-write in application code races: both read qty=10, both write 9, one decrement is lost.

-- Buggy: SELECT qty ... ; (app computes qty-1); UPDATE stock SET qty = 9 ...

-- Fix 1: atomic update with guard (works at READ COMMITTED)
UPDATE stock SET qty = qty - 1 WHERE sku = 'A1' AND qty > 0;   -- check rows affected = 1

-- Fix 2: pessimistic lock
BEGIN;
SELECT qty FROM stock WHERE sku = 'A1' FOR UPDATE;
UPDATE stock SET qty = qty - 1 WHERE sku = 'A1';
COMMIT;

-- Fix 3: optimistic version check
UPDATE stock SET qty = 9, version = version + 1 WHERE sku = 'A1' AND version = 7;

Fix 1 is cheapest: the row lock is held only for the statement and the database serializes the arithmetic. Fix 2 suits complex logic between read and write. Fix 3 suits low contention and user-think-time flows.

FOR UPDATE takes an exclusive row lock on the selected rows until the transaction ends; other writers and other FOR UPDATE readers wait. FOR SHARE takes a shared lock (blocks writers, allows other shared readers). NOWAIT fails immediately instead of waiting; SKIP LOCKED silently skips rows locked by others.

SKIP LOCKED turns a table into a work queue where many workers each claim different rows without blocking each other (PostgreSQL 9.5+, MySQL 8.0+):

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'PENDING'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process, then
UPDATE jobs SET status = 'DONE' WHERE id IN (...);
COMMIT;

• Results are an inconsistent view by design; do not use it for correctness-sensitive reads.
• Index the filter so locking does not scan and lock many rows; in MySQL a non-indexed FOR UPDATE locks every scanned row.
• Keep the transaction short; a crashed worker releases its locks, so jobs return to the queue automatically.

A deadlock is a cycle of lock waits: T1 holds row A and wants B, T2 holds B and wants A. The database detects the cycle and aborts one transaction (PostgreSQL SQLSTATE 40P01 after deadlock_timeout of 1 s; InnoDB error 1213, rolls back the cheaper victim).

-- Session 1                      -- Session 2
UPDATE acct SET bal=bal-10        UPDATE acct SET bal=bal-10
 WHERE id=1;                       WHERE id=2;
UPDATE acct SET bal=bal+10        UPDATE acct SET bal=bal+10
 WHERE id=2;  -- waits             WHERE id=1;  -- DEADLOCK

Prevention:

• Acquire locks in a consistent global order (sort ids before updating a batch).
• Keep transactions short and avoid user interaction or remote calls inside them.
• Index predicates used by updates/deletes so they lock fewer rows (InnoDB gap/next-key locks widen on missing indexes).
• Lock the whole set up front with a single SELECT ... ORDER BY id FOR UPDATE.
• Retry the aborted transaction a bounded number of times with jitter.

Debugging: PostgreSQL logs both queries (log_lock_waits = on); MySQL SHOW ENGINE INNODB STATUS has a LATEST DETECTED DEADLOCK section.

Pessimistic locks the row up front (FOR UPDATE) and blocks competitors; optimistic assumes conflicts are rare, lets everyone proceed and detects a conflict at write time with a version column. Choose optimistic for web flows with user think time and low contention; pessimistic for hot rows with high conflict rates (counters, inventory), where optimistic retries would thrash.

@Entity
class Account {
    @Id Long id;
    @Version long version;   // Hibernate adds "where id=? and version=?"
    BigDecimal balance;
}

The SQL becomes UPDATE account SET balance=?, version=version+1 WHERE id=? AND version=?; zero rows updated raises OptimisticLockException (Spring: ObjectOptimisticLockingFailureException), and the caller reloads and retries or reports a conflict (HTTP 409 / ETag with If-Match).

• Optimistic locking works across HTTP requests, because no DB lock is held between them.
• LockModeType.PESSIMISTIC_WRITE maps to FOR UPDATE; OPTIMISTIC_FORCE_INCREMENT bumps the version even without a change.
• Bulk JPQL updates bypass @Version unless you increment it manually.

An operation is idempotent if repeating it has the same effect as doing it once. Networks time out and clients retry, so a "create payment" call must not charge twice. Use a client-supplied idempotency key enforced by a unique constraint.

CREATE TABLE payment (
  id bigserial PRIMARY KEY,
  idempotency_key text NOT NULL UNIQUE,
  amount numeric(12,2) NOT NULL,
  status text NOT NULL,
  response jsonb
);

INSERT INTO payment (idempotency_key, amount, status)
VALUES ('k-123', 50.00, 'PENDING')
ON CONFLICT (idempotency_key) DO NOTHING;   -- 0 rows => replay: return stored response

• Insert the key and perform the side effect in the same transaction where possible; for external calls (PSP) pass the key downstream too.
• Store the response so a retry returns the same result; reject same key with a different payload (422).
• Expire keys after a window (e.g. 24 h).
• MySQL equivalent: INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE.
• PUT and DELETE are idempotent by definition; POST is not and needs the key.

A local transaction covers one resource. Spanning several needs either two-phase commit (XA/JTA) or a pattern that avoids it.

• 2PC/XA: a coordinator asks all resources to prepare, then commit. Atomic but blocking: a coordinator crash leaves in-doubt transactions holding locks; poor throughput; not supported by most brokers and cloud services. Rarely used in modern microservices.
• Transactional outbox: write the business change and an event row in the same local transaction; a relay (poller or CDC such as Debezium) publishes the event, giving at-least-once delivery. Consumers must be idempotent.
• Saga: a sequence of local transactions with compensating actions (refund, cancel) on failure; orchestrated or choreographed. Eventual consistency; compensation can fail and intermediate states are visible.
• Dual write (save() then kafkaTemplate.send()) is the bug to avoid: a crash between the two loses or duplicates the event.

BEGIN;
INSERT INTO orders (id, status) VALUES (1, 'CREATED');
INSERT INTO outbox (aggregate_id, type, payload) VALUES (1, 'OrderCreated', '{"id":1}');
COMMIT;

Asynchronous replication applies WAL/binlog on the replica after commit, so a read routed to a replica can run before the write arrives (replication lag, from milliseconds to minutes under load). The user violates read-your-writes consistency.

Fixes:

• Route reads that must be fresh (immediately after a write by the same user, or in the same session) to the primary, e.g. for N seconds after a write via a session flag or cookie.
• Track the write's position (PostgreSQL LSN, MySQL GTID) and read from a replica only once it has replayed it (pg_last_wal_replay_lsn(), WAIT_FOR_EXECUTED_GTID_SET).
• Use synchronous replication (synchronous_commit = remote_apply) for critical data at a latency cost.
• Return the updated entity from the write response so the UI need not re-read.

-- PostgreSQL: measure lag on a replica
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

In Spring, a routing DataSource keyed by @Transactional(readOnly = true) sends only read-only transactions to replicas, and anything that writes must stay on the primary.

A connection opened a transaction and stopped doing work, typically because application code made a slow remote call or waited for user input inside @Transactional, or a code path forgot to commit/rollback. Its locks stay held, its snapshot pins old row versions (bloat, VACUUM blocked), and it consumes a pooled connection until the pool starves.

SELECT pid, now() - state_change AS idle_for, query
FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY idle_for DESC;

ALTER ROLE app SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app SET statement_timeout = '30s';
ALTER ROLE app SET lock_timeout = '5s';

Prevention:

• Keep transactions short; do HTTP calls and file I/O outside them (read, call, then write in a short transaction).
• Set DB-side timeouts and a pool leakDetectionThreshold (HikariCP).
• Avoid Open-Session-In-View holding connections during view rendering.
• MySQL: innodb_lock_wait_timeout, wait_timeout, and monitor information_schema.innodb_trx.

Use SERIALIZABLE when the invariant spans several rows that different transactions read and write (write skew, e.g. meeting-room double booking, "at least one admin remains") and explicit locks are impractical. The engine guarantees outcomes equal to some serial order.

• PostgreSQL (9.1+): Serializable Snapshot Isolation. Transactions run on snapshots without blocking, the engine tracks read-write dependencies (SIREAD locks) and aborts a transaction that would create a dangerous cycle with SQLSTATE 40001. No extra blocking, but false positives happen and retries are mandatory.
• MySQL InnoDB: converts plain reads to shared locking reads, using next-key locks; it blocks and can deadlock instead of failing with serialization errors.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM booking WHERE room = 5 AND tstzrange(start_at,end_at) && tstzrange(:s,:e);
INSERT INTO booking (room, start_at, end_at) VALUES (5, :s, :e);
COMMIT;  -- may fail with 40001: retry the whole transaction

Alternative for the booking example: an exclusion constraint (EXCLUDE USING gist (room WITH =, during WITH &&)) enforces the invariant declaratively with no isolation tricks.

The retry runs in the same transaction and the same persistence context, so the first-level cache returns the already loaded stale entity; also, after a failed flush the transaction is marked rollback-only and cannot continue. Retries must wrap the whole transaction, outside the proxy boundary, so each attempt gets a new transaction and a new session.

@Service
class TransferFacade {
    private final TransferService service;           // has @Transactional methods
    TransferFacade(TransferService service) { this.service = service; }

    public void transfer(long from, long to, BigDecimal amt) {
        for (int attempt = 1; ; attempt++) {
            try {
                service.transferInTx(from, to, amt); // new transaction per call
                return;
            } catch (ObjectOptimisticLockingFailureException e) {
                if (attempt == 3) throw e;
            }
        }
    }
}

Alternatively use Spring Retry @Retryable placed on a bean that calls the transactional bean (aspect ordering must put retry outside the transaction).

InnoDB plain SELECT uses a consistent snapshot established at the first read, so it ignores later commits. But UPDATE, DELETE and locking reads (FOR UPDATE) always operate on the latest committed version and can modify rows invisible to your snapshot; afterwards those rows become visible to you because your own changes are always seen. This mixes two views and produces surprising results.

-- T1 (REPEATABLE READ)                 -- T2
START TRANSACTION;
SELECT count(*) FROM t WHERE k=1;  -- 5
                                        INSERT INTO t (k) VALUES (1); COMMIT;
SELECT count(*) FROM t WHERE k=1;  -- 5 (snapshot)
UPDATE t SET flag=1 WHERE k=1;     -- affects 6 rows (latest data)
SELECT count(*) FROM t WHERE k=1;  -- 6 (own writes make the row visible)

• On a range with an index, InnoDB also takes gap and next-key locks, blocking inserts into the range by other transactions, which can cause unexpected waits and deadlocks; READ COMMITTED disables most gap locking (a common production setting with row-based binlog).
• PostgreSQL REPEATABLE READ is stricter: updating a row changed since the snapshot fails with a serialization error instead of silently using the latest version.