Transactions, ACID, Isolation, Deadlocks — Revision Guide

---

What is a Transaction

A unit of work that executes as a single atomic operation against the database. Defined by a BEGIN and a COMMIT or ROLLBACK. Everything in between either fully commits or fully rolls back.

---

ACID

Atomicity

All or nothing. If any part of the transaction fails, everything rolls back.

Implemented via WAL (Write Ahead Log) — every change is written to the log before applied. On failure, PostgreSQL replays the log in reverse to undo.

Note: Atomicity is scoped to the database. Crossing into a second system (RabbitMQ, HTTP) breaks the atomicity boundary — which is why the outbox pattern exists.

Consistency

Data moves from one valid state to another. Constraints, foreign keys, check constraints, triggers never violated. Can't insert an order with a non-existent customer.

Consistency is split — the DB enforces structural constraints, you enforce business rules. Both sides must hold.

Implemented via the constraint engine — enforced at write time before commit.

Isolation

Concurrent transactions don't interfere with each other. Each transaction feels like it's the only one running.

The degree of isolation is configurable — full isolation is expensive, so PostgreSQL exposes a dial. This is where isolation levels live.

Implemented via MVCC in PostgreSQL — multiple row versions kept simultaneously, each transaction sees a consistent snapshot.

Durability

Once committed, data survives crashes. Written to disk, not just memory.

Implemented via WAL — changes written to WAL on disk before commit is acknowledged. On crash, PostgreSQL replays WAL to recover committed transactions.

---

MVCC — How PostgreSQL Implements Isolation

Every row has two hidden columns: - xmin — transaction ID that created this row version - xmax — transaction ID that deleted/updated this row (0 if still live)

When your transaction starts, PostgreSQL records the current transaction ID — say txid = 500.

When you read a row, PostgreSQL checks: - Was this row created before txid 500? (xmin < 500) - Is it still alive? (xmax = 0 or xmax > 500)

If yes — visible. If no — not visible.

No row copying. No snapshot stored. Just a transaction ID and visibility check per row. Old versions kept alive until VACUUM cleans them up.

Key consequence: In PostgreSQL, readers never block writers and writers never block readers. A plain SELECT acquires zero locks. It just picks the appropriate row version from its snapshot. This is fundamentally different from lock-based databases.

---

Snapshot Internals — What PostgreSQL Actually Builds

Every transaction gets a snapshot object — not just a single transaction ID. Three fields:

• xmin — lowest transaction ID still in progress when snapshot was taken. Everything below this is fully settled — committed or rolled back.
• xmax — next unassigned transaction ID. Everything at or above this didn't exist when snapshot was taken. Invisible.
• xip_list — exact list of transaction IDs that were in-flight between xmin and xmax at snapshot time.

Example:

When your transaction starts:
  Transaction 490 — committed
  Transaction 495 — still running
  Transaction 498 — still running
  Transaction 500 — your transaction
  Next unassigned — 501

Your snapshot:
  xmin     = 495
  xmax     = 501
  xip_list = [495, 498]

Now 495 commits while your transaction is running. Do you see its writes?

• Under RR — No. 495 is in your xip_list. It was in-flight when you started. Ignored regardless of when it commits.
• Under RC — your snapshot is rebuilt at every statement. 495 is no longer in-flight. New xip_list doesn't include it. You see it.

Row visibility check per read:

Is xmin of this row < my xmax?          — created before I started?
Is xmin of this row NOT in xip_list?    — already committed when I started?
Is xmax of this row 0 or in xip_list?   — still alive or deleted by in-flight tx?

All three must hold for the row to be visible.

RC vs RR is literally just when the snapshot object is rebuilt: - RR — once at transaction start, xip_list frozen for entire transaction - RC — rebuilt at every statement start, xip_list refreshed each time

Same mechanism. Different frequency.

---

Isolation Problems

Dirty Read

Reading uncommitted data from another transaction. Transaction A updates a row, Transaction B reads it before A commits. A rolls back — B read data that never existed.

PostgreSQL prevents this at every isolation level, including Read Uncommitted. It does not implement dirty reads at all.

Non-repeatable Read

