Database Transactions: ACID, Isolation Levels, and Deadlocks

Two users buy the last item simultaneously. Both succeed. You shipped one item you do not have. Transactions prevent this.

ACID Properties

• Atomicity: All operations succeed or all fail. No partial updates.
• Consistency: Data moves from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Committed data survives crashes.

Transaction in Practice

async function purchaseItem(userId: string, itemId: string) {
  return db.transaction(async (trx) => {
    const item = await trx("items").where({ id: itemId }).where("stock", ">", 0).forUpdate().first();
    if (\!item) throw new Error("Out of stock");
    await trx("items").where({ id: itemId }).decrement("stock", 1);
    return trx("orders").insert({ userId, itemId, price: item.price }).returning("*");
  });
}

Isolation Levels

• READ COMMITTED (PostgreSQL default): prevents dirty reads
• REPEATABLE READ: prevents non-repeatable reads
• SERIALIZABLE: prevents phantom reads (slowest)

Avoiding Deadlocks

• Lock rows in same order across all transactions
• Keep transactions short
• Use SELECT FOR UPDATE SKIP LOCKED for queue patterns

---

Production Backend Patterns series.