Understanding Database Transactions and ACID: The Foundation of Data Integrity

TL;DR

Database transactions are units of work that must complete entirely or not at all. ACID (Atomicity, Consistency, Isolation, Durability) properties ensure data integrity even during failures, concurrent access, and system crashes. Atomicity means all-or-nothing execution. Consistency maintains database rules. Isolation prevents conflicts between concurrent transactions. Durability guarantees committed data survives crashes. Understanding transactions and ACID is fundamental to building reliable systems that handle money, inventory, and any data where correctness matters more than speed.

ACID Principles:

• Atomicity: All operations succeed or all fail (no partial updates)
• Consistency: Database rules are never violated
• Isolation: Concurrent transactions don’t interfere with each other
• Durability: Committed data survives crashes and power failures

---

What is a Transaction?

A transaction is a sequence of database operations that execute as a single logical unit.

Simple example: Bank transfer

-- Transfer $100 from Alice to Bob
BEGIN TRANSACTION;

  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';

COMMIT;

Without transactions:

-- What if system crashes between these?
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
-- CRASH! Bob never gets the money. Alice lost $100.
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';

Money disappeared. Database is inconsistent.

With transactions:

• Either both updates happen (committed)
• Or neither happens (rolled back)
• Never partial update

---

Why Transactions Matter

Without transactions, these scenarios happen:

Scenario 1: Partial Updates

E-commerce order creation:

INSERT INTO orders (user_id, total) VALUES (123, 99.99);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 456, 2);
UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 456;
-- CRASH HERE
UPDATE accounts SET balance = balance - 99.99 WHERE user_id = 123;

Result:

• Order created
• Inventory decremented
• User never charged
• Lost revenue

Scenario 2: Concurrent Access

Two users booking last seat on flight:

-- User A
SELECT seats_available FROM flights WHERE flight_id = 123;  -- Returns 1
-- User B checks at same time
SELECT seats_available FROM flights WHERE flight_id = 123;  -- Returns 1
-- Both see 1 seat available

-- User A books
UPDATE flights SET seats_available = 0 WHERE flight_id = 123;
INSERT INTO bookings (flight_id, user_id) VALUES (123, 'userA');

-- User B books
UPDATE flights SET seats_available = -1 WHERE flight_id = 123;  -- NEGATIVE SEATS!
INSERT INTO bookings (flight_id, user_id) VALUES (123, 'userB');

Result: Double-booked flight. Impossible state.

Scenario 3: Read During Write

Viewing account balance during transfer:

-- Transfer in progress
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
-- Another query reads here
SELECT SUM(balance) FROM accounts;  -- Reads partial state!
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';

Result: Report shows incorrect total. $100 temporarily missing.

Transactions solve all of these.

---

The ACID Properties

ACID is the guarantee that transactions provide.

A - Atomicity

All or nothing. Transaction either completes fully or has no effect.

Example: Creating user account

BEGIN TRANSACTION;

  -- Create user record
  INSERT INTO users (email, name) VALUES ('john@example.com', 'John Doe');

  -- Create default preferences
  INSERT INTO preferences (user_id, theme) VALUES (LASTVAL(), 'dark');

  -- Send welcome email (external service call)
  SELECT send_email('john@example.com', 'Welcome!');

  -- If any step fails, all are rolled back
COMMIT;

If sending email fails:

ROLLBACK;  -- Undo user creation and preferences

Guarantees:

• Either user + preferences + email sent
• Or none of it (clean rollback)
• Never partial state (user without preferences)

Implementation: Databases use write-ahead logging (WAL):

1. Write operations to log
2. Apply operations to database
3. If crash before commit: Undo from log
4. If crash after commit: Redo from log

C - Consistency

Database rules are never violated. Constraints always hold.

Example: Inventory constraints

CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  quantity INT CHECK (quantity >= 0)  -- Never negative
);

BEGIN TRANSACTION;
  UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 123;
  -- If this makes quantity negative, transaction FAILS
COMMIT;

Types of consistency rules:

1. Domain constraints

age INT CHECK (age >= 0 AND age <= 150)
email TEXT NOT NULL UNIQUE
status TEXT CHECK (status IN ('active', 'inactive', 'suspended'))

2. Referential integrity

