Transactions and ACID | Computer Science Fundamentals

Learning Objectives

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

Understand what database transactions are and why they're essential
Explain the ACID properties (Atomicity, Consistency, Isolation, Durability)
Use transactions in SQL (BEGIN, COMMIT, ROLLBACK)
Understand isolation levels and their trade-offs
Recognize concurrency problems (dirty reads, lost updates, phantoms)
Explain locking mechanisms (shared, exclusive, row-level, table-level)
Understand Multi-Version Concurrency Control (MVCC)
Handle deadlocks and implement recovery strategies
Work with distributed transactions and the two-phase commit protocol
Apply transaction best practices in real applications

Introduction

Imagine transferring $100 from your checking account to your savings account. This operation requires two steps:

Deduct $100 from checking
Add $100 to savings

What if the system crashes after step 1 but before step 2? You'd lose $100!

Transactions solve this problem by treating multiple operations as a single, indivisible unit of work. Either all operations succeed (commit) or all fail (rollback), ensuring data integrity even in the face of failures.

What is a Transaction?

A transaction is a sequence of database operations that are treated as a single logical unit of work.

-- Without transaction (DANGEROUS)
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
-- System crashes here - money disappears!
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- With transaction (SAFE)
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;
-- If any operation fails, all changes are rolled back

Transaction States

         BEGIN
           ↓
    [Active] ─────→ [Failed]
       ↓               ↓
    [Partially     [Aborted]
     Committed]
       ↓
    [Committed]

Active: Transaction is executing
Partially Committed: Final statement executed, but not yet committed
Committed: Transaction completed successfully, changes permanent
Failed: Error occurred, cannot proceed
Aborted: Transaction rolled back, database restored to state before transaction

ACID Properties

ACID defines the key properties that guarantee reliable transaction processing.

Atomicity

Atomicity: All operations in a transaction succeed or all fail - no partial completion.

-- Banking example
BEGIN TRANSACTION;
    -- Deduct from checking
    UPDATE Accounts SET Balance = Balance - 500
    WHERE AccountID = 123;

    -- Add to savings
    UPDATE Accounts SET Balance = Balance + 500
    WHERE AccountID = 456;

    -- Check for overdraft
    IF (SELECT Balance FROM Accounts WHERE AccountID = 123) < 0 THEN
        ROLLBACK;  -- Undo all changes
    ELSE
        COMMIT;    -- Make all changes permanent
    END IF;

Implementation: Using a transaction log (write-ahead log):

Record all operations in log before applying
If crash occurs, replay or undo from log
Ensures atomicity even with system failures

Consistency

Consistency: Transaction brings database from one valid state to another valid state, preserving all constraints and rules.

-- Constraint: Total money in system must remain constant
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2) CHECK (Balance >= 0)  -- Constraint
);

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

    -- Before commit, database checks all constraints
    -- If Balance would become negative, transaction is rejected
COMMIT;

Examples of consistency rules:

Foreign key constraints must be satisfied
Check constraints must be valid
Unique constraints must hold
Custom business rules (e.g., "account balance cannot be negative")

Isolation

Isolation: Concurrent transactions execute as if they were run sequentially, without interfering with each other.

-- Transaction A
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Reads $1000
    -- ... processing ...
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
COMMIT;

-- Transaction B (running concurrently)
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Should see $1000 or $900?
    UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 1;
COMMIT;

Isolation ensures predictable behavior. We'll explore isolation levels in detail later.

Durability

Durability: Once a transaction commits, its changes are permanent, even if the system crashes immediately after.

BEGIN TRANSACTION;
    INSERT INTO Orders VALUES (1001, 100, '2024-03-15', 599.99);
COMMIT;  -- Once this returns, the order is permanently saved

-- System crashes here
-- After restart, Order 1001 still exists

Implementation:

Write-ahead logging (WAL)
Transaction log persisted to disk before COMMIT returns
Periodic checkpoints save database state
Recovery process replays committed transactions from log

Using Transactions in SQL

Basic Transaction Commands

-- Start a transaction
BEGIN TRANSACTION;  -- or START TRANSACTION; or BEGIN;

