Transactions and ACID Properties

A transaction is a sequence of SQL operations treated as a single unit of work—either all operations succeed (commit) or all fail (rollback). Transactions ensure data integrity in critical operations like transferring money between bank accounts: debit one account, credit another. If the system crashes after the debit but before the credit, the transaction rolls back, and no money is lost. Understanding transactions and the ACID properties (Atomicity, Consistency, Isolation, Durability) is fundamental to building reliable database applications. This lesson covers how to create transactions, handle errors, use savepoints, understand isolation levels, and prevent common concurrency problems like dirty reads and deadlocks.

📚 What You'll Learn

• What transactions are and why they're essential
• The four ACID properties (Atomicity, Consistency, Isolation, Durability)
• How to start, commit, and rollback transactions
• How to use savepoints for partial rollbacks
• Transaction isolation levels and their trade-offs
• Common concurrency problems (dirty reads, lost updates, deadlocks)
• How to handle transaction errors
• Best practices for using transactions
• When to use and when to avoid transactions

1. Understanding Transactions

Without Transactions (Dangerous!)

-- ❌ Transfer $100 from Account A to Account B (NO TRANSACTION)

-- Step 1: Deduct from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- ⚡ SYSTEM CRASH HERE! ⚡

-- Step 2: Add to Account B (NEVER EXECUTES!)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- PROBLEM: $100 disappeared! Account A lost money, Account B didn't receive it.

With Transactions (Safe!)

-- ✅ Transfer $100 with TRANSACTION

START TRANSACTION;

-- Step 1: Deduct from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- ⚡ SYSTEM CRASH HERE? Transaction rolls back! ⚡

-- Step 2: Add to Account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

COMMIT;  -- Both updates succeed together

-- If any step fails, ROLLBACK undoes everything
-- Money is never lost!

2. Transaction Commands

START TRANSACTION

-- Begin a transaction
START TRANSACTION;

-- Alternative syntax (same meaning)
BEGIN;
BEGIN WORK;

COMMIT

-- Save all changes permanently
COMMIT;

-- After COMMIT:
-- - Changes are permanent
-- - Cannot be undone
-- - Visible to other users

ROLLBACK

-- Undo all changes since START TRANSACTION
ROLLBACK;

-- After ROLLBACK:
-- - All changes discarded
-- - Database returns to state before transaction
-- - As if transaction never happened

Complete Example

-- Create new order with items
START TRANSACTION;

-- Insert order
INSERT INTO orders (customer_id, order_date, total)
VALUES (1001, NOW(), 299.99);

-- Get the new order_id
SET @order_id = LAST_INSERT_ID();

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES 
    (@order_id, 501, 2, 149.99),
    (@order_id, 502, 1, 49.99);

-- Update inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 501;
UPDATE products SET stock = stock - 1 WHERE product_id = 502;

-- Everything worked? Save it!
COMMIT;

-- If any step failed, we would ROLLBACK instead

3. ACID Properties

A - Atomicity

-- Example: Transfer money
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

COMMIT;

-- Either BOTH updates happen, or NEITHER happens
-- Cannot have situation where only one update occurs

C - Consistency

-- Example: Constraints enforced
START TRANSACTION;

-- This violates a CHECK constraint (balance cannot be negative)
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A';
-- Assuming account A only has $500

-- Transaction will FAIL and ROLLBACK
-- Database remains consistent (no negative balance)

I - Isolation

-- Transaction 1:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- Not committed yet...

-- Transaction 2 (running at same time):
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 'A';
-- Sees ORIGINAL balance (before Transaction 1's change)
-- Transaction 1's uncommitted change is INVISIBLE

COMMIT;  -- Transaction 2

COMMIT;  -- Transaction 1

-- Isolation prevents Transaction 2 from seeing incomplete work

D - Durability

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1001, 299.99);

COMMIT;  -- Changes written to disk

-- ⚡ POWER FAILURE! ⚡
-- ⚡ SERVER CRASHES! ⚡

-- When server restarts:
-- The inserted order is STILL THERE
-- Commit guarantees durability

4. Savepoints: Partial Rollbacks

Creating and Using Savepoints

START TRANSACTION;

