Database Transaction Management

Table of contents

ACID Implementation Summary

A - Atomicity (All-or-Nothing)

Implementation: Transaction Log / Write-Ahead Log (WAL)

Process:

1. Log “BEGIN TRANSACTION”
2. Before each operation, write old values to log
3. Execute actual operations
4. Log “COMMIT TRANSACTION”
5. On crash, use log to rollback incomplete transactions

Responsible party: ✅ Database itself

• The DB automatically manages transaction logs
• Application just needs to call BEGIN, COMMIT, ROLLBACK

C - Consistency (Valid State)

Implementation: Constraints + Validation

Mechanisms:

• CHECK constraints
• FOREIGN KEY constraints
• UNIQUE constraints
• NOT NULL constraints
• Triggers

Responsible party: ⚠️ Both DB and Application

• ✅ Database: Enforces schema-level constraints (CHECK, FK, UNIQUE, NOT NULL)
• ✅ Application: Enforces business logic rules (e.g., “total money in system must remain constant”, “user can’t book overlapping appointments”)

Example:

-- DB enforces this:
CREATE TABLE accounts (balance DECIMAL CHECK (balance >= 0));

-- Application must enforce this:
-- "Sum of all balances must equal initial total"
-- This requires application-level validation

I - Isolation (Concurrent Transactions Don’t Interfere)

Implementation: Locks + MVCC (Multi-Version Concurrency Control)

Mechanisms:

1. Locking:

• Shared locks (read locks)
• Exclusive locks (write locks)

2. MVCC:

• Each transaction sees a snapshot version of data
• Reads don’t block writes, writes don’t block reads
• Used by PostgreSQL, MySQL InnoDB

Isolation Levels (from weakest to strongest):

1. Read Uncommitted (dirty reads possible)
2. Read Committed (only see committed data)
3. Repeatable Read (consistent reads within transaction)
4. Serializable (fully sequential execution)

Responsible party: ✅ Database itself

• DB automatically manages locks and MVCC
• Application can configure isolation level: SET TRANSACTION ISOLATION LEVEL ...

Example of failed concurrency

Scenario:

Timeline:
T1: Transaction A starts (timestamp: 10:00:00)
T2: Transaction B starts (timestamp: 10:00:01)
T3: Transaction B commits successfully
T4: Transaction A fails and rollback
T5: Transaction A re-submits (NEW transaction, timestamp: 10:00:05)

What Actually Happens:

Key Point: Each re-submission is a BRAND NEW transaction

When Transaction A fails and is re-submitted, it’s NOT the same transaction - it’s a completely new one with a new timestamp.

Original Transaction A (timestamp 10:00:00) → FAILED → ROLLED BACK → GONE
New Transaction A' (timestamp 10:00:05) → This is SEPARATE

Detailed Walkthrough:

Step 1: Both transactions start

-- Transaction A (T1, timestamp 10:00:00)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Gets EXCLUSIVE LOCK on row id=1

-- Transaction B (T2, timestamp 10:00:01)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- BLOCKED! Waiting for A's lock to release

Step 2: Transaction A fails

-- Transaction A
ROLLBACK;  -- Lock is RELEASED, changes are UNDONE

Step 3: Transaction B proceeds

-- Transaction B
-- Lock is now available!
UPDATE accounts SET balance = balance - 50 WHERE id = 1;  -- Executes now
COMMIT;  -- SUCCESS ✅

Step 4: Transaction A re-submits (NEW transaction)

-- Transaction A' (NEW, timestamp 10:00:05)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- This sees the COMMITTED result from Transaction B
-- Works with the NEW balance (after B's changes)
COMMIT;

Result:

The re-submitted transaction A’ works with the database state AFTER Transaction B committed.

There’s NO queue of “original timestamps” - each transaction is independent.

Example with Numbers:

Initial balance: $1000

Timeline:
1. Transaction A starts: tries to deduct $100
   - Gets lock on the row
   
2. Transaction B starts: tries to deduct $50
   - WAITS for A's lock
   
3. Transaction A FAILS and ROLLBACK
   - Lock released
   - Balance still: $1000 (no change)
   
4. Transaction B continues
   - UPDATE balance = 1000 - 50 = $950
   - COMMIT ✅
   - Balance now: $950
   
5. Transaction A re-submits (NEW transaction A')
   - UPDATE balance = 950 - 100 = $850
   - COMMIT ✅
   - Final balance: $850

Final result: $850 (both operations applied, B first then A)

What if we want to preserve original order?

If you need to preserve the original submission order even after failures, you need application-level logic:

Option 1: Retry with application-level queue

python

# Pseudo-code
queue = []

queue.append(("A", timestamp_A))
queue.append(("B", timestamp_B))

# Sort by timestamp
queue.sort(key=lambda x: x[1])

# Execute in order, retry failed ones
for transaction in queue:
    while not execute(transaction):
        # Keep retrying A until success
        time.sleep(1)

Option 2: Optimistic locking with version numbers

-- Add version column
UPDATE accounts 
SET balance = balance - 100,
    version = version + 1
WHERE id = 1 AND version = 5;  -- Only succeeds if version hasn't changed

-- If this returns 0 rows updated → conflict detected → retry

Option 3: Pessimistic locking

-- Transaction A acquires lock first
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- Explicit lock
-- Even if A fails, we can ensure B waits

-- In application code, maintain order

Summary:

Key Insight:

The database doesn’t remember “original submission order” across transaction failures.

• Each BEGIN creates a fresh transaction
• Failed transactions are completely erased (atomicity!)
• If you need ordering guarantees across failures, you must implement it in your application layer

D - Durability (Committed Data Persists)

Implementation: Write-Ahead Logging (WAL) + Persistent Storage

Process:

1. Modify data in memory buffer
2. Write to WAL log (on disk)
3. On COMMIT, flush log to disk (fsync)
4. Background process writes buffer to disk periodically

Why this design?

• WAL is sequential write (fast)
• Data files are random write (slow)
• On crash, replay WAL to recover

Responsible party: ✅ Database itself

• DB automatically manages WAL and checkpoints
• Application just calls COMMIT

Summary Table: Who Implements What?

Key Takeaway:

Database handles: A, I, D automatically - developers just use BEGIN/COMMIT/ROLLBACK

Both DB + App handle: C (Consistency)

• DB enforces schema constraints
• Application enforces complex business rules

One-Sentence Summary:

• Atomicity: WAL for rollback ← DB handles it
• Consistency: Constraints + business logic ← DB + App handle it
• Isolation: Locks + snapshots ← DB handles it
• Durability: WAL written to disk first ← DB handles it

The database does the heavy lifting, but developers must design good schemas and business logic! 🎯