Database Transactions, Concurrency Control, and the ACID Principles

SS3 Term 1 • Week 5

About 45 minutes

Imagine you are using a mobile banking app in Lagos to transfer ₦50,000 to your mother's account in Ibadan. The bank's database executes two separate steps: 1. DEBIT ₦50,000 from Your Account 2. CREDIT ₦50,000 to Your Mother's Account

What happens if the cellular network towers lose power right between Step 1 and Step 2?

If the database operates naively, ₦50,000 has disappeared from your account, but your mother never received it! In commercial banking, aviation reservations, and cryptocurrency exchanges, such catastrophic failures are completely unacceptable.

To guarantee infallible financial and mission-critical reliability, computer scientists formulated the concept of Database Transactions and the ACID Principles.

Learning Outcomes

By the end of this lesson, you will be able to:

Deconstruct the four ACID properties: Atomicity, Consistency, Isolation, and Durability.
Execute transaction state controls: BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.
Analyze concurrency anomalies: Dirty Reads, Lost Updates, and Inconsistent State.
Trace Write-Ahead Logging (WAL) and database crash recovery mechanisms.

Key Vocabulary

Term	Technical Definition
Transaction	A logical unit of database processing that includes one or more database access operations treated as an indivisible action.
ACID	The four standard properties guaranteeing reliable transaction processing: Atomicity, Consistency, Isolation, Durability.
Atomicity	The "all-or-nothing" rule: all operations in the transaction succeed, or the entire transaction is rolled back with zero modifications.
Consistency	Guarantees that a transaction transforms the database from one valid state to another, preserving all integrity constraints.
Isolation	Ensures that concurrent transactions execute independently without interfering with or viewing each other's intermediate state.
Durability	Guarantees that once a transaction commits, its modifications are permanently recorded and survive system crashes.
ROLLBACK	An operation that undoes all modifications performed by the current transaction, returning data to its pre-transaction state.

Deconstructing the ACID Principles

┌────────────────────────────────────────────────────────────────────────┐
│                        THE ACID GUARANTEE                              │
├───────────────┬────────────────────────────────────────────────────────┤
│ A - Atomicity │ All or nothing. No partial updates survive crashes.    │
│ C - Consistency│ Database constraints (PK, FK, CHECK) are never broken. │
│ I - Isolation │ Concurrent users cannot see each other's half-finished │
│               │ work.                                                  │
│ D - Durability│ Committed writes survive complete power loss.          │
└───────────────┴────────────────────────────────────────────────────────┘

The Transaction State Machine:

       ┌───────────┐
       │   Active  │ <── BEGIN TRANSACTION
       └─────┬─────┘
             │
       ┌─────┴─────┐
       │ Partially │
       │ Committed │
       └─────┬─────┘
       ┌─────┴─────┐           ┌──────────┐
       │ Committed │ ────────> │ Terminated│ (Success)
       └───────────┘           └──────────┘
             │                       ▲
             ▼                       │
       ┌───────────┐                 │
       │   Failed  │ ──> ROLLBACK ───┘ (Aborted & Restored)
       └───────────┘

Concurrency Hazards: When Millions Collide

When thousands of customers buy concert tickets or transfer funds simultaneously, databases must manage Concurrency:

Lost Update Problem: Two transactions read balance ₦100,000 simultaneously. Tx1 adds ₦10,000; Tx2 adds ₦20,000. If Tx2 overwrites Tx1's update without knowledge, ₦10,000 is lost!
Dirty Read: Tx1 debits ₦50,000. Tx2 reads the new balance before Tx1 commits. Tx1 then aborts and rolls back! Tx2 based decisions on money that was never transferred.
Non-Repeatable Read: Tx1 reads a student record. Tx2 modifies the record and commits. Tx1 reads the same row again and gets contradictory numbers.

The Solution: Isolation Levels and Locking

To prevent these anomalies, databases use Locks (Shared Read Locks and Exclusive Write Locks) and Multi-Version Concurrency Control (MVCC).

Practical Activity: Simulating a Fault-Tolerant Bank Transfer in Python

Let us write a Python script using SQLite transactions to demonstrate how ROLLBACK protects against power failures and network dropouts.

Step 1: Create `transaction_bank_lab.py`

import sqlite3