FOREIGN KEY (user_id) REFERENCES users(id)
-- Can't create order for non-existent user

3. Business rules

-- Total must equal sum of line items
CREATE TRIGGER check_order_total
AFTER INSERT OR UPDATE ON order_items
FOR EACH ROW
EXECUTE FUNCTION validate_order_total();

4. Application-level consistency

BEGIN TRANSACTION;
  -- Total in accounts must always equal sum of balances
  SELECT SUM(balance) FROM accounts;  -- Must equal $1,000,000
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';
  -- Sum still $1,000,000
COMMIT;

If consistency would be violated, transaction fails:

BEGIN TRANSACTION;
  UPDATE inventory SET quantity = -5 WHERE product_id = 123;
  -- ERROR: CHECK constraint violated
ROLLBACK;  -- Database unchanged

I - Isolation

Concurrent transactions don’t interfere. Each sees consistent snapshot of data.

Problem without isolation:

-- Transaction 1: Transfer $100
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';  -- Alice: $900
  -- Transaction 2 reads here: Sees Alice with $900, Bob with $100
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';    -- Bob: $200
COMMIT;

-- Transaction 2: Calculate total balance
BEGIN;
  SELECT balance FROM accounts WHERE user_id = 'alice';  -- $900
  SELECT balance FROM accounts WHERE user_id = 'bob';    -- $100 (should be $200)
  -- Sees inconsistent state! Total is $1000 instead of $1100
COMMIT;

With isolation:

-- Transaction 2 either:
-- A) Sees state BEFORE Transaction 1 (Alice: $1000, Bob: $100)
-- B) Sees state AFTER Transaction 1 (Alice: $900, Bob: $200)
-- Never partial state

Isolation levels (SQL standard):

Read Uncommitted (lowest isolation)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Can read uncommitted changes from other transactions
-- Dirty reads possible

Read Committed (default in PostgreSQL, MySQL)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Only see committed changes
-- Each query sees latest committed data

Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Queries within transaction see consistent snapshot
-- Same query returns same results

Serializable (highest isolation)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transactions execute as if serial (one at a time)
-- No concurrency anomalies

Trade-off: Higher isolation = more consistency, less concurrency.

D - Durability

Committed data survives crashes. Once committed, data is permanent.

Example:

BEGIN TRANSACTION;
  INSERT INTO orders (user_id, total) VALUES (123, 99.99);
COMMIT;  -- Committed!

-- Power failure here

-- After restart, order is still in database
SELECT * FROM orders WHERE user_id = 123;  -- Returns the order

Implementation:

1. Write-Ahead Logging (WAL)

Before modifying data:
1. Write change to log on disk
2. Flush log to disk (fsync)
3. Acknowledge commit to client
4. Apply change to data files (can happen later)

Crash recovery:

On startup:
1. Read WAL
2. Replay uncommitted transactions
3. Undo incomplete transactions
4. Database is consistent

2. Synchronous commits

SET synchronous_commit = on;  -- Default
-- COMMIT doesn't return until data on disk

Trade-off: Durability vs performance

SET synchronous_commit = off;
-- Faster commits, but last few seconds might be lost on crash

3. Replication

Primary writes to WAL
Replica applies WAL
Commit after both acknowledge
-- Data on multiple servers (more durable)

---

Transaction Isolation Levels Deep Dive

Isolation levels prevent concurrency problems.

Concurrency Problems

1. Dirty Read Read uncommitted data that might be rolled back.

-- Transaction 1
BEGIN;
  UPDATE accounts SET balance = 1000000 WHERE user_id = 'alice';
  -- Transaction 2 reads here
  ROLLBACK;  -- Oops, mistake

-- Transaction 2
BEGIN;
  SELECT balance FROM accounts WHERE user_id = 'alice';  -- Sees 1000000
  -- But that was rolled back! Data never existed.
COMMIT;

Prevented by: Read Committed, Repeatable Read, Serializable

2. Non-repeatable Read Same query returns different results within transaction.

-- Transaction 1
BEGIN;
  SELECT balance FROM accounts WHERE user_id = 'alice';  -- Returns $1000
  -- Transaction 2 commits update here
  SELECT balance FROM accounts WHERE user_id = 'alice';  -- Returns $900 (changed!)
COMMIT;