-- Execute operations
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- Commit (make changes permanent)
COMMIT;

-- Or rollback (undo all changes)
ROLLBACK;

Savepoints

Savepoints allow partial rollback within a transaction.

BEGIN TRANSACTION;

    INSERT INTO Orders (OrderID, CustomerID, OrderDate)
    VALUES (1001, 100, '2024-03-15');

    SAVEPOINT after_order;

    INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)
    VALUES (1001, 201, 2, 29.99);

    INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)
    VALUES (1001, 202, 1, 49.99);

    -- Error in last item - rollback to savepoint
    ROLLBACK TO SAVEPOINT after_order;

    -- Order exists, but no items
    -- Can now insert correct items
    INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)
    VALUES (1001, 203, 1, 39.99);

COMMIT;

Autocommit Mode

Most databases default to autocommit mode - each statement is a separate transaction.

-- MySQL: Disable autocommit
SET autocommit = 0;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;  -- Now required

-- Re-enable autocommit
SET autocommit = 1;

-- PostgreSQL: Transactions must be explicit
BEGIN;
    -- operations
COMMIT;

Practical Example: Order Processing

BEGIN TRANSACTION;

    -- Create order
    INSERT INTO Orders (CustomerID, OrderDate, Status, TotalAmount)
    VALUES (100, CURRENT_DATE, 'Processing', 0);

    SET @OrderID = LAST_INSERT_ID();

    -- Add order items
    INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@OrderID, 201, 2, 29.99);

    INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@OrderID, 202, 1, 49.99);

    -- Update inventory
    UPDATE Products SET StockQuantity = StockQuantity - 2
    WHERE ProductID = 201;

    UPDATE Products SET StockQuantity = StockQuantity - 1
    WHERE ProductID = 202;

    -- Check if we have enough stock
    IF EXISTS (SELECT 1 FROM Products WHERE ProductID IN (201, 202) AND StockQuantity < 0) THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
    ELSE
        -- Update order total
        UPDATE Orders SET TotalAmount = (
            SELECT SUM(Quantity * UnitPrice)
            FROM OrderItems
            WHERE OrderID = @OrderID
        )
        WHERE OrderID = @OrderID;

        COMMIT;
    END IF;

Concurrency Problems

When multiple transactions run concurrently without proper isolation, several problems can occur.

1. Dirty Read

Reading uncommitted changes from another transaction.

-- Transaction A
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 1;
    -- Not committed yet

-- Transaction B (concurrent)
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;
    -- Sees 1000 added (dirty read)
COMMIT;

-- Transaction A rolls back
ROLLBACK;  -- Transaction B read data that never existed!

Problem: Transaction B made decisions based on data that was rolled back.

2. Non-Repeatable Read

Reading the same row twice in a transaction gives different results.

-- Transaction A
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Reads $1000

    -- ... some processing ...

    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Reads $1500 (!= $1000)
COMMIT;