-- Insert order
INSERT INTO orders (customer_id, order_date, total)
VALUES (1001, NOW(), 0);
SET @order_id = LAST_INSERT_ID();

SAVEPOINT after_order;  -- Create savepoint

-- Try to insert first item
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 501, 2, 149.99);

SAVEPOINT after_item1;  -- Another savepoint

-- Try to insert second item (this fails - product doesn't exist)
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 999, 1, 49.99);  -- Error!

-- Roll back just the failed item
ROLLBACK TO after_item1;

-- Order and first item still exist!
-- Insert different item instead
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 502, 1, 49.99);

COMMIT;  -- Commit order with corrected items

Savepoint Commands

-- Create savepoint
SAVEPOINT savepoint_name;

-- Rollback to savepoint (undo to that point)
ROLLBACK TO savepoint_name;

-- Release savepoint (no longer needed)
RELEASE SAVEPOINT savepoint_name;

5. Handling Transaction Errors

Conditional Rollback

-- Check conditions before committing
START TRANSACTION;

-- Deduct from account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- Check if balance went negative
SELECT balance INTO @new_balance 
FROM accounts 
WHERE account_id = 'A';

IF @new_balance < 0 THEN
    -- Insufficient funds, cancel transaction
    ROLLBACK;
    SELECT 'Transaction failed: Insufficient funds' AS message;
ELSE
    -- Sufficient funds, complete transfer
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
    COMMIT;
    SELECT 'Transaction successful' AS message;
END IF;

Application-Level Error Handling (Pseudocode)

// Python example
try:
    conn.execute("START TRANSACTION")
    
    conn.execute("UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'")
    conn.execute("UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'")
    
    conn.execute("COMMIT")
    print("Transfer successful")
    
except Exception as e:
    conn.execute("ROLLBACK")
    print(f"Transfer failed: {e}")

// Any error triggers ROLLBACK
// Success triggers COMMIT

6. Transaction Isolation Levels

Four Isolation Levels

Setting Isolation Level

-- Set for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set for single transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ... queries ...
COMMIT;

-- Check current level
SELECT @@transaction_isolation;

7. Common Concurrency Problems

Dirty Read

-- Transaction 1:
START TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE account_id = 'A';
-- Not committed yet!

-- Transaction 2 (READ UNCOMMITTED):
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 'A';
-- Sees 1000 (uncommitted change!)

-- Transaction 1:
ROLLBACK;  -- Change undone!

-- Transaction 2 read data that was rolled back = DIRTY READ
-- Solution: Use READ COMMITTED or higher

Non-Repeatable Read

-- Transaction 1:
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 'A';
-- Result: 500

-- Transaction 2:
START TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE account_id = 'A';
COMMIT;

-- Transaction 1:
SELECT balance FROM accounts WHERE account_id = 'A';
-- Result: 1000 (different from first read!)

-- Same query, different results = NON-REPEATABLE READ
-- Solution: Use REPEATABLE READ or higher

Phantom Read

-- Transaction 1:
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE customer_id = 1001;
-- Result: 5 orders

-- Transaction 2:
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1001, 99.99);
COMMIT;

-- Transaction 1:
SELECT COUNT(*) FROM orders WHERE customer_id = 1001;
-- Result: 6 orders (new row appeared!)

-- New rows appeared = PHANTOM READ
-- Solution: Use SERIALIZABLE

Lost Update

-- Transaction 1:
START TRANSACTION;
SELECT stock FROM products WHERE product_id = 101;  -- stock = 10
-- Calculate: new_stock = 10 - 2 = 8

-- Transaction 2:
START TRANSACTION;
SELECT stock FROM products WHERE product_id = 101;  -- stock = 10
-- Calculate: new_stock = 10 - 3 = 7

-- Transaction 1:
UPDATE products SET stock = 8 WHERE product_id = 101;
COMMIT;

-- Transaction 2:
UPDATE products SET stock = 7 WHERE product_id = 101;
COMMIT;

-- Final stock: 7 (should be 5!)
-- Transaction 1's update was LOST
-- Solution: Use UPDATE ... SET stock = stock - 2 (atomic operation)

8. Deadlocks

Deadlock Example

