Database Transactions: ACID, Isolation Levels, and When It All Goes Wrong

Transactions are one of those topics that developers understand conceptually — group operations so they succeed or fail together — but misunderstand in practice. The subtleties of isolation levels, the difference between phantom reads and non-repeatable reads, and when you actually need serializable isolation all matter in production systems where concurrent users are modifying shared data.

This guide is about the practical side: the bugs that happen without proper transaction isolation and how to fix them.

ACID: What Each Property Actually Means

Atomicity: The transaction succeeds completely or fails completely. If you transfer $100 from account A to account B and the debit succeeds but the credit fails, the debit is rolled back. The database never has a state where $100 was debited but not credited.

Consistency: A transaction brings the database from one valid state to another valid state. Constraints, triggers, and cascades are enforced. You cannot end a transaction with a violated foreign key.

Isolation: Concurrent transactions behave as if they run serially. The degree of this guarantee is controlled by the isolation level — this is where most of the nuance lives.

Durability: Once a transaction is committed, it persists even through crashes. PostgreSQL uses write-ahead logging (WAL) to ensure committed data survives a crash.

The Concurrency Anomalies

Without sufficient isolation, concurrent transactions can produce incorrect results. Understanding these anomalies is the key to choosing the right isolation level.

Dirty Read: Transaction A reads data that Transaction B has written but not yet committed. If Transaction B rolls back, Transaction A was working with data that never existed.

PostgreSQL's Read Committed isolation (the default) prevents dirty reads. Every read sees only committed data.

Non-Repeatable Read: Transaction A reads a row, Transaction B updates and commits it, Transaction A reads the same row again and gets a different value.

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- returns 1000

-- Transaction B commits an update: balance now = 900

SELECT balance FROM accounts WHERE id = 1;  -- returns 900 (different!)
COMMIT;

Phantom Read: Transaction A reads a set of rows matching a condition, Transaction B inserts (or deletes) rows matching the same condition, Transaction A re-reads and gets different rows.

-- Transaction A
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- returns 5

-- Transaction B inserts a new pending order and commits

SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- returns 6 (phantom!)
COMMIT;

Lost Update: Two transactions read the same value, both modify it, and one modification overwrites the other.

-- Transaction A reads balance = 1000, plans to add $100
-- Transaction B reads balance = 1000, plans to add $200
-- Transaction A writes 1100 and commits
-- Transaction B writes 1200 and commits  ← A's update is lost

Write Skew: Two transactions read overlapping data, make decisions based on what they read, and write different data — but the combination of their writes violates a constraint.

Classic example: two doctors are on call, at least one must always be on call. Both see the other is on call, both decide they can take the day off, both update simultaneously, and now zero doctors are on call.

Isolation Levels in PostgreSQL

PostgreSQL implements four isolation levels (though it maps Read Uncommitted to Read Committed):

Read Committed (default):

Prevents: Dirty reads
Allows: Non-repeatable reads, phantom reads, write skew
Use for: Most web application read operations

Repeatable Read:

Prevents: Dirty reads, non-repeatable reads
Allows: Phantom reads (PostgreSQL actually prevents these too)
Use for: Reports and calculations that need a consistent snapshot

Serializable:

Prevents: All anomalies including write skew
More expensive: The database detects and aborts transactions that would produce non-serializable results
Use for: Financial operations, inventory management, anything where correctness is absolute

Set isolation level in PostgreSQL:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

In Prisma:

await prisma.$transaction(
  async (tx) => {
    // Operations here
  },
  { isolationLevel: 'Serializable' }
)

Fixing the Lost Update Problem

Optimistic locking — add a version number and check it on update:

-- Add a version column
ALTER TABLE accounts ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- Update only if version matches what we read
UPDATE accounts
SET balance = balance + 100, version = version + 1
WHERE id = $1 AND version = $readVersion;

-- Check affected rows
-- If 0 rows affected, someone else updated first — retry

In application code:

async function updateBalance(accountId: string, amount: number, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const account = await prisma.account.findUniqueOrThrow({
      where: { id: accountId },
    })

    const updated = await prisma.account.updateMany({
      where: {
        id: accountId,
        version: account.version,  // Optimistic lock check
      },
      data: {
        balance: account.balance + amount,
        version: { increment: 1 },
      },
    })

    if (updated.count === 1) return  // Success
    // Otherwise, retry
  }

  throw new Error('Update failed after retries due to concurrent modification')
}

Pessimistic locking — lock the row when reading it:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions trying to modify this row will wait
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

FOR UPDATE acquires a row-level lock. Other transactions that try to lock the same row (FOR UPDATE, FOR SHARE) will wait until this transaction commits or rolls back.

In Prisma:

await prisma.$transaction(async (tx) => {
  const account = await tx.$queryRaw<Account[]>`
    SELECT * FROM accounts WHERE id = ${accountId} FOR UPDATE
  `

  if (account[0].balance < amount) {
    throw new Error('Insufficient funds')
  }

  await tx.account.update({
    where: { id: accountId },
    data: { balance: account[0].balance - amount },
  })
})

Fixing Write Skew With Serializable Isolation

Write skew (the on-call doctors problem) requires serializable isolation — nothing else prevents it:

await prisma.$transaction(
  async (tx) => {
    // Read the current state
    const onCallDoctors = await tx.doctor.count({
      where: { onCall: true },
    })

    // Make a decision based on it
    if (onCallDoctors <= 1) {
      throw new Error('Cannot go off-call: minimum 1 doctor required')
    }

    // Write based on the decision
    await tx.doctor.update({
      where: { id: doctorId },
      data: { onCall: false },
    })
  },
  { isolationLevel: 'Serializable' }
)

With serializable isolation, if two transactions execute this simultaneously and both would produce an invalid state, PostgreSQL aborts one of them with a serialization failure error. Your application catches this and retries.

Deadlocks

Deadlocks happen when two transactions each hold a lock that the other needs:

Transaction A locks row X, waits for row Y
Transaction B locks row Y, waits for row X

PostgreSQL detects deadlocks automatically and aborts one transaction (with error code 40P01). The application should retry on this error.

Prevent deadlocks by always acquiring locks in the same order:

// BAD: transactions might acquire locks in different orders
// Transaction A: lock user 1, then lock account 1
// Transaction B: lock account 1, then lock user 1

// GOOD: always lock in a consistent order
async function transfer(fromId: string, toId: string, amount: number) {
  // Always lock the smaller ID first
  const [first, second] = [fromId, toId].sort()

  await prisma.$transaction(async (tx) => {
    await tx.$queryRaw`SELECT id FROM accounts WHERE id IN (${first}, ${second}) FOR UPDATE`
    // Now proceed with the transfer
  })
}

Transactions are a deep topic, but for most web applications, the default Read Committed isolation with optimistic locking for concurrent modifications covers the majority of cases. Reach for Serializable isolation deliberately, understand its retry requirements, and benchmark the performance impact for your specific workload.

Dealing with data consistency issues in a concurrent application, or designing a transaction strategy for a financial or inventory system? This is exactly the kind of problem I work through with clients. Book a call: calendly.com/jamesrossjr.