-- Transaction B (runs between A's two SELECTs)
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 1;
COMMIT;

Problem: Transaction A's two identical queries returned different results.

3. Phantom Read

New rows appear between queries in the same transaction.

-- Transaction A
BEGIN TRANSACTION;
    SELECT COUNT(*) FROM Orders WHERE Status = 'Pending';  -- Returns 5

    -- ... some processing ...

    SELECT COUNT(*) FROM Orders WHERE Status = 'Pending';  -- Returns 6 (phantom!)
COMMIT;

-- Transaction B (runs between A's two SELECTs)
BEGIN TRANSACTION;
    INSERT INTO Orders (Status) VALUES ('Pending');
COMMIT;

Problem: A new row "appeared" (phantom) during Transaction A.

4. Lost Update

Two transactions read the same value, both modify it, and the second commit overwrites the first.

-- Initial: Balance = $1000

-- Transaction A
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Reads $1000
    -- Calculate new balance: $1000 - $100 = $900
    UPDATE Accounts SET Balance = 900 WHERE AccountID = 1;
COMMIT;

-- Transaction B (overlapping with A)
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Reads $1000
    -- Calculate new balance: $1000 + $200 = $1200
    UPDATE Accounts SET Balance = 1200 WHERE AccountID = 1;
COMMIT;

-- Final balance: $1200 (should be $1100!)
-- Transaction A's update was lost

Problem: Transaction A's changes were overwritten by Transaction B.

Isolation Levels

SQL defines four isolation levels that balance concurrency vs. consistency.

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

READ UNCOMMITTED

Lowest isolation: Can read uncommitted changes from other transactions.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;
    -- Can see uncommitted changes from other transactions
    SELECT * FROM Accounts;
COMMIT;

Use case: Rarely used. Acceptable for approximate analytics where dirty reads are tolerable.

Problems: Dirty reads, non-repeatable reads, phantom reads all possible.

READ COMMITTED

Default in many databases: Can only read committed data.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Sees only committed data
    -- Other transactions can modify this row
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Might see different value
COMMIT;

Use case: General-purpose transactions where exact repeatability isn't critical.

Problems: Non-repeatable reads and phantom reads possible.

REPEATABLE READ

Higher isolation: Once you read a row, it won't change during your transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Reads $1000
    -- Other transactions CANNOT modify this row
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Still reads $1000
COMMIT;

Use case: When you need consistent reads of existing rows (e.g., financial reports).

Problems: Phantom reads still possible (new rows can be inserted).

Note: In MySQL InnoDB, REPEATABLE READ also prevents phantom reads using gap locking.

SERIALIZABLE

Highest isolation: Transactions execute as if they were serialized (one after another).

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
    SELECT * FROM Orders WHERE Status = 'Pending';
    -- Other transactions CANNOT insert, update, or delete matching rows
    SELECT * FROM Orders WHERE Status = 'Pending';  -- Identical result
COMMIT;

Use case: Critical operations requiring complete isolation (e.g., financial audits).

Problems: None, but lowest concurrency (performance impact).

Choosing an Isolation Level

-- PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    -- transaction operations
COMMIT;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- transaction operations
COMMIT;

-- SQL Server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- transaction operations
COMMIT;

Guidelines:

READ COMMITTED: Good default for most applications
REPEATABLE READ: When you need consistent reads within a transaction
SERIALIZABLE: When absolute consistency is required (use sparingly)
READ UNCOMMITTED: Rarely appropriate (analytics on approximate data)

Locking Mechanisms

Databases use locks to implement isolation.

Lock Types

Shared Lock (S-lock): Allows reading, prevents writing.

-- Transaction holds shared lock while reading
SELECT * FROM Products WHERE ProductID = 100;
-- Other transactions can also read (shared locks compatible)
-- But cannot write (exclusive lock blocked)

Exclusive Lock (X-lock): Prevents both reading and writing.

-- Transaction holds exclusive lock while writing
UPDATE Products SET Price = 99.99 WHERE ProductID = 100;
-- Other transactions cannot read or write (blocked)

Lock Compatibility Matrix:

	Shared Lock	Exclusive Lock
Shared Lock	Compatible	Incompatible
Exclusive Lock	Incompatible	Incompatible

Lock Granularity

Row-level locks: Lock individual rows

-- Only locks the specific row
UPDATE Products SET Price = 99.99 WHERE ProductID = 100;
-- Other rows can be modified concurrently

Table-level locks: Lock entire table

-- Locks entire table
LOCK TABLES Products WRITE;
UPDATE Products SET Price = Price * 1.1;
UNLOCK TABLES;

Page-level locks: Lock a page (block) of rows

Trade-off:

Row-level: High concurrency, high overhead
Table-level: Low concurrency, low overhead

Two-Phase Locking (2PL)

A protocol ensuring serializability:

Phase 1 (Growing): Acquire locks, never release Phase 2 (Shrinking): Release locks, never acquire

Transaction Timeline:
Acquire Lock A ──┐
Acquire Lock B ──┤  Growing Phase
Read/Write   ────┤
Release Lock A ──┤  Shrinking Phase
Release Lock B ──┘

Strict 2PL: Hold all locks until COMMIT/ROLLBACK (most common).

Deadlocks

A deadlock occurs when two transactions wait for each other's locks.

-- Transaction A
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;  -- Locks row 1
    -- Waits for lock on row 2 (held by B)
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;

-- Transaction B (concurrent)
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance + 50 WHERE AccountID = 2;   -- Locks row 2
    -- Waits for lock on row 1 (held by A)
    UPDATE Accounts SET Balance = Balance - 50 WHERE AccountID = 1;
COMMIT;

-- Deadlock! Neither can proceed.

Deadlock Detection and Resolution

Databases detect deadlocks and abort one transaction:

-- MySQL will automatically detect and abort one transaction
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

-- Application must retry:
BEGIN TRANSACTION;
    -- operations
    IF SQLSTATE = '40001' THEN  -- Deadlock error code
        ROLLBACK;
        -- Retry transaction
    END IF;
COMMIT;

Preventing Deadlocks

Access resources in consistent order

-- Always update accounts in order of AccountID
UPDATE Accounts SET Balance = Balance - 100
WHERE AccountID IN (1, 2)
ORDER BY AccountID;  -- Ensures consistent lock order

Keep transactions short

-- Bad: Long transaction holds locks
BEGIN TRANSACTION;
    SELECT * FROM Products;  -- Locks many rows
    -- ... complex processing (takes 10 seconds) ...
    UPDATE Products SET Price = ...;
COMMIT;

-- Good: Release locks quickly
BEGIN TRANSACTION;
    SELECT * FROM Products;
COMMIT;

-- ... processing outside transaction ...

BEGIN TRANSACTION;
    UPDATE Products SET Price = ...;
COMMIT;

Use appropriate isolation levels

-- Lower isolation = fewer locks = less deadlock risk
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Use lock timeouts

-- MySQL: Set lock wait timeout
SET innodb_lock_wait_timeout = 5;  -- Wait max 5 seconds for lock

-- PostgreSQL: Set statement timeout
SET statement_timeout = '5s';

Multi-Version Concurrency Control (MVCC)

MVCC is an alternative to locking for concurrency control.

How MVCC Works

Instead of locking, MVCC keeps multiple versions of each row:

Account ID = 1:
Version 1: Balance = $1000, created by Transaction T1 (committed)
Version 2: Balance = $900,  created by Transaction T2 (in progress)

Readers see the appropriate version based on transaction start time:

Transaction started before T2: sees Version 1 ($1000)
Transaction started after T2 committed: sees Version 2 ($900)

Benefits:

Readers don't block writers
Writers don't block readers
Better concurrency than locking

MVCC in PostgreSQL

-- Transaction A
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Sees $1000
    -- Transaction B updates to $900 (creates new version)
    SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Still sees $1000
COMMIT;

-- Transaction A sees consistent snapshot throughout
-- No locks needed for SELECT

MVCC Implementation Details

Each row has hidden columns:

xmin: Transaction ID that created this version
xmax: Transaction ID that deleted/updated this version

-- PostgreSQL: See hidden columns
SELECT xmin, xmax, * FROM Accounts;

-- Output:
+------+------+----+---------+
| xmin | xmax | ID | Balance |
+------+------+----+---------+
| 100  | 0    | 1  | 1000    |  -- Created by transaction 100, still current
| 101  | 105  | 2  | 500     |  -- Created by 101, deleted by 105
| 105  | 0    | 2  | 600     |  -- New version created by 105
+------+------+----+---------+

MVCC Trade-offs

Pros:

High concurrency (readers don't block writers)
No read locks needed
Consistent snapshots

Cons:

More storage (multiple versions)
Vacuum/cleanup needed to remove old versions
Write-write conflicts still require locking

Distributed Transactions

When a transaction spans multiple databases or services.

Two-Phase Commit (2PC)

A protocol for coordinating distributed transactions.

Coordinator                    Participant A           Participant B
     |                              |                        |
     |-- Prepare (can you commit?) -->|                     |
     |-- Prepare (can you commit?) -----------------------→ |
     |                              |                        |
     |←-------- Yes (ready) ---------|                       |
     |←-------- Yes (ready) -------------------------------  |
     |                              |                        |
     |-- Commit ------------------>|                        |
     |-- Commit ------------------------------------------>  |
     |                              |                        |
     |←-------- Done --------------|                        |
     |←-------- Done ------------------------------------- |

Phase 1 (Prepare):

Coordinator asks all participants: "Can you commit?"
Participants prepare (write to log) and vote Yes/No
If any votes No, abort

Phase 2 (Commit/Abort):

If all voted Yes, coordinator sends Commit to all
If any voted No, coordinator sends Abort to all
Participants execute the decision

Example: Distributed Banking Transaction

-- Database A (Bank A)
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    PREPARE TRANSACTION 'transfer_tx';  -- Phase 1

-- Database B (Bank B)
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
    PREPARE TRANSACTION 'transfer_tx';  -- Phase 1

-- If both prepared successfully:
COMMIT PREPARED 'transfer_tx';  -- Phase 2 on both databases

-- If either failed:
ROLLBACK PREPARED 'transfer_tx';  -- Phase 2 on both databases

Issues with 2PC

Blocking: If coordinator crashes after Prepare, participants are stuck
Performance: Two network round-trips
Single point of failure: Coordinator failure blocks all participants

Alternatives to 2PC

Saga Pattern: Break into smaller transactions with compensation

Transaction 1: Debit Account A
  → Success
Transaction 2: Credit Account B
  → Failure
Compensation: Re-credit Account A (undo Transaction 1)

Eventual Consistency: Accept temporary inconsistency, converge eventually

Practical Examples

Example 1: Banking Transfer

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer failed';
    END;

    START TRANSACTION;

    -- Debit from source
    UPDATE Accounts
    SET Balance = Balance - amount
    WHERE AccountID = from_account;

    -- Check for overdraft
    IF (SELECT Balance FROM Accounts WHERE AccountID = from_account) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    -- Credit to destination
    UPDATE Accounts
    SET Balance = Balance + amount
    WHERE AccountID = to_account;

    -- Record transaction
    INSERT INTO Transactions (FromAccount, ToAccount, Amount, TransactionDate)
    VALUES (from_account, to_account, amount, NOW());

    COMMIT;
END //

DELIMITER ;

-- Usage
CALL transfer_money(1, 2, 100.00);

Example 2: Inventory Management

BEGIN TRANSACTION;

    -- Reserve inventory
    UPDATE Products
    SET StockQuantity = StockQuantity - 5,
        ReservedQuantity = ReservedQuantity + 5
    WHERE ProductID = 100;

    -- Check if enough stock
    IF (SELECT StockQuantity FROM Products WHERE ProductID = 100) < 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock';
    END IF;

    -- Create order
    INSERT INTO Orders (CustomerID, OrderDate, Status)
    VALUES (123, NOW(), 'Processing');

    SET @OrderID = LAST_INSERT_ID();

    -- Add order item
    INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@OrderID, 100, 5, (SELECT Price FROM Products WHERE ProductID = 100));

COMMIT;

Example 3: Preventing Lost Updates

-- Using optimistic locking with version numbers

-- Table with version column
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2),
    Version INT DEFAULT 0
);

