Transactions & ACID

🎯 Real-Life Analogy: Bank Transfer

Imagine transferring $100 from your savings account to your checking account:

1. Step 1: Subtract $100 from savings
2. Step 2: Add $100 to checking

What if the computer crashes after Step 1 but before Step 2?

• ❌ Without a transaction: Your $100 disappears! (Savings reduced, but checking not increased)
• ✅ With a transaction: If anything fails, BOTH steps are undone. Your money is safe!

Transactions ensure all-or-nothing execution!

BEGIN TRANSACTION;
    -- SQL operations here
    -- (INSERT, UPDATE, DELETE, etc.)
COMMIT;  -- Save all changes permanently

-- OR

BEGIN TRANSACTION;
    -- SQL operations here
ROLLBACK;  -- Undo all changes

BEGIN TRANSACTION;

-- Deduct $100 from Alice's account
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';

-- Add $100 to Bob's account
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT;  -- Both updates are saved together!

🔍 Key Transaction Concepts:

• BEGIN TRANSACTION: Starts a transaction (also: BEGIN or START TRANSACTION)
• COMMIT: Saves all changes permanently
• ROLLBACK: Undoes all changes, restores to the state before BEGIN
• Atomic: All-or-nothing - either everything happens or nothing happens

🎯 Real-World Scenario: E-Commerce Order

When a customer places an order, multiple things must happen:

BEGIN TRANSACTION;

-- 1. Create the order record
INSERT INTO orders (customer_id, total) VALUES (123, 299.99);

-- 2. Add order items
INSERT INTO order_items (order_id, product_id, quantity) VALUES (5001, 42, 2);

-- 3. Reduce inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 42;

-- 4. Charge the customer
UPDATE customer_balances SET balance = balance - 299.99 WHERE customer_id = 123;

COMMIT;  -- All steps complete successfully!

Without a transaction: What if the inventory update fails but the order was already created? Customer is charged but gets nothing!

With a transaction: If ANY step fails, EVERYTHING is rolled back. The customer isn't charged, inventory isn't changed, and the order doesn't exist. Database stays consistent!

🎯 Real-Life Analogy: Editing a Document

• BEGIN TRANSACTION: Open a Word document and start editing
• Making changes: Type, delete, format - changes are in memory
• COMMIT: Click "Save" - changes written to disk permanently
• ROLLBACK: Click "Close without saving" - all edits discarded

🔍 What Happened:

1. BEGIN TRANSACTION: Started tracking changes
2. UPDATEs: Made changes in a temporary state
3. COMMIT: Wrote all changes to disk permanently
4. Alice's balance: $1000 → $800
5. Bob's balance: $500 → $700
6. Total money in system: $1500 (unchanged, as it should be!)

🔍 When to Use ROLLBACK:

• Error Detection: IF you detect a business rule violation, ROLLBACK
• User Cancellation: User clicks "Cancel" mid-operation
• Validation Failure: Data doesn't pass validation checks
• Dry Run/Testing: Test queries without actually changing data

BEGIN TRANSACTION;

-- Try to withdraw money
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Check if balance went negative (business rule violation!)
-- In real code, you'd check this programmatically

-- If balance < 0:
ROLLBACK;  -- Undo the withdrawal!

-- If balance >= 0:
COMMIT;  -- Allow the withdrawal

🎯 What is ACID?

ACID is an acronym for four critical properties that every reliable database transaction must have:

• A - Atomicity
• C - Consistency
• I - Isolation
• D - Durability

These properties work together to ensure your data stays safe, accurate, and reliable - even in the face of errors, crashes, or concurrent users!

🎯 Real-Life Analogy: Buying a Coffee

When you buy coffee:

1. You pay $5
2. You receive the coffee

What if the power goes out after you pay but before you get your coffee?

• ❌ Non-atomic: You lose $5 and get nothing
• ✅ Atomic: The entire transaction is canceled - you keep your $5, store keeps the coffee

Atomicity ensures you can't have half a transaction!

BEGIN TRANSACTION;

-- Step 1: Deduct from inventory
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;

-- Step 2: Create order
INSERT INTO orders (customer_id, product_id) VALUES (101, 42);

-- Step 3: Charge customer
UPDATE customers SET balance = balance - 99.99 WHERE customer_id = 101;

COMMIT;

🎯 Real-Life Analogy: Chess Game Rules

In chess, certain rules must always be followed:

• Each player has exactly one king
• Pawns can't move backward
• You can't move into check

Consistency means the database enforces rules like constraints, triggers, and business logic - just like a chess app won't let you make illegal moves!

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    balance REAL CHECK(balance >= 0)  -- Constraint: balance can't be negative
);

BEGIN TRANSACTION;

-- Valid transaction (respects constraint)
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
-- If balance was $100, it's now $50 ✅ Still valid (>= 0)