Same row reads different value within the same transaction. Transaction A reads a row. Transaction B updates and commits. Transaction A reads same row again — different value.

Why it matters: Transaction A is calculating payroll. First read 50000, does calculations. Second read of same employee — 70000. Internal state is now inconsistent.

This is fundamentally a multi-statement problem. The gap between two statements is where the world changes.

Phantom Read

Same range query returns different rows within the same transaction. Transaction A queries all orders > 1000 — gets 5 rows. Transaction B inserts a new order > 1000 and commits. Transaction A runs same query — gets 6 rows.

---

Isolation Levels

Each level answers: how frozen is my view of the world?

The snapshot is the mechanism — when is it taken, how long does it last.

Level	Snapshot taken at	Dirty Read	Non-repeatable Read	Phantom Read
Read Uncommitted	N/A — reads live data	possible	possible	possible
Read Committed	Start of each statement	prevented	possible	possible
Repeatable Read	Start of the transaction	prevented	prevented	prevented (PostgreSQL) / possible (standard)
Serializable	Transaction start + conflict detection	prevented	prevented	prevented

Read Uncommitted

Reads live uncommitted data. PostgreSQL doesn't implement it — silently upgrades to Read Committed.

Read Committed (PostgreSQL default)

Snapshot taken at the start of each statement. Each new statement sees all commits made before it ran. Non-repeatable reads possible because two statements in the same transaction can see different committed states.

Repeatable Read

Snapshot taken at transaction start, held for entire transaction. All reads see the same frozen state. External commits after transaction start are invisible.

PostgreSQL vs the SQL standard: The SQL standard says Repeatable Read only prevents non-repeatable reads — phantoms require Serializable. PostgreSQL's MVCC snapshot covers the entire state at transaction start, so new rows are also invisible. PostgreSQL's RR prevents phantoms too.

In lock-based databases (older MySQL) — RR uses row locks on rows already read, plus gap locks on index ranges to prevent new matching rows from being inserted. Gap locks block inserts. Readers and writers block each other — high contention, correct result.

PostgreSQL's snapshot approach achieves the same result with zero blocking.

Interview answer: "The standard says Serializable is required for phantom prevention. PostgreSQL's MVCC implementation prevents phantoms at Repeatable Read as well."

Serializable

Full isolation. Transactions execute as if sequential. PostgreSQL uses SSI (Serializable Snapshot Isolation) — tracks read/write dependencies between transactions, aborts one if a cycle is detected. Application must handle serialization errors and retry.

---

Write Skew — What Serializable Solves That RR Doesn't

Two transactions read overlapping data, each makes a valid decision based on it, each writes to different rows — combined result violates an invariant that neither write alone would.

No dirty read. No non-repeatable read. No phantom. But the outcome is wrong.

-- Rule: at least one doctor must be on call
-- Current state: doctor_a on_call = true, doctor_b on_call = true

-- Transaction A
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- sees 2, safe
UPDATE doctors SET on_call = false WHERE id = 'doctor_a';

-- Transaction B, concurrent
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- sees 2, safe
UPDATE doctors SET on_call = false WHERE id = 'doctor_b';

-- Result: 0 doctors on call. Rule violated.

A wrote to doctor_a, B wrote to doctor_b. No row lock conflict. RR cannot detect this.

Why a subquery inside UPDATE doesn't fix it:

UPDATE doctors SET on_call = false
WHERE id = 'doctor_a'
AND (SELECT COUNT(*) FROM doctors WHERE on_call = true) > 1;

The statement is atomic — it won't half-execute. But under RC, the subquery snapshot is taken at statement start. Both instances start before either commits. Both subqueries see count = 2. Both conditions pass. They lock different rows — no conflict. Both commit. Still broken.

The precise condition for write skew: - Invariant spans multiple rows - Each transaction writes to different rows — no row lock conflict - Combined writes violate the invariant

Real production example (PEPPOL batch limit):

-- Rule: max 100 invoices in 'processing' at once
-- Current count: 99
-- Instance A updates inv_001, Instance B updates inv_002
-- Both subqueries see 99, both proceed, result: 101 in processing