# Connect to in-memory database
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# 1. Setup Accounts Table
cursor.execute("""
CREATE TABLE accounts (
    account_no TEXT PRIMARY KEY,
    owner_name TEXT NOT NULL,
    balance REAL NOT NULL CHECK (balance >= 0)
);
""")

# Seed initial balances
cursor.execute("INSERT INTO accounts VALUES ('ACC-01', 'Bayo (Lagos)', 100000.0);")
cursor.execute("INSERT INTO accounts VALUES ('ACC-02', 'Mama (Ibadan)', 15000.0);")
conn.commit()

def print_balances(stage_title):
    print(f"\n--- {stage_title} ---")
    cursor.execute("SELECT account_no, owner_name, balance FROM accounts;")
    for acc in cursor.fetchall():
        print(f"  {acc[0]} ({acc[1]}): ₦{acc[2]:,.2f}")

print_balances("INITIAL BALANCES")

# Scenario: Bayo attempts to transfer ₦30,000, but a mid-way network error occurs!
TRANSFER_AMOUNT = 30000.0

print("\n>>> INITIATING ATOMIC TRANSACTION: Bayo -> Mama (₦30,000)")
try:
    # Begin Transaction
    cursor.execute("BEGIN TRANSACTION;")

    # Step 1: Debit Bayo
    cursor.execute("UPDATE accounts SET balance = balance - ? WHERE account_no = 'ACC-01';", (TRANSFER_AMOUNT,))
    print("Step 1: Debited ₦30,000 from Bayo's account.")

    # SIMULATE SYSTEM CRASH / EXCEPTION BEFORE STEP 2!
    raise ConnectionResetError("CRITICAL: Undersea fiber cable severed / Power cut!")

    # Step 2 (Never reached): Credit Mama
    cursor.execute("UPDATE accounts SET balance = balance + ? WHERE account_no = 'ACC-02';", (TRANSFER_AMOUNT,))
    conn.commit()

except Exception as error:
    print(f"ALERT: Transaction intercepted by error: {error}")
    print("EXECUTING ROLLBACK TO RESTORE ATOMIC INTEGRITY...")
    conn.rollback()

print_balances("POST-FAILURE BALANCES (AFTER ROLLBACK)")
print("\nVERIFICATION: Zero funds were lost! Atomicity guaranteed consistency.")

conn.close()

Step 2: Run the Script

python transaction_bank_lab.py

Expected Results and Model Output

When you run transaction_bank_lab.py, your terminal will display:

--- INITIAL BALANCES ---
  ACC-01 (Bayo (Lagos)): ₦100,000.00
  ACC-02 (Mama (Ibadan)): ₦15,000.00

>>> INITIATING ATOMIC TRANSACTION: Bayo -> Mama (₦30,000)
Step 1: Debited ₦30,000 from Bayo's account.
ALERT: Transaction intercepted by error: CRITICAL: Undersea fiber cable severed / Power cut!
EXECUTING ROLLBACK TO RESTORE ATOMIC INTEGRITY...

--- POST-FAILURE BALANCES (AFTER ROLLBACK) ---
  ACC-01 (Bayo (Lagos)): ₦100,000.00
  ACC-02 (Mama (Ibadan)): ₦15,000.00

VERIFICATION: Zero funds were lost! Atomicity guaranteed consistency.

Common Mistakes to Avoid

Forgetting to Call COMMIT: In Python SQLite, if you execute DML modifications without calling conn.commit(), all changes are discarded when the database connection closes!
Leaving Transactions Open Indefinitely: Open transactions hold locks on database tables. If a transaction stays open, other users trying to write to the table will hang in infinite wait states until timeouts occur.
Assuming Check Constraints Replace Transactions: A table CHECK (balance >= 0) prevents negative balances, but it cannot guarantee that the credit step in another table succeeds. Only atomic transactions provide multi-statement safety.

Notebook Summary

A Transaction is an indivisible unit of database operations.
ACID Guarantees:
Atomicity: All operations succeed or all are rolled back.
Consistency: Maintains all schema rules and constraints.
Isolation: Prevents concurrent transactions from seeing partial data.
Durability: Committed data is safely written to non-volatile disk.
COMMIT saves changes; ROLLBACK undoes incomplete operations.

Optional Extension Challenge

Research Two-Phase Commit (2PC) used in distributed cloud databases (such as Google Cloud Spanner). How do distributed nodes agree to commit across multiple continents using Prepare and Commit phases?