COMMIT;  -- Success!

BEGIN TRANSACTION;

-- Invalid transaction (violates constraint)
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
-- If balance was $50, this would make it -$150 ❌ Violates constraint!

-- Database automatically ROLLBACK this transaction!
-- Consistency preserved!

🔍 Real-World Example: Money Transfer

Business Rule: Total money in the system must stay constant

Before transaction:

• Alice: $1000
• Bob: $500
• Total: $1500

After transaction:

• Alice: $800 (sent $200)
• Bob: $700 (received $200)
• Total: $1500 ✅ (same as before - consistent!)

If only one UPDATE happened, the total would be $1300 or $1700 - violating consistency!

🎯 Real-Life Analogy: ATM Withdrawals

Imagine you and your spouse both try to withdraw money from the same account at the same time:

• Account balance: $500
• You withdraw: $400 at ATM #1
• Spouse withdraws: $300 at ATM #2 (at the same moment!)

Without isolation:

1. Both ATMs read balance: $500
2. ATM #1 deducts $400, writes $100
3. ATM #2 deducts $300, writes $200
4. Final balance: $200 (but you withdrew $700 total - $200 disappeared!)

With isolation:

1. ATM #1 locks the account, reads $500
2. ATM #1 deducts $400, writes $100, unlocks
3. ATM #2 locks the account, reads $100
4. ATM #2 rejects withdrawal (insufficient funds)
5. Final balance: $100 ✅ Correct!

-- Transaction 1 (running concurrently)
BEGIN TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 42;
-- Transaction is active but NOT yet committed...

-- Transaction 2 (running at the same time)
BEGIN TRANSACTION;
SELECT stock FROM products WHERE product_id = 42;
-- What value does it see?

-- With ISOLATION: Sees the OLD value (before Transaction 1's update)
-- Transaction 2 doesn't see uncommitted changes!

-- Transaction 1 completes
COMMIT;  -- Now the change is visible to others

-- Transaction 2 continues
SELECT stock FROM products WHERE product_id = 42;
-- Still sees the OLD value (reads are consistent within a transaction)

COMMIT;

🔍 How Isolation Works (Simplified):

Databases use locks or versioning to provide isolation:

1. Locking:

• When you read/write a row, database puts a "lock" on it
• Other transactions must wait for the lock to be released
• Prevents two transactions from modifying the same data simultaneously

2. Multi-Version Concurrency Control (MVCC):

• Each transaction sees a "snapshot" of the database from when it started
• Reads don't block writes, writes don't block reads
• More efficient but more complex to implement

🎯 Real-Life Analogy: Saving a Document

When you click "Save" in a word processor:

• Before save: Your work is in RAM (volatile memory)
• After save: Your work is on the hard drive (permanent storage)
• Power outage 1 second after save: Your work is still safe! ✅

Durability means COMMIT works like that "Save" button - once it's done, your data is safe from crashes!

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance + 1000 WHERE account_id = 123;

COMMIT;  -- ✅ Data is written to disk!

-- Even if these disasters happen 1 millisecond after COMMIT:
-- ❌ Power failure
-- ❌ System crash
-- ❌ Database process killed

-- When the database restarts:
-- ✅ The $1000 credit is still there!
-- ✅ Transaction is NOT lost!

🔍 How Durability Works:

Databases use several techniques to ensure durability:

1. Write-Ahead Logging (WAL):

• Before making changes, database writes them to a log file on disk
• Log is written sequentially (fast!)
• If crash happens, database replays the log on restart
• Used by SQLite, PostgreSQL, MySQL

2. Force Writes to Disk:

• COMMIT doesn't return until data is physically written to disk
• Uses fsync() system call to force OS to flush buffers
• Ensures data survives power failures

3. Redundancy:

• Write to multiple disks (RAID)
• Replicate to backup servers
• Even if one disk fails, data is safe

BEGIN TRANSACTION;

-- Check if room is available
SELECT available FROM rooms WHERE room_id = 101;
-- Returns: available = 1 (YES)

-- Reserve the room
UPDATE rooms SET available = 0 WHERE room_id = 101;

-- Create booking record
INSERT INTO bookings (customer_id, room_id, check_in, check_out)
VALUES (456, 101, '2024-03-15', '2024-03-20');

-- Charge the customer
UPDATE customers SET balance = balance - 500 WHERE customer_id = 456;

COMMIT;  -- All or nothing!

🔍 Transaction Best Practices:

• Keep transactions short: Long transactions lock resources
• Use transactions for multi-step operations: Anything that must happen together
• Don't mix user input with transactions: Don't wait for user during a transaction
• Handle errors: Always have error handling that calls ROLLBACK
• Test failure scenarios: Make sure ROLLBACK works correctly