Database Transactions

When you buy concert tickets online, what happens if the payment succeeds but the ticket reservation fails? Or when transferring money between bank accounts, what prevents your account from being debited without the recipient's account being credited? These scenarios require database transactions, the foundation of data consistency.

Understanding transactions is crucial for system design interviews involving Design TicketMaster, Design E-commerce Store, and any system handling limited resources like inventory, seats, or account balances.

The Transaction Problem

Consider this e-commerce scenario without transactions:

# Dangerous: No transaction protection
def purchase_item(user_id, item_id, quantity):
    # Step 1: Check inventory
    available = get_inventory_count(item_id)
    if available < quantity:
        return "Out of stock"

    # Step 2: Charge payment
    payment_result = charge_credit_card(user_id, item_price * quantity)
    if not payment_result.success:
        return "Payment failed"

    # Step 3: Reduce inventory
    reduce_inventory(item_id, quantity)

    # Step 4: Create order record
    create_order(user_id, item_id, quantity)

    return "Purchase successful"

What could go wrong?

This code has four separate database operations that must all succeed together. But since they're not wrapped in a transaction, failures can occur between any steps:

Payment succeeds, but inventory reduction fails → If reduce_inventory() throws an exception or the database connection drops after step 2, the customer gets charged but keeps their money AND the item remains in stock for others to buy.
Inventory reduced, but order creation fails → If create_order() fails after step 3, the inventory count decreases but there's no record of who bought it or how to fulfill the order. The item is effectively lost.
Two customers buying the last item simultaneously → Customer A and B both execute step 1 at the same time, both see available = 1, both proceed through payment, both reduce inventory. Final result: -1 items in stock, but both customers expect to receive the item.
System crashes between steps → If the server crashes after payment but before inventory update, the customer is charged but the item remains available. After restart, there's no way to know which payments need to complete their purchase flow.

The core problem: Multi-step operations need all-or-nothing execution to maintain data integrity.

Transaction Problem Scenario

What Are Database Transactions?

A database transaction is a sequence of operations that are executed as a single, atomic unit. Either all operations succeed, or none of them do.

Transactions solve the coordination problem when multiple related changes must happen together consistently.

Transactions vs Individual Queries

Individual Query (single operation):

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'alice';

Transaction (multiple related operations):

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 'alice';
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 'bob';
    INSERT INTO transfers (from_account, to_account, amount) VALUES ('alice', 'bob', 100);
COMMIT;

The transaction ensures that Alice's debit, Bob's credit, and the transfer record all succeed together, or none of them happen.

Transaction Lifecycle Flow

ACID Properties: The Transaction Contract

ACID properties define the reliability guarantees that transactions provide:

ACID Properties Explained

Atomicity: All or Nothing

Definition: Either the entire transaction succeeds, or none of it happens.

Real-world analogy: Buying a combo meal - you get the burger, fries, AND drink, or you get nothing. You can't get charged for the combo but only receive the burger.

Atomicity in Action

See how transaction rollback prevents partial failures in a banking system

Loading Python environment... This may take a few seconds on first load.

Consistency: Valid State to Valid State

Definition: Transactions move the database from one valid state to another, preserving all defined rules and constraints.

Consistency prevents business rule violations, like negative account balances or booking more seats than exist.

Examples of consistency rules:

Bank account balances cannot go negative
Concert venues cannot sell more tickets than seats available
User emails must be unique
Foreign key relationships must remain valid

Isolation: Concurrent Transactions Don't Interfere

Definition: Concurrent transactions execute as if they were running one after another, even when running simultaneously.

The problem without isolation:

# Two users buying the last concert ticket simultaneously
User A: checks availability → sees 1 ticket available
User B: checks availability → sees 1 ticket available
User A: purchases ticket → inventory becomes 0
User B: purchases ticket → inventory becomes -1 (overbooking!)

With proper isolation:

# Database ensures only one purchase succeeds
User A: BEGIN TRANSACTION → checks availability → purchases ticket → COMMIT
User B: BEGIN TRANSACTION → waits for User A → checks availability → sees 0 tickets → fails

For User B to actually wait, the read must take a lock (SELECT ... FOR UPDATE) or run under Serializable isolation. Plain Read Committed does not block—both users could read the same availability and overbook.

Learn More: For deep dive into how different isolation levels prevent dirty reads, phantom reads, and other concurrency issues, see Database Isolation Levels.

Durability: Committed Changes Are Permanent

Definition: Once a transaction commits, the changes survive system crashes, power failures, and other failures.

How databases achieve durability:

Write-Ahead Logging (WAL): Changes are logged to disk before being applied
Checkpoints: Periodic saves of the entire database state
Replication: Changes are copied to multiple database servers

Real-world importance: After you submit your tax return online, the data must survive even if the server crashes immediately after confirmation.

Real-World Transaction Patterns

E-commerce Purchase Flow

E-commerce Transaction Flow

See how complex business operations are broken down into atomic transactions

Loading Python environment... This may take a few seconds on first load.