-- Transaction 1:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- Locks row A, waiting to lock row B...

-- Transaction 2 (at same time):
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'B';
-- Locks row B, waiting to lock row A...

-- Transaction 1:
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- Waiting for Transaction 2 to release lock on B...

-- Transaction 2:
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'A';
-- Waiting for Transaction 1 to release lock on A...

-- DEADLOCK! Both waiting for each other
-- MySQL detects this and rolls back one transaction
-- Error: "Deadlock found when trying to get lock"

Preventing Deadlocks

9. Transaction Best Practices

When to Use Transactions

✅ Use transactions for:
- Financial operations (transfers, payments)
- Multi-table inserts (order + order items)
- Complex updates that must succeed/fail together
- Inventory management
- Any operation where partial completion is unacceptable

❌ Don't use transactions for:
- Single INSERT/UPDATE/DELETE (already atomic)
- Read-only SELECT queries
- Batch processes where partial success is OK
- Operations with user interaction mid-transaction

10. 🎯 Practice Exercises

accounts (account_id, customer_name, balance)
transactions (transaction_id, from_account, to_account, amount, transaction_date)

-- Money transfer with validation
START TRANSACTION;

-- Check source account balance
SELECT balance INTO @current_balance 
FROM accounts 
WHERE account_id = 'A'
FOR UPDATE;  -- Lock the row

-- Check if sufficient funds
IF @current_balance >= 250 THEN
    -- Deduct from source
    UPDATE accounts 
    SET balance = balance - 250 
    WHERE account_id = 'A';
    
    -- Add to destination
    UPDATE accounts 
    SET balance = balance + 250 
    WHERE account_id = 'B';
    
    -- Log transaction
    INSERT INTO transactions (from_account, to_account, amount, transaction_date)
    VALUES ('A', 'B', 250, NOW());
    
    -- Commit successful transfer
    COMMIT;
    SELECT 'Transfer successful' AS message;
ELSE
    -- Insufficient funds, rollback
    ROLLBACK;
    SELECT 'Transfer failed: Insufficient funds' AS message;
END IF;

orders (order_id, customer_id, order_date, total, status)
order_items (item_id, order_id, product_id, quantity, price)
products (product_id, name, price, stock)

-- Order processing with item-level rollback
START TRANSACTION;

-- Create order
INSERT INTO orders (customer_id, order_date, total, status)
VALUES (1001, NOW(), 0, 'pending');
SET @order_id = LAST_INSERT_ID();

SAVEPOINT order_created;

-- Try to add Item 1: Product 501, Quantity 2
SELECT stock INTO @stock FROM products WHERE product_id = 501;

IF @stock >= 2 THEN
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT @order_id, 501, 2, price FROM products WHERE product_id = 501;
    
    UPDATE products SET stock = stock - 2 WHERE product_id = 501;
    SAVEPOINT item1_added;
    SELECT 'Item 1 added' AS message;
ELSE
    SELECT 'Item 1 out of stock' AS message;
END IF;

-- Try to add Item 2: Product 502, Quantity 5
SELECT stock INTO @stock FROM products WHERE product_id = 502;

IF @stock >= 5 THEN
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT @order_id, 502, 5, price FROM products WHERE product_id = 502;
    
    UPDATE products SET stock = stock - 5 WHERE product_id = 502;
    SAVEPOINT item2_added;
    SELECT 'Item 2 added' AS message;
ELSE
    SELECT 'Item 2 out of stock, skipped' AS message;
END IF;

-- Try to add Item 3: Product 503, Quantity 1
SELECT stock INTO @stock FROM products WHERE product_id = 503;

IF @stock >= 1 THEN
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT @order_id, 503, 1, price FROM products WHERE product_id = 503;
    
    UPDATE products SET stock = stock - 1 WHERE product_id = 503;
    SELECT 'Item 3 added' AS message;
ELSE
    SELECT 'Item 3 out of stock, skipped' AS message;
END IF;

-- Calculate order total
UPDATE orders 
SET total = (
    SELECT SUM(quantity * price) 
    FROM order_items 
    WHERE order_id = @order_id
)
WHERE order_id = @order_id;

-- Check if we have at least one item
SELECT COUNT(*) INTO @item_count FROM order_items WHERE order_id = @order_id;