Prevented by: Repeatable Read, Serializable

3. Phantom Read Query returns different rows within transaction.

-- Transaction 1
BEGIN;
  SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 5
  -- Transaction 2 inserts new pending order
  SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 6 (phantom!)
COMMIT;

Prevented by: Serializable

4. Write Skew Concurrent transactions read same data, make different decisions, create invalid state.

-- Business rule: At least 1 doctor on call

-- Transaction 1 (Dr. Alice going off call)
BEGIN;
  SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- Returns 2 (Alice, Bob)
  -- 2 doctors, safe to go off call
  UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Transaction 2 (Dr. Bob going off call, runs concurrently)
BEGIN;
  SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- Returns 2 (Alice, Bob)
  -- 2 doctors, safe to go off call
  UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;

-- Result: 0 doctors on call! Rule violated.

Prevented by: Serializable

Isolation Level Comparison

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read	Write Skew	Performance
Read Uncommitted	❌ Possible	❌ Possible	❌ Possible	❌ Possible	Fastest
Read Committed	✅ Prevented	❌ Possible	❌ Possible	❌ Possible	Fast
Repeatable Read	✅ Prevented	✅ Prevented	❌ Possible	❌ Possible	Slower
Serializable	✅ Prevented	✅ Prevented	✅ Prevented	✅ Prevented	Slowest

Choose based on requirements:

• Read Uncommitted: Never use (unless you don’t care about correctness)
• Read Committed: Good default (most databases)
• Repeatable Read: When queries must see consistent snapshot
• Serializable: When correctness is critical (financial, inventory)

---

Practical Transaction Patterns

Pattern 1: Explicit Transaction Control

BEGIN TRANSACTION;

  -- Multiple operations
  INSERT INTO orders (user_id, total) VALUES (123, 99.99);
  INSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 456);
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456;

COMMIT;  -- Or ROLLBACK on error

Application code (Node.js/PostgreSQL):

async function createOrder(userId, items) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Create order
    const orderResult = await client.query(
      'INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id',
      [userId, calculateTotal(items)]
    );
    const orderId = orderResult.rows[0].id;

    // Add items
    for (const item of items) {
      await client.query(
        'INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)',
        [orderId, item.productId, item.quantity]
      );

      // Update inventory
      await client.query(
        'UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2',
        [item.quantity, item.productId]
      );
    }

    await client.query('COMMIT');
    return orderId;

  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Pattern 2: Savepoints (Partial Rollback)

BEGIN;

  INSERT INTO users (email, name) VALUES ('john@example.com', 'John Doe');

  SAVEPOINT after_user_insert;

  INSERT INTO preferences (user_id, theme) VALUES (LASTVAL(), 'dark');

  -- Error in preferences
  ROLLBACK TO SAVEPOINT after_user_insert;  -- Keep user, undo preferences

  -- Retry with default
  INSERT INTO preferences (user_id, theme) VALUES (LASTVAL(), 'light');

COMMIT;

Pattern 3: Optimistic Locking

Prevent concurrent updates with version number.

-- Table with version column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  price DECIMAL,
  version INT DEFAULT 0
);

-- Update with version check
UPDATE products
SET price = 99.99, version = version + 1
WHERE id = 123 AND version = 5;  -- Only update if version matches

-- Check rows affected
-- If 0, someone else updated (version changed)
-- Retry or fail

Application code:

async function updateProductPrice(productId, newPrice) {
  // Get current version
  const product = await db.query(
    'SELECT version FROM products WHERE id = $1',
    [productId]
  );

  // Update with version check
  const result = await db.query(
    'UPDATE products SET price = $1, version = version + 1 WHERE id = $2 AND version = $3',
    [newPrice, productId, product.version]
  );

  if (result.rowCount === 0) {
    throw new Error('Product was modified by another transaction. Please retry.');
  }
}

Pattern 4: Pessimistic Locking

Lock rows to prevent concurrent access.

BEGIN;

  -- Lock row for update
  SELECT * FROM accounts WHERE user_id = 'alice' FOR UPDATE;

  -- No other transaction can modify this row until we commit
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';

COMMIT;

Lock modes:

FOR UPDATE           -- Exclusive lock (write)
FOR SHARE            -- Shared lock (read)
FOR UPDATE NOWAIT    -- Fail immediately if locked
FOR UPDATE SKIP LOCKED  -- Skip locked rows

