Transactions

Transactions

Why This Matters

Think of transactions like a safety net for your data. When you transfer money from one account to another, you want both operations to happen together—or not at all. You don't want money to disappear from one account and never appear in the other.

This matters because databases are shared resources. Multiple transactions can run simultaneously, and without transactions, you'd have race conditions, data corruption, and lost updates. Transactions ensure that operations are atomic—either all succeed or all fail.

In interviews, when someone asks "How would you design a payment system?", they're testing whether you understand transactions. Do you know when to use them, and when to avoid them? Most engineers don't. They either use transactions for everything (slow) or nothing (unreliable).

What Engineers Usually Get Wrong

Most engineers think "transactions are always necessary." But transactions have overhead. Locking can cause contention. Logging adds I/O overhead. For high-throughput systems, this overhead can be a bottleneck. Many systems relax transaction guarantees for better performance (eventual consistency).

Engineers also think "transactions prevent all race conditions." But transactions only prevent race conditions within a single database. If you have multiple databases or services, you need distributed transactions (Saga pattern, 2PC), which are more complex and have different trade-offs.

How This Breaks Systems in the Real World

An e-commerce system was processing orders. The process was: (1) create order record, (2) charge credit card, (3) update inventory. This was done in a transaction with serializable isolation. Under normal load, this worked. But during a sale (high traffic), many transactions tried to update the same inventory row simultaneously. Serializable isolation requires transactions to run one at a time for conflicting rows. Transactions started queuing. Response times went from 100ms to 10 seconds.

The fix? Use a lower isolation level (read committed) and handle race conditions in application code (optimistic locking). The lesson? Isolation levels are trade-offs. Higher isolation is safer but slower.

Another story: A service was using transactions for logging. Every log entry was wrapped in a transaction. During high traffic, the transaction overhead became a bottleneck. The service became slow. The fix? Don't use transactions for logging—logging doesn't need ACID guarantees. Use eventual consistency instead.

---

What is a Transaction?

A transaction groups multiple operations into a single atomic unit:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Either:

• Both updates succeed (transaction commits)
• Both updates fail (transaction rolls back)

Never:

• One update succeeds and the other fails

---

ACID Properties

Transactions guarantee ACID properties (see ACID Properties topic for details):

• Atomicity: All or nothing
• Consistency: Valid state transitions
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

---

Transaction States

BEGIN
  ↓
ACTIVE (executing operations)
  ↓
COMMIT or ROLLBACK
  ↓
COMMITTED or ABORTED

Committed

All operations completed successfully, changes are permanent.

BEGIN;
  INSERT INTO orders (id, total) VALUES (1, 100);
COMMIT;  -- Transaction committed, order is permanent

Rolled Back

Transaction aborted, all changes are undone.

BEGIN;
  INSERT INTO orders (id, total) VALUES (1, 100);
  -- Error occurs
ROLLBACK;  -- Transaction rolled back, order is not created

---

Basic Transaction Operations

BEGIN / START TRANSACTION

Start a new transaction.

BEGIN;
-- or
START TRANSACTION;

COMMIT

Save all changes made in the transaction.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- Changes are now permanent

ROLLBACK

Undo all changes made in the transaction.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- Something went wrong
ROLLBACK;  -- Changes are undone, balance restored

SAVEPOINT

Create a point to which you can roll back.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT sp1;
  UPDATE accounts SET balance = balance + 50 WHERE id = 2;
  -- Oops, second update was wrong
  ROLLBACK TO SAVEPOINT sp1;  -- Undo second update, keep first
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Correct update
COMMIT;

---

Transaction Examples

Money Transfer

BEGIN;
  -- Deduct from source account
  UPDATE accounts 
  SET balance = balance - 100 
  WHERE id = 1 AND balance >= 100;
  
  -- Check if update succeeded
  IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    RETURN 'Insufficient funds';
  END IF;
  
  -- Add to destination account
  UPDATE accounts 
  SET balance = balance + 100 
  WHERE id = 2;
  
COMMIT;
RETURN 'Transfer successful';

Order Processing

BEGIN;
  -- Create order
  INSERT INTO orders (customer_id, total) 
  VALUES (123, 150.00);
  
  SET @order_id = LAST_INSERT_ID();
  
  -- Add order items
  INSERT INTO order_items (order_id, product_id, quantity, price)
  VALUES 
    (@order_id, 1, 2, 50.00),
    (@order_id, 2, 1, 50.00);
  
  -- Update inventory
  UPDATE products SET stock = stock - 2 WHERE id = 1;
  UPDATE products SET stock = stock - 1 WHERE id = 2;
  
  -- Check for stock issues
  IF EXISTS (SELECT 1 FROM products WHERE stock < 0) THEN
    ROLLBACK;
    RETURN 'Insufficient stock';
  END IF;
  
COMMIT;
RETURN 'Order created';

---

Nested Transactions

Some databases support nested transactions (savepoints):

BEGIN;  -- Outer transaction
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  
  BEGIN;  -- Inner transaction (savepoint)
    UPDATE accounts SET balance = balance + 50 WHERE id = 2;
    -- If this fails, rollback inner transaction only
  COMMIT;  -- or ROLLBACK for inner
  
COMMIT;  -- Outer transaction

Note: Not all databases support true nested transactions. Many use savepoints instead.

---

Transaction Isolation

Isolation controls how transactions interact with each other (see Isolation Levels topic).

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
  SELECT balance FROM accounts WHERE id = 1;
  -- Other transactions can see changes after COMMIT
COMMIT;

---

Distributed Transactions

Transactions spanning multiple databases or services.

Two-Phase Commit (2PC)

Coordinator:
  1. Prepare phase: Ask all participants if they can commit
  2. Commit phase: If all say yes, tell all to commit

Challenges:

• Coordinator is single point of failure
• Blocking if coordinator fails
• Network partitions cause issues

Saga Pattern

Break transaction into smaller, compensatable steps.

Step 1: Reserve inventory → If fails, no compensation needed
Step 2: Charge credit card → If fails, release inventory
Step 3: Create order → If fails, refund card and release inventory

Each step has a compensating action.

---

Best Practices

Keep Transactions Short

-- ❌ Bad: Long transaction holds locks
BEGIN;
  -- Complex processing
  -- Network calls
  -- User input
  -- More processing
COMMIT;

-- ✅ Good: Short transaction
-- Do processing outside transaction
-- Then:
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Handle Errors

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  
  IF @@ERROR <> 0 THEN
    ROLLBACK;
    RETURN 'Error occurred';
  END IF;
  
COMMIT;

Avoid Long-Running Transactions

• Hold locks for minimal time
• Don't do I/O operations inside transactions
• Don't wait for user input

Use Appropriate Isolation Levels

• READ COMMITTED: Default, good for most cases
• REPEATABLE READ: When you need consistent reads
• SERIALIZABLE: Only when absolutely necessary

---

Common Patterns

Optimistic Locking

Assume conflicts are rare, detect and retry if they occur.

-- Read with version
SELECT id, balance, version FROM accounts WHERE id = 1;
-- balance = 100, version = 5

-- Update with version check
UPDATE accounts 
SET balance = balance - 50, version = version + 1
WHERE id = 1 AND version = 5;

-- If no rows updated, someone else modified it
IF ROW_COUNT() = 0 THEN
  -- Retry or return error
END IF;

Pessimistic Locking

Lock data upfront to prevent conflicts.

BEGIN;
  -- Lock row
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  
  -- Now safe to update
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Retry Logic

def transfer_money(from_id, to_id, amount):
    max_retries = 3
    for attempt in range(max_retries):
        try:
            db.begin_transaction()
            # ... transfer logic ...
            db.commit()
            return success
        except DeadlockError:
            if attempt < max_retries - 1:
                sleep(random.uniform(0, 0.1) * (2 ** attempt))  # Exponential backoff
                continue
            raise
        except:
            db.rollback()
            raise

---

Database-Specific Notes

PostgreSQL

-- Explicit transaction
BEGIN;
  -- operations
COMMIT;

-- Auto-commit mode (default)
-- Each statement is its own transaction

MySQL

-- InnoDB supports transactions
-- MyISAM does not (table-level locking only)

-- Check if using InnoDB
SHOW TABLE STATUS WHERE Name = 'accounts';

SQL Server

-- Explicit transaction
BEGIN TRANSACTION;
  -- operations
COMMIT TRANSACTION;
-- or
ROLLBACK TRANSACTION;

---

Common Pitfalls

Deadlocks

Two transactions waiting for each other's locks.

-- Transaction 1
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Waits for T2

-- Transaction 2
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE id = 2;
  UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- Waits for T1

-- Deadlock! Database aborts one transaction

Solution: Consistent lock ordering, shorter transactions, retry logic

Lost Updates

Two transactions read, modify, and write, overwriting each other's changes.

Solution: Use locking or optimistic concurrency control

Long-Running Transactions

Hold locks too long, blocking other transactions.

Solution: Keep transactions short, do processing outside transaction

---

When Transactions Matter

Critical Operations

• Financial transactions
• Order processing
• Inventory management
• User account operations

Less Critical

• Logging
• Analytics
• Caching
• Non-critical updates

Rule of thumb: Use transactions when data consistency is more important than performance.

---

Interview Questions

1. Beginner Question

Q: What is a database transaction, and why is it important?

A: A transaction is a sequence of database operations executed as a single unit of work. Either all operations succeed (commit) or all fail (rollback).

Why important:

• Data integrity: Ensures database remains in a consistent state
• Atomicity: Prevents partial updates (e.g., money disappearing during transfer)
• Error handling: Automatic rollback on failure

Example:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Both updates succeed, or both are rolled back

Without transactions: If the second UPDATE fails, money disappears from account 1 but doesn't appear in account 2.

2. Intermediate Question

Q: What happens if a database crashes in the middle of a transaction?

A:

On recovery:

1. Committed transactions: Changes are preserved (durability). The database replays the transaction log to restore committed changes.
2. Uncommitted transactions: Changes are rolled back (atomicity). The database uses the transaction log to undo uncommitted operations.

How it works:

• Write-ahead logging (WAL): All changes are written to a log file before commit
• On crash: Database reads the log on startup
• Recovery: Replays committed transactions, rolls back uncommitted ones

Example:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Written to log
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Written to log
  -- CRASH occurs here (before COMMIT)

On recovery:

• Transaction was not committed → Both updates are rolled back
• Account balances remain unchanged
• Database is in consistent state

3. Senior-Level System Question

Q: Design a distributed payment system processing 1M transactions/second. How would you handle transactions across multiple services and databases?

A:

Challenge: Traditional ACID transactions don't scale across distributed systems.

Solution: Saga Pattern (Distributed transactions):

1. Break transaction into steps:

   def transfer_money(from_account, to_account, amount):
       saga = Saga()
       
       # Step 1: Reserve funds
       saga.add_step(
           name="reserve_funds",
           execute=lambda: reserve_funds(from_account, amount),
           compensate=lambda: release_funds(from_account, amount)
       )
       
       # Step 2: Credit destination
       saga.add_step(
           name="credit_account",
           execute=lambda: credit_account(to_account, amount),
           compensate=lambda: debit_account(to_account, amount)
       )
       
       # Step 3: Record transaction
       saga.add_step(
           name="record_transaction",
           execute=lambda: record_transaction(from_account, to_account, amount),
           compensate=lambda: void_transaction(transaction_id)
       )
       
       return saga.execute()
   

2. Saga execution:

   • Execute steps sequentially
   • If any step fails, execute compensating transactions in reverse order
   • Each step is a local transaction (ACID within service)

3. Event-driven approach:

   # Service 1: Account Service
   def debit_account(account_id, amount):
       with transaction():
           account = get_account(account_id)
           if account.balance < amount:
               raise InsufficientFunds()
           account.balance -= amount
           save_account(account)
           publish_event("funds_reserved", {account_id, amount})
   
   # Service 2: Payment Service (listens to event)
   def on_funds_reserved(event):
       with transaction():
           credit_account(event.to_account, event.amount)
           publish_event("payment_completed", {...})
   

4. Two-phase commit (2PC) alternative:

   • Coordinator manages transaction across services
   • Phase 1: Prepare (all services ready to commit)
   • Phase 2: Commit or abort
   • Problem: Blocking, single point of failure

5. Idempotency:

   def transfer_money(transaction_id, from_account, to_account, amount):
       # Check if already processed
       if transaction_exists(transaction_id):
           return get_transaction_result(transaction_id)
       
       # Process transaction
       result = process_transfer(...)
       record_transaction(transaction_id, result)
       return result
   

Trade-offs:

• Saga: More complex, eventual consistency, but scalable
• 2PC: Simpler, strong consistency, but doesn't scale
• Event-driven: Highly scalable, eventual consistency, requires idempotency

Recommendation: Use Saga pattern for distributed transactions. It provides better scalability while maintaining data integrity through compensating transactions.

---

Failure Stories You'll Recognize

The Deadlock That Killed Performance: Two transactions were trying to update the same rows in different orders. Transaction A locked row 1, then tried to lock row 2. Transaction B locked row 2, then tried to lock row 1. Deadlock. The database detected it and killed one transaction. But if many transactions were doing this, deadlocks became frequent. The service became slow and unreliable. The fix? Always acquire locks in the same order, or use optimistic locking.

The Long Transaction That Blocked Everything: A service was processing orders in a transaction. The transaction included network calls to a payment gateway. If the payment gateway was slow (5 seconds), the transaction held locks for 5 seconds. Other transactions trying to access the same rows were blocked. The service became unresponsive. The fix? Keep transactions short—do network calls outside transactions, or use async processing.

The Transaction That Logged Everything: A service was using transactions for logging. Every log entry was wrapped in a transaction. During high traffic, the transaction overhead became a bottleneck. The service became slow. The fix? Don't use transactions for logging—logging doesn't need ACID guarantees. Use eventual consistency instead.

What Interviewers Are Really Testing

They want to hear you talk about when to use transactions, isolation levels, and distributed transaction patterns. Junior engineers say "just use transactions." Senior engineers say "transactions provide ACID guarantees, but they have overhead. Use them for critical operations, keep them short, and understand isolation levels. For distributed systems, use Saga pattern or eventual consistency."

When they ask "How would you design a high-throughput system?", they're testing:

• Do you understand that transactions have overhead?

• Do you know when to relax transaction guarantees for performance?

• Can you design systems that balance consistency and performance?

• Transactions ensure atomicity—all operations succeed or all fail together

• BEGIN starts a transaction, COMMIT saves changes, ROLLBACK undoes changes

• Transactions provide ACID guarantees—Atomicity, Consistency, Isolation, Durability

• Keep transactions short to reduce lock contention and improve concurrency

• Handle errors properly—always rollback on failure to maintain data integrity

• Distributed transactions require different patterns (Saga, 2PC) due to network partitions

• Saga pattern uses compensating transactions for distributed systems

• Idempotency is critical in distributed systems to handle retries safely

• Use transactions for critical operations (financial, orders) but not for everything (logging, analytics)

• Understand isolation levels—they affect how transactions interact with each other

• Monitor for deadlocks and long-running transactions that can impact performance

How InterviewCrafted Will Teach This

We'll teach this through production failures, not definitions. Instead of memorizing "a transaction is a sequence of operations," you'll learn through scenarios like "what happens when 1000 transactions try to update the same row?"

You'll see how transactions affect performance, reliability, and system design. When an interviewer asks "how would you design a payment system?", you'll think about isolation levels, deadlocks, and when to use transactions—not just "use transactions."

• ACID Properties - Understand the guarantees that transactions provide
• Isolation Levels - Learn how transactions interact with each other
• Data Replication - Explore transactions in distributed systems
• Query Optimization - Understand transaction performance implications