IF @item_count > 0 THEN
    COMMIT;
    SELECT 'Order created successfully' AS message, @order_id AS order_id;
ELSE
    ROLLBACK;
    SELECT 'Order cancelled: No items available' AS message;
END IF;

products (product_id, name, price, stock, reserved_stock)
orders (order_id, customer_id, order_date, total, status)
order_items (item_id, order_id, product_id, quantity, price)
inventory_log (log_id, product_id, change_amount, reason, log_date)

-- Advanced inventory reservation system
START TRANSACTION;

SET @product_id = 501;
SET @quantity = 3;
SET @customer_id = 1001;

-- Lock product row to prevent concurrent access
SELECT 
    stock,
    reserved_stock,
    stock - reserved_stock AS available_stock
INTO @stock, @reserved, @available
FROM products 
WHERE product_id = @product_id
FOR UPDATE;  -- Critical: Locks the row

-- Check if enough available stock
IF @available >= @quantity THEN
    
    -- Reserve the stock
    UPDATE products 
    SET reserved_stock = reserved_stock + @quantity
    WHERE product_id = @product_id;
    
    -- Log reservation
    INSERT INTO inventory_log (product_id, change_amount, reason, log_date)
    VALUES (@product_id, -@quantity, 'Reserved for order', NOW());
    
    SAVEPOINT stock_reserved;
    
    -- Create order
    INSERT INTO orders (customer_id, order_date, total, status)
    VALUES (@customer_id, NOW(), 0, 'pending');
    
    SET @order_id = LAST_INSERT_ID();
    
    -- Add order item
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT @order_id, @product_id, @quantity, price 
    FROM products 
    WHERE product_id = @product_id;
    
    -- Calculate total
    UPDATE orders 
    SET total = (
        SELECT SUM(quantity * price) 
        FROM order_items 
        WHERE order_id = @order_id
    )
    WHERE order_id = @order_id;
    
    COMMIT;
    
    SELECT 
        'Order created successfully' AS message,
        @order_id AS order_id,
        @quantity AS reserved_quantity,
        @available - @quantity AS remaining_available;
        
ELSE
    -- Insufficient stock
    ROLLBACK;
    
    SELECT 
        'Insufficient stock' AS message,
        @available AS available_stock,
        @quantity AS requested_quantity,
        @quantity - @available AS shortage;
END IF;


-- Separate transaction: Convert reservation to actual sale
START TRANSACTION;

SET @order_id = 1;  -- Order to complete

-- Get order items
SELECT product_id, quantity 
INTO @product_id, @quantity
FROM order_items 
WHERE order_id = @order_id
LIMIT 1;  -- Simplified: handling one item

-- Lock product
SELECT stock, reserved_stock 
INTO @stock, @reserved
FROM products 
WHERE product_id = @product_id
FOR UPDATE;

-- Convert reserved stock to sold
UPDATE products 
SET stock = stock - @quantity,
    reserved_stock = reserved_stock - @quantity
WHERE product_id = @product_id;

-- Log sale
INSERT INTO inventory_log (product_id, change_amount, reason, log_date)
VALUES (@product_id, -@quantity, 'Sold in order ' + @order_id, NOW());

-- Mark order as completed
UPDATE orders 
SET status = 'completed' 
WHERE order_id = @order_id;

COMMIT;

SELECT 'Order completed and stock updated' AS message;

📝 Key Takeaways

• Transactions ensure all-or-nothing execution of multiple operations
• Use START TRANSACTION, COMMIT, and ROLLBACK
• ACID properties: Atomicity, Consistency, Isolation, Durability
• Atomicity: All operations succeed or all fail together
• Consistency: Database remains in valid state
• Isolation: Transactions don't interfere with each other
• Durability: Committed changes survive crashes
• Savepoints allow partial rollbacks within transactions
• Isolation levels trade protection for performance
• READ COMMITTED is typical default (good balance)
• Deadlocks occur when transactions wait for each other
• Prevent deadlocks by accessing resources in same order
• Keep transactions short to minimize locks
• Use FOR UPDATE to explicitly lock rows
• Transactions are essential for data integrity in critical operations