Concert Ticket Sales (Race Conditions)

The classic race condition scenario: What happens when multiple users try to buy the last few tickets simultaneously?

Without proper isolation:

# Both users see 2 tickets available
User A: SELECT available_tickets FROM events WHERE id = 'concert123';  # Returns 2
User B: SELECT available_tickets FROM events WHERE id = 'concert123';  # Returns 2

# Both users proceed to buy 2 tickets each
User A: UPDATE events SET available_tickets = 0 WHERE id = 'concert123';  # 2 -> 0
User B: UPDATE events SET available_tickets = -2 WHERE id = 'concert123'; # 0 -> -2

# Result: 4 tickets sold for 2 available seats!

With proper transaction isolation:

-- User A's transaction
BEGIN TRANSACTION;
SELECT available_tickets FROM events WHERE id = 'concert123' FOR UPDATE;  -- Locks row
-- User B must wait here
UPDATE events SET available_tickets = available_tickets - 2 WHERE id = 'concert123';
COMMIT;  -- Lock released

-- User B's transaction (runs after A completes)
BEGIN TRANSACTION;
SELECT available_tickets FROM events WHERE id = 'concert123' FOR UPDATE;  -- Returns 0
-- Sees no tickets available, purchase fails safely
ROLLBACK;

When to Use Transactions vs When to Avoid Them

Use Transactions For:

✅ Financial operations

Money transfers, payments, refunds
Account balance updates
Any operation involving currency

✅ Inventory management

Product purchases, reservations
Stock updates, order fulfillment
Limited resource allocation

✅ User account operations

Registration with multiple steps
Profile updates affecting multiple tables
Permission changes across systems

✅ Data integrity requirements

Referential integrity across tables
Business rule enforcement
Audit trail consistency

Avoid Transactions For:

❌ Read-only operations

Data analytics, reporting
Search queries, content browsing
Logging, monitoring data

❌ Independent operations

Sending emails, notifications
Updating caches, search indexes
Operations that can safely fail/retry

❌ Long-running processes

Batch data processing
File uploads, media processing
Operations taking > 1 second

❌ High-frequency writes

User activity tracking
Real-time analytics
Performance metrics collection

Transaction Best Practices

Keep Transactions Short

# Bad: Long-running transaction
BEGIN TRANSACTION;
    UPDATE orders SET status = 'processing';
    send_email_to_customer();        # Network call - slow!
    process_payment_with_api();      # External API - very slow!
    UPDATE orders SET status = 'completed';
COMMIT;

# Good: Short transaction, async processing
BEGIN TRANSACTION;
    UPDATE orders SET status = 'processing';
COMMIT;

# Handle slow operations asynchronously
queue_email_task(customer_id);
queue_payment_task(order_id);

Handle Deadlocks Gracefully

def transfer_money(from_account, to_account, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            BEGIN TRANSACTION;
            # Always lock accounts in consistent order (by ID) to prevent deadlocks
            accounts = sorted([from_account, to_account], key=lambda x: x.id)
            for account in accounts:
                SELECT * FROM accounts WHERE id = account.id FOR UPDATE;

            # Perform transfer
            UPDATE accounts SET balance = balance - amount WHERE id = from_account.id;
            UPDATE accounts SET balance = balance + amount WHERE id = to_account.id;
            COMMIT;
            return "Success"

        except DeadlockDetected:
            ROLLBACK;
            if attempt < max_retries - 1:
                sleep(random.uniform(0.1, 0.5))  # Random backoff
            else:
                return "Transfer failed after retries"

Use Appropriate Isolation Levels

# High-consistency operations (financial)
with transaction(isolation_level=SERIALIZABLE):
    transfer_money(from_account, to_account, amount)

# Normal operations (most common)
with transaction(isolation_level=READ_COMMITTED):
    update_user_profile(user_id, new_data)

# Analytics, reporting (performance-focused)
with transaction(isolation_level=READ_UNCOMMITTED):
    generate_sales_report()

Deep Dive: Learn about dirty reads, phantom reads, and how each isolation level handles concurrency in Database Isolation Levels.

System Design Interview Tips

"How would you handle race conditions in ticket booking?"

Explain pessimistic locking with SELECT FOR UPDATE
Discuss optimistic locking with version numbers
Consider queue-based approaches for high concurrency

"How do you ensure payment consistency in an e-commerce system?"

Describe the transaction boundary around payment + inventory
Explain compensation patterns for distributed systems
Discuss event sourcing for complex financial operations

"What happens if a database crashes mid-transaction?"

Explain Write-Ahead Logging (WAL) for durability
Describe transaction recovery on restart
Discuss the role of checkpoints and replication

Common Mistakes to Avoid

Making transactions too long → Leads to deadlocks and poor performance
Not handling deadlocks → Application crashes instead of graceful retry
Wrong isolation level → Either poor performance or race conditions
Ignoring distributed transactions → Inconsistency across microservices
Not considering compensation → Can't handle failures in distributed systems