Engineering Fluency OS

Why This Matters

Imagine transferring $500 from your checking account to your savings account. The database must subtract $500 from checking AND add $500 to savings. What if the system crashes right after the subtraction but before the addition? You just lost $500 into thin air. A transaction prevents this by grouping both operations into a single all-or-nothing unit.

ACID properties are the guarantees that make databases trustworthy. Every banking system, payment processor, and booking platform relies on transactions to ensure that data never ends up in a half-finished, inconsistent state. Without ACID, your data is one power outage away from corruption.

Define Terms

Visual Model

T1: Transfer

BEGIN

Debit -$500checking

Credit +$500savings

COMMIT

T2: Deposit

BEGIN

Read balancechecking

Credit +$200checking

COMMIT

Timeflows downward

The full process at a glance. Click Start tour to walk through each step.

Two concurrent transactions each follow BEGIN, operations, then COMMIT. ACID guarantees keep data consistent.

Code Example

Code

-- SQL: Bank transfer as a transaction
BEGIN TRANSACTION;

-- Step 1: Debit the source account
UPDATE accounts
SET balance = balance - 500
WHERE id = 1 AND balance >= 500;

-- Step 2: Credit the destination account
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

-- Step 3: Verify both updates affected exactly 1 row
-- (In real code, your app checks the row counts here)

-- If everything is OK:
COMMIT;

-- If something went wrong:
-- ROLLBACK;

-- Example: ROLLBACK on error
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- Oops: account 999 does not exist
UPDATE accounts SET balance = balance + 1000 WHERE id = 999;
-- No rows affected! We should rollback
ROLLBACK;
-- The first UPDATE is also undone. Balance is restored.

-- ACID in action: try violating a constraint
BEGIN TRANSACTION;
UPDATE accounts SET balance = -100 WHERE id = 1;
-- If there is a CHECK (balance >= 0) constraint:
-- ERROR: CHECK constraint failed
-- Transaction is automatically rolled back

Interactive Experiment

Try these exercises:

Create an accounts table with a balance column. Start a transaction, debit one account, and then ROLLBACK. Verify the balance is unchanged.
Write a transfer that intentionally fails midway (e.g., update a non-existent account). Confirm both accounts are unaffected.
Add a CHECK (balance >= 0) constraint. Try to overdraw an account inside a transaction. What happens?
Open two separate database connections and observe how one transaction's uncommitted changes are invisible to the other (isolation).

Quick Quiz

Coding Challenge

Implement a Transaction Simulator

Write a class called `TransactionLog` that simulates ACID transactions on an in-memory key-value store. It should support `begin()`, `set(key, value)`, `get(key)`, `commit()`, and `rollback()`. Changes made after begin() should only become permanent after commit(). Rollback should discard all changes since the last begin().

Loading editor...

Real-World Usage

Transactions protect data integrity across all industries:

Banking: Every transfer, deposit, and withdrawal is a transaction. Without atomicity, money could appear or disappear.
E-commerce: Placing an order involves creating an order record, updating inventory, and charging the payment -- all in one transaction.
Booking systems: Reserving a hotel room checks availability AND creates the booking atomically. Two users cannot book the same room.
User registration: Creating a user account, their profile, and their default settings must all succeed together or not at all.
Migration scripts: Schema changes (adding columns, renaming tables) are wrapped in transactions so a failed migration does not leave the schema half-changed.

Transactions & ACID