-- Application code (pseudocode)
-- Read product with version
SELECT ProductID, ProductName, Price, Version
FROM Products
WHERE ProductID = 100;
-- Returns: (100, 'Widget', 29.99, 5)

-- User modifies price to 34.99

-- Update with version check
UPDATE Products
SET Price = 34.99,
    Version = Version + 1
WHERE ProductID = 100
  AND Version = 5;  -- Only update if version hasn't changed

-- If no rows affected, someone else updated it
IF ROW_COUNT() = 0 THEN
    -- Version conflict - reload and retry
END IF;

Exercises

Basic Exercises

Basic Transaction: Write a transaction to transfer inventory between two warehouses:
```
CREATE TABLE Warehouses (
    WarehouseID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT
);
```
Transfer 10 units of Product 101 from Warehouse 1 to Warehouse 2.

Identify Concurrency Problem: What problem occurs here?

-- Transaction A
BEGIN;
SELECT Price FROM Products WHERE ProductID = 100;  -- $50
-- ... calculate 10% discount ...
UPDATE Products SET Price = 45 WHERE ProductID = 100;
COMMIT;

-- Transaction B (overlapping)
BEGIN;
SELECT Price FROM Products WHERE ProductID = 100;  -- $50
-- ... calculate 20% discount ...
UPDATE Products SET Price = 40 WHERE ProductID = 100;
COMMIT;