Example: Job queue

-- Get next job without conflicts
BEGIN;

  SELECT * FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED;  -- Skip jobs locked by other workers

  UPDATE jobs SET status = 'processing', worker_id = 'worker-1'
  WHERE job_id = 123;

COMMIT;

Pattern 5: Idempotent Operations

Design operations that can safely retry.

-- Bad: Not idempotent
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'alice';
-- If retried, adds $100 again

-- Good: Idempotent
INSERT INTO transactions (id, user_id, amount)
VALUES ('txn-abc-123', 'alice', 100)
ON CONFLICT (id) DO NOTHING;  -- If already processed, skip

UPDATE accounts SET balance = balance + 100
WHERE user_id = 'alice'
AND NOT EXISTS (SELECT 1 FROM transactions WHERE id = 'txn-abc-123');

---

Transaction Performance

Long-Running Transactions

Problem:

BEGIN;
  -- Long-running operation
  SELECT pg_sleep(600);  -- 10 minutes
  UPDATE accounts SET balance = balance - 1 WHERE user_id = 'alice';
COMMIT;

Impact:

• Holds locks for 10 minutes
• Blocks other transactions
• Increases lock contention
• Can cause deadlocks

Solution:

// Keep transactions short
await db.query('BEGIN');
await db.query('UPDATE accounts SET balance = balance - 1 WHERE user_id = $1', ['alice']);
await db.query('COMMIT');

// Do long operations outside transaction
await sendEmail('alice@example.com', 'Payment processed');  // After commit

Deadlocks

Problem:

-- Transaction 1
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
  -- Transaction 2 locks bob here
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';  -- Waits
COMMIT;

-- Transaction 2 (concurrent)
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE user_id = 'bob';
  -- Transaction 1 locks alice here
  UPDATE accounts SET balance = balance + 50 WHERE user_id = 'alice';  -- Waits
COMMIT;

-- DEADLOCK! Both waiting for each other.

Database detects and aborts one transaction:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        Process 5678 waits for ShareLock on transaction 1234.

Solution: Consistent ordering

-- Always lock accounts in same order (alphabetically, by ID, etc.)
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';  -- Always first
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 'bob';    -- Always second
COMMIT;

Connection Pooling

Problem: One connection per transaction

// Bad: Creates new connection for each transaction
async function transfer() {
  const connection = await createConnection();
  await connection.query('BEGIN');
  await connection.query('UPDATE ...');
  await connection.query('COMMIT');
  await connection.close();  // Expensive
}

Solution: Connection pool

const pool = new Pool({ max: 20 });  // Pool of connections

async function transfer() {
  const client = await pool.connect();  // Reuse connection
  try {
    await client.query('BEGIN');
    await client.query('UPDATE ...');
    await client.query('COMMIT');
  } finally {
    client.release();  // Return to pool
  }
}

---

Real-World Example: E-commerce Checkout

Requirements:

• Create order
• Charge payment
• Update inventory
• Send confirmation email
• All-or-nothing (don’t charge without creating order)

Implementation:

async function checkout(userId, items, paymentMethod) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

    // 1. Validate inventory
    for (const item of items) {
      const result = await client.query(
        'SELECT quantity FROM inventory WHERE product_id = $1 FOR UPDATE',
        [item.productId]
      );

      if (result.rows[0].quantity < item.quantity) {
        throw new Error(`Insufficient inventory for product ${item.productId}`);
      }
    }

    // 2. Create order
    const orderResult = await client.query(
      'INSERT INTO orders (user_id, total, status) VALUES ($1, $2, $3) RETURNING id',
      [userId, calculateTotal(items), 'pending']
    );
    const orderId = orderResult.rows[0].id;

    // 3. Add order items
    for (const item of items) {
      await client.query(
        'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ($1, $2, $3, $4)',
        [orderId, item.productId, item.quantity, item.price]
      );
    }

    // 4. Update inventory
    for (const item of items) {
      await client.query(
        'UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2',
        [item.quantity, item.productId]
      );
    }

    // 5. Charge payment (external service - idempotent)
    const paymentId = await chargePayment(paymentMethod, calculateTotal(items), orderId);

    // 6. Update order with payment
    await client.query(
      'UPDATE orders SET payment_id = $1, status = $2 WHERE id = $3',
      [paymentId, 'completed', orderId]
    );

    await client.query('COMMIT');

    // 7. Send email (after commit, outside transaction)
    await sendConfirmationEmail(userId, orderId);

    return orderId;

  } catch (error) {
    await client.query('ROLLBACK');
    // Refund payment if it succeeded
    if (error.paymentSucceeded) {
      await refundPayment(error.paymentId);
    }
    throw error;
  } finally {
    client.release();
  }
}