Fix options: 1. Serializable isolation — DB detects the conflict, aborts one 2. Semaphore row — single row with CHECK constraint, UPDATE ... SET available = available - 1 WHERE available > 0 3. Push the concern up — Redis atomic counter, RabbitMQ prefetch count

Write skew often signals a DB design smell — the invariant is being enforced in the wrong place.

---

Single Statement vs Multi-Statement — The Core Distinction

Single statement conditional UPDATE:

UPDATE invoices SET status = 'processing' WHERE id = 42 AND status = 'pending';

The WHERE clause evaluation and the write are inseparable at the storage level. When two instances execute this concurrently: 1. One acquires the row lock, updates, holds lock until commit 2. The other hits the lock, waits — not just for the statement, for the entire transaction to commit 3. After commit, the waiter re-evaluates the WHERE clause against committed state 4. Sees status = 'processing', condition fails, 0 rows affected

Safe at any isolation level. The check and the write cannot be separated.

Multi-statement (dangerous):

-- Round trip 1
SELECT status FROM invoices WHERE id = 42;  -- no lock acquired
-- application decides to proceed
-- Round trip 2
UPDATE invoices SET status = 'processing' WHERE id = 42;

The SELECT acquires no lock. Both instances read 'pending'. Both decide to proceed. Both update. Both win. Double processing.

The gap between SELECT and UPDATE is the window. Isolation levels control how wide that gap can hurt you.

Critical: isolation level affects multi-statement gaps, not single statement safety.

---

Row Lock Lifetime

Row lock is acquired on UPDATE / DELETE / SELECT FOR UPDATE.

Released on COMMIT or ROLLBACK — not on statement completion.

If Transaction A takes 10 seconds between its UPDATE and its COMMIT — every other transaction waiting on that row is blocked for those 10 seconds. Long transactions under high contention are dangerous.

---

Two-Instance Concurrency — Isolation Level Comparison

For the problem: two instances trying to claim the same invoice for processing.

Approach	Safe?	Why
Atomic conditional UPDATE (RC)	Yes	Row lock forces B to re-evaluate at committed state
Atomic conditional UPDATE (RR)	Yes	Same — single statement, row lock, re-evaluation
SELECT then UPDATE (RC)	No	Gap between statements, both read 'pending'
SELECT then UPDATE (RR)	Worse	Snapshot frozen — B still sees 'pending' after A commits
SELECT FOR UPDATE (RC or RR)	Yes	Lock acquired at SELECT, B waits, sees committed state

RC accidentally helps for multi-statement because per-statement snapshot refresh kicks in after lock wait. RR removes that safety net by freezing the snapshot. RR is worse than RC for this specific problem if using separate SELECT + UPDATE.

---

SELECT FOR UPDATE

Explicit row lock acquired at read time. Use when the check and the write cannot be collapsed into a single statement.

BEGIN;
SELECT status FROM invoices WHERE id = 42 FOR UPDATE;
-- row is now locked — other transactions block here
-- application logic runs
UPDATE invoices SET status = 'processing' WHERE id = 42;
COMMIT;
-- lock released

Requires a transaction. Without a transaction, the lock is released immediately after the SELECT — before you act on it. Useless.

Lock window comparison:

• Atomic conditional UPDATE — lock held only during the write
• SELECT FOR UPDATE — lock held from SELECT through application logic through write until COMMIT

In high contention scenarios SELECT FOR UPDATE creates a longer queue. Atomic conditional UPDATE is preferred when the check can be expressed in a WHERE clause.

When SELECT FOR UPDATE is necessary: 1. Read then apply application logic — logic cannot be expressed in SQL 2. Locking multiple rows before touching either 3. Lock then insert — the row being written doesn't exist yet, nothing to conditionally UPDATE

PostgreSQL behaviour: Under both RC and RR, SELECT FOR UPDATE re-reads committed state when it acquires the lock after waiting. This is a special exception to RR's snapshot freeze — locked reads refresh after a wait.

---

Deadlocks

Two transactions waiting for each other to release a lock.

Transaction A holds lock on row 1, wants lock on row 2
Transaction B holds lock on row 2, wants lock on row 1
Both wait forever

PostgreSQL detects this automatically and aborts one transaction with:

ERROR: deadlock detected

How to avoid: - Always acquire locks in the same order across transactions - Keep transactions short — hold locks for as little time as possible - Use SELECT FOR UPDATE explicitly so lock acquisition order is visible in code

---

Transactions in .NET

EF Core — implicit transaction

context.Orders.Add(order);
context.Invoices.Add(invoice);
await context.SaveChangesAsync(); // one transaction, both committed or neither

SaveChanges wraps everything in a transaction automatically.

EF Core — explicit transaction

await using var tx = await context.Database.BeginTransactionAsync();
try
{
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    context.Invoices.Add(invoice);
    await context.SaveChangesAsync();
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Use when you need multiple SaveChanges calls in one transaction.

EF Core — atomic conditional UPDATE (EF7+)

var rows = await context.Invoices
    .Where(i => i.Id == 42 && i.Status == InvoiceStatus.Pending)
    .ExecuteUpdateAsync(s => s
        .SetProperty(i => i.Status, InvoiceStatus.Processing));

if (rows == 0) // lost the race — another instance claimed it

Translates directly to a single UPDATE ... WHERE statement. No SELECT. No gap. Safe under concurrent access.

The classic EF trap:

var invoice = await context.Invoices.FindAsync(42); // SELECT — no lock
invoice.Status = InvoiceStatus.Processing;
await context.SaveChangesAsync(); // UPDATE — gap exists between these two

This is SELECT then UPDATE. Two round trips. Both instances read, both update, both win. Wrong.

EF Core — SELECT FOR UPDATE

await using var tx = await context.Database.BeginTransactionAsync();

var invoice = await context.Invoices
    .FromSql($"SELECT * FROM invoices WHERE id = 42 FOR UPDATE")
    .FirstAsync();

invoice.Status = InvoiceStatus.Processing;
await context.SaveChangesAsync();

await tx.CommitAsync();

Transaction is mandatory. Lock is held from SELECT to COMMIT.

Dapper — manual transaction

using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
using var transaction = await connection.BeginTransactionAsync();
try
{
    await connection.ExecuteAsync("INSERT INTO orders...", order, transaction);
    await connection.ExecuteAsync("INSERT INTO invoices...", invoice, transaction);
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

No change tracking. You own the transaction explicitly.

---

Unit of Work

The transaction object is the unit of work — defines what commits together or rolls back together.

EF Core's DbContext is the built-in unit of work — tracks all changes, commits in one SaveChanges call.

Explicit IUnitOfWork wrapper only needed when: - Multiple DbContext instances need coordinating - Abstracting ORM for testability - Fine-grained transaction control

---

Key One-Liners

• Atomicity — all or nothing, WAL enables rollback. Scoped to the DB — crossing systems breaks it.
• Consistency — constraint engine enforces rules at write time. Business rules are your responsibility.
• Isolation — MVCC gives each transaction a snapshot. The dial is isolation level.
• Durability — WAL written to disk before commit acknowledged.
• Plain SELECT in PostgreSQL — acquires zero locks. MVCC means readers never block writers.
• Row lock — acquired on write, held until COMMIT or ROLLBACK, not statement completion.
• Read Committed — snapshot per statement. Fresh view after each statement.
• Repeatable Read — snapshot per transaction. Frozen view. PostgreSQL prevents phantoms too via MVCC.
• Lock-based RR (MySQL) — row locks + gap locks. Readers block writers. Same correctness, more contention.
• Serializable — SSI detects write skew, aborts conflicting transactions. Application must retry.
• Single statement conditional UPDATE — safe at any isolation level. Check and write are inseparable.
• Multi-statement SELECT then UPDATE — RC accidentally safer than RR here. Neither is correct under concurrency.
• Write skew — invariant spans multiple rows, writes to different rows, combined result wrong. Serializable or redesign.
• SELECT FOR UPDATE — explicit row lock at read time. Mandatory transaction. Longer lock window than atomic UPDATE.
• Deadlock — PostgreSQL detects and aborts one. Always acquire locks in same order.
• EF Core trap — FindAsync + SaveChanges is SELECT then UPDATE. Use ExecuteUpdateAsync for atomic conditional updates.