Savepoints: Write a transaction that inserts an order and multiple items. If any item has insufficient stock, rollback just that item, not the entire order.

Intermediate Exercises

Isolation Levels: For each scenario, choose the appropriate isolation level:
- Reading configuration data that rarely changes
- Generating a financial report requiring consistent data
- Counting all pending orders (approximate count is acceptable)
- Processing a payment transaction

Deadlock Scenario: Show how these two transactions can deadlock:

-- Transaction A
UPDATE TableX SET ... WHERE ID = 1;
UPDATE TableY SET ... WHERE ID = 2;

-- Transaction B
UPDATE TableY SET ... WHERE ID = 2;
UPDATE TableX SET ... WHERE ID = 1;

How would you fix it?

MVCC Understanding: Explain what happens:

-- Transaction A (started at T1)
BEGIN;
SELECT Balance FROM Accounts WHERE ID = 1;  -- Returns $1000

-- Transaction B (started at T2 > T1)
BEGIN;
UPDATE Accounts SET Balance = 1500 WHERE ID = 1;
COMMIT;

-- Back to Transaction A
SELECT Balance FROM Accounts WHERE ID = 1;  -- What does this return?

Advanced Exercises

Complex Transaction: Implement a stored procedure for processing a subscription renewal:
- Check if customer has payment method
- Charge the payment method
- Update subscription expiration date
- Record transaction in billing history
- Send confirmation (record in outbox table)
Handle all possible failure cases.
Optimistic Locking: Implement a version-based optimistic locking system for a document editing application where multiple users can edit the same document.
Distributed Transaction: Design a distributed transaction for a microservices architecture:
- Order Service: Create order
- Inventory Service: Reserve stock
- Payment Service: Process payment
- Shipping Service: Create shipment
Handle failure scenarios using either 2PC or Saga pattern.

Transaction Analysis: Given this transaction:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT SUM(Quantity) FROM Inventory WHERE ProductID = 100;
-- Returns 500 units

-- Decision: If more than 400 units, mark as high stock
IF total > 400 THEN
    UPDATE Products SET StockStatus = 'High' WHERE ProductID = 100;
END IF;

COMMIT;

Could another transaction inserting inventory cause issues? What isolation level would prevent this?

Summary

In this reading, we explored database transactions and ACID properties:

Transactions treat multiple operations as atomic units of work
ACID properties ensure reliability:
- Atomicity: All-or-nothing execution
- Consistency: Database remains in valid state
- Isolation: Concurrent transactions don't interfere
- Durability: Committed changes survive failures
Isolation levels balance consistency vs. performance:
- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
Concurrency problems: Dirty reads, non-repeatable reads, phantom reads, lost updates
Locking mechanisms: Shared/exclusive locks, row/table level, two-phase locking
MVCC provides high concurrency without locks by maintaining multiple versions
Distributed transactions use protocols like two-phase commit
Best practices: Keep transactions short, handle deadlocks, choose appropriate isolation levels

Understanding transactions is crucial for building reliable database applications that maintain data integrity even under concurrent access and failures.

Conclusion

You've now completed Module 7 on Databases! You've learned:

Database fundamentals: DBMS, data models, relational concepts
SQL mastery: Queries, joins, aggregations, subqueries
Database design: Normalization, functional dependencies, normal forms
Performance optimization: Indexes, B-trees, query plans, EXPLAIN
Transaction management: ACID, isolation levels, concurrency control

These concepts form the foundation of modern data-driven applications. Practice by building real database applications, optimizing queries, and designing normalized schemas.

Next Steps:

Build a complete application with proper transaction handling
Practice query optimization on large datasets
Explore advanced topics: replication, sharding, NoSQL databases
Study distributed systems and database internals

Happy querying!