Key points:

• Serializable isolation (prevent inventory oversell)
• Lock inventory rows (pessimistic locking)
• Idempotent payment (can retry safely)
• Email outside transaction (don’t wait on external service)
• Compensating action (refund on failure)

---

Common Mistakes

1. Auto-commit Mode

Problem:

// Each query is separate transaction
await db.query('INSERT INTO orders ...');  // Transaction 1
await db.query('INSERT INTO order_items ...');  // Transaction 2
// If second fails, first is already committed

Solution:

await db.query('BEGIN');
await db.query('INSERT INTO orders ...');
await db.query('INSERT INTO order_items ...');
await db.query('COMMIT');

2. Transactions Around External Services

Problem:

BEGIN;
  UPDATE inventory SET quantity = quantity - 1;
  -- Call external payment API (takes 5 seconds)
  UPDATE orders SET status = 'paid';
COMMIT;

Transaction holds locks while waiting on external service.

Solution:

-- Store intent in database
BEGIN;
  UPDATE inventory SET quantity = quantity - 1;
  INSERT INTO payment_intents (order_id, amount) VALUES (123, 99.99);
COMMIT;

-- Process payment outside transaction
const result = await paymentAPI.charge(...);

-- Update status in new transaction
BEGIN;
  UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;

3. Not Handling Rollback

Problem:

await db.query('BEGIN');
await db.query('INSERT ...');
await somethingThatMightFail();  // Throws error
await db.query('COMMIT');  // Never reached
// Connection stuck in transaction

Solution:

try {
  await db.query('BEGIN');
  await db.query('INSERT ...');
  await somethingThatMightFail();
  await db.query('COMMIT');
} catch (error) {
  await db.query('ROLLBACK');
  throw error;
}

4. Wrong Isolation Level

Problem:

-- Using default Read Committed for financial transaction
BEGIN;
  SELECT balance FROM accounts WHERE user_id = 'alice';  -- $1000
  -- Another transaction updates balance here
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
  -- Might allow overdraft!
COMMIT;

Solution:

BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT balance FROM accounts WHERE user_id = 'alice' FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 'alice';
COMMIT;

---

Monitoring Transactions

Key Metrics

Active transactions:

SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active';

Long-running transactions:

SELECT pid, usename, state, query_start, NOW() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

Blocked queries:

SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query,
       blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Deadlocks:

SELECT deadlocks FROM pg_stat_database WHERE datname = 'mydb';

---

Conclusion

Transactions and ACID are the foundation of data integrity.

Key takeaways:

Atomicity: All or nothing. No partial updates.

Consistency: Database rules always hold. No invalid states.

Isolation: Concurrent transactions don’t interfere. Choose the right level.

Durability: Committed data survives crashes. Write-ahead logging.

Best practices:

• Use transactions for related operations
• Keep transactions short
• Choose appropriate isolation level
• Handle rollback properly
• Avoid long operations in transactions
• Lock consistently to prevent deadlocks
• Monitor transaction metrics

When to use transactions:

• Financial operations (payments, transfers)
• Inventory management
• Multi-step operations that must succeed together
• Concurrent access to shared data

Trade-offs:

• Consistency vs performance (isolation levels)
• Durability vs speed (synchronous commits)
• Simplicity vs concurrency (lock granularity)

The right approach: Understand ACID. Use transactions when data integrity matters. Choose isolation levels based on requirements. Monitor and optimize for your workload.

Build systems that are correct first, fast second.

---

Resources:

• PostgreSQL Transaction Isolation
• MySQL InnoDB Transactions
• Designing Data-Intensive Applications (Chapter 7: Transactions)

“Correctness first. Performance second. Transactions make correctness possible.”