Database Topic

Transactions

Master database transactions to ensure data consistency and reliability. Critical for database and system design interviews.

A transaction is a sequence of database operations that are executed as a single unit of work, ensuring all operations succeed or all fail together.

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?

On recovery:

Committed transactions: Changes are preserved (durability). The database replays the transaction log to restore committed changes.
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?

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

Solution: Saga Pattern (Distributed transactions):

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()

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

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", {...})

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

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.

Key Takeaways

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

Keep exploring

Database concepts build on each other. Explore related topics to deepen your understanding of how data systems work.