Database Transactions

A database transaction is a sequence of operations executed as a single logical unit of work — either all operations succeed and are committed, or all are rolled back as if none of them happened.

What It Really Means

Transactions are the mechanism that makes ACID properties real. Without transactions, every SQL statement executes independently. If your application needs to debit one account and credit another, these are two separate operations. If the system crashes between them, you lose money.

Wrapping both operations in a transaction creates a boundary: the database guarantees that both succeed or both fail. This is atomicity — the "A" in ACID. But transactions provide more than atomicity. They also define isolation boundaries (how concurrent transactions interact), consistency enforcement (constraints checked at commit), and durability guarantees (committed data survives crashes).

Every SQL statement in PostgreSQL and MySQL runs inside a transaction, even if you do not explicitly write BEGIN. In autocommit mode, each statement is its own transaction. Understanding explicit transactions is essential when multiple operations must succeed or fail together.

How It Works in Practice

Basic Transaction Lifecycle

Savepoints

Savepoints allow partial rollback within a transaction:

Isolation Level Behavior

Implementation

Handling transaction retries in application code:

Common anti-patterns:

Trade-offs

PostgreSQL's Repeatable Read actually prevents phantoms too, unlike the SQL standard.

Short transactions vs long transactions:

• Short transactions: lower lock contention, less VACUUM interference, better throughput
• Long transactions: hold snapshots that prevent dead tuple cleanup, increase bloat, hold locks

Rule of thumb: Keep transactions as short as possible. Never hold a transaction open during I/O to external systems.

Common Misconceptions

• "Transactions are only for writes" — Read-only transactions benefit from snapshot isolation. A reporting query in a REPEATABLE READ transaction sees a consistent snapshot even if other transactions commit during its execution.
• "BEGIN/COMMIT adds significant overhead" — The overhead is negligible. Autocommit mode wraps each statement in its own transaction anyway, so explicit transactions actually reduce overhead by batching.
• "Serializable isolation solves all concurrency bugs" — It prevents database-level anomalies but not application-level race conditions. Two transactions can both read that a username is available, then both try to insert it.
• "Nested transactions exist in PostgreSQL" — They do not. PostgreSQL uses savepoints to simulate nested transactions. COMMIT inside a nested block does not actually commit.
• "Transactions work across microservices" — Database transactions are local to a single database. For cross-service consistency, use the saga pattern or two-phase commit.

How This Appears in Interviews

1. "How do you ensure a bank transfer is atomic?" — Wrap debit and credit in a single transaction. If either fails, both roll back.
2. "What isolation level would you choose for an e-commerce checkout?" — Read Committed with application-level optimistic locking for most cases. Serializable for critical inventory checks.
3. "How do you handle distributed transactions across microservices?" — Saga pattern with compensating transactions, not distributed database transactions.
4. "Why is this query slow?" — Check for long-running transactions holding locks or preventing VACUUM.

Related Concepts

• ACID Properties — transactions implement all four ACID guarantees
• MVCC — the mechanism that provides snapshot isolation for transactions
• Optimistic vs Pessimistic Locking — strategies for handling write conflicts
• Write-Ahead Logging — how transactions achieve atomicity and durability
• Connection Pooling — pool mode affects transaction behavior
• System Design Interview Guide
• Algoroq Pricing — access all concept deep-dives