Transactions and Concurrency

Account Balance: $1000

User A: Reads balance ($1000)
User B: Reads balance ($1000)
User A: Withdraws $800 (1000 - 800 = 200)
User B: Withdraws $800 (1000 - 800 = 200)

Final Balance: $200 (But $1600 was withdrawn!)

-- Create the banking database
CREATE DATABASE BankDB;
USE BankDB;

-- Create accounts table
CREATE TABLE accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT positive_balance CHECK (balance >= 0)
);

-- Create transactions history table
CREATE TABLE transaction_history (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    from_account INT,
    to_account INT,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_type ENUM('deposit', 'withdrawal', 'transfer') NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_account) REFERENCES accounts(account_id),
    FOREIGN KEY (to_account) REFERENCES accounts(account_id)
);

-- Create test accounts with initial balances
INSERT INTO accounts (account_holder, balance) VALUES
    ('Alice Johnson', 5000.00),
    ('Bob Smith', 3000.00),
    ('Charlie Brown', 1000.00),
    ('Diana Prince', 10000.00),
    ('Eve Wilson', 2500.00);

-- Verify our accounts
SELECT * FROM accounts;

-- DANGEROUS: Transfer without transaction protection
DELIMITER //
CREATE PROCEDURE unsafe_transfer(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10, 2)
)
BEGIN
    DECLARE current_balance DECIMAL(10, 2);
    
    -- Check sender's balance
    SELECT balance INTO current_balance 
    FROM accounts 
    WHERE account_id = p_from_account;
    
    -- Simulate processing delay (where race conditions occur!)
    SELECT SLEEP(0.1);
    
    -- Deduct from sender
    UPDATE accounts 
    SET balance = balance - p_amount 
    WHERE account_id = p_from_account;
    
    -- Add to receiver
    UPDATE accounts 
    SET balance = balance + p_amount 
    WHERE account_id = p_to_account;
    
    SELECT 'Transfer completed' AS status;
END //
DELIMITER ;

CALL unsafe_transfer(1, 2, 4000.00);  -- Alice to Bob

CALL unsafe_transfer(1, 3, 4000.00);  -- Alice to Charlie

SELECT * FROM accounts WHERE account_id IN (1, 2, 3);
-- Alice's balance might be negative!

DELIMITER //
CREATE PROCEDURE safe_transfer(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10, 2)
)
BEGIN
    DECLARE current_balance DECIMAL(10, 2);
    DECLARE exit handler for sqlexception
    BEGIN
        -- If anything goes wrong, rollback!
        ROLLBACK;
        SELECT 'Transfer failed - rolled back' AS status;
    END;
    
    -- Start transaction
    START TRANSACTION;
    
    -- Lock the sender's account for update
    SELECT balance INTO current_balance 
    FROM accounts 
    WHERE account_id = p_from_account
    FOR UPDATE;
    
    -- Check if sufficient funds
    IF current_balance < p_amount THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Perform the transfer
    UPDATE accounts 
    SET balance = balance - p_amount 
    WHERE account_id = p_from_account;
    
    UPDATE accounts 
    SET balance = balance + p_amount 
    WHERE account_id = p_to_account;
    
    -- Record the transaction
    INSERT INTO transaction_history 
        (from_account, to_account, amount, transaction_type, status)
    VALUES 
        (p_from_account, p_to_account, p_amount, 'transfer', 'completed');
    
    -- Commit the transaction
    COMMIT;
    
    SELECT 'Transfer successful' AS status;
END //
DELIMITER ;

-- Session 1: Transfer from Alice to Bob
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Wait before completing...

-- Session 2: Transfer from Bob to Alice
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 2;
-- Now try to update Alice's account
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- DEADLOCK!

-- Session 1: Set READ UNCOMMITTED (dirty reads possible)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1;

-- Session 2: Make uncommitted change
START TRANSACTION;
UPDATE accounts SET balance = 9999.99 WHERE account_id = 1;
-- Don't commit yet!

-- Back to Session 1: You'll see the uncommitted change!
SELECT * FROM accounts WHERE account_id = 1;

-- Deposit money
DELIMITER //
CREATE PROCEDURE deposit(
    IN p_account_id INT,
    IN p_amount DECIMAL(10, 2)
)
BEGIN
    START TRANSACTION;
    
    UPDATE accounts 
    SET balance = balance + p_amount 
    WHERE account_id = p_account_id;
    
    INSERT INTO transaction_history 
        (to_account, amount, transaction_type, status)
    VALUES 
        (p_account_id, p_amount, 'deposit', 'completed');
    
    COMMIT;
    
    SELECT CONCAT('Deposited 

## Challenge Exercises

### Challenge 1: Stress Test Your Bank

Create a stress testing procedure:

```sql
DELIMITER //
CREATE PROCEDURE stress_test_transfers(
    IN num_transfers INT
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE from_acc INT;
    DECLARE to_acc INT;
    DECLARE amount DECIMAL(10, 2);
    
    WHILE i < num_transfers DO
        -- Random accounts (1-5)
        SET from_acc = FLOOR(1 + RAND() * 5);
        SET to_acc = FLOOR(1 + RAND() * 5);
        
        -- Ensure different accounts
        WHILE from_acc = to_acc DO
            SET to_acc = FLOOR(1 + RAND() * 5);
        END WHILE;
        
        -- Random amount (10-500)
        SET amount = ROUND(10 + RAND() * 490, 2);
        
        -- Attempt transfer (might fail due to insufficient funds)
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN
                -- Ignore failures, continue testing
            END;
            
            CALL safe_transfer(from_acc, to_acc, amount);
        END;
        
        SET i = i + 1;
    END WHILE;
    
    -- Show final state
    SELECT 'Stress test complete!' AS status;
    SELECT * FROM accounts ORDER BY account_id;
END //
DELIMITER ;

-- Run the stress test
CALL stress_test_transfers(100);

DELIMITER //
CREATE PROCEDURE calculate_daily_interest(
    IN interest_rate DECIMAL(5, 4)
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE acc_id INT;
    DECLARE acc_balance DECIMAL(10, 2);
    DECLARE cur CURSOR FOR 
        SELECT account_id, balance FROM accounts;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    START TRANSACTION;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO acc_id, acc_balance;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Calculate and add interest
        UPDATE accounts 
        SET balance = balance + (balance * interest_rate / 365)
        WHERE account_id = acc_id;
    END LOOP;
    
    CLOSE cur;
    COMMIT;
    
    SELECT CONCAT('Interest calculated at ', interest_rate * 100, '% annual rate') AS status;
END //
DELIMITER ;

-- Create audit table
CREATE TABLE audit_log (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    user VARCHAR(100),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_values JSON,
    new_values JSON
);

-- Create trigger for balance changes
DELIMITER //
CREATE TRIGGER audit_balance_changes
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, user, old_values, new_values)
    VALUES (
        'accounts',
        'UPDATE',
        USER(),
        JSON_OBJECT('account_id', OLD.account_id, 'balance', OLD.balance),
        JSON_OBJECT('account_id', NEW.account_id, 'balance', NEW.balance)
    );
END //
DELIMITER ;

-- Always lock accounts in ID order to prevent deadlocks
IF from_account_id > to_account_id THEN
    -- Lock in ascending order
    SELECT ... FROM accounts WHERE account_id = to_account_id FOR UPDATE;
    SELECT ... FROM accounts WHERE account_id = from_account_id FOR UPDATE;
ELSE
    -- Lock in ascending order
    SELECT ... FROM accounts WHERE account_id = from_account_id FOR UPDATE;
    SELECT ... FROM accounts WHERE account_id = to_account_id FOR UPDATE;
END IF;

-- Index for quick balance lookups
CREATE INDEX idx_account_balance ON accounts(account_id, balance);

-- Index for transaction history queries
CREATE INDEX idx_transaction_date ON transaction_history(created_at);
CREATE INDEX idx_transaction_accounts ON transaction_history(from_account, to_account);

-- Instead of 1000 individual updates
START TRANSACTION;
UPDATE accounts 
SET balance = balance * 1.001  -- 0.1% interest
WHERE account_type = 'savings';
COMMIT;

-- Every transaction has equal debits and credits
INSERT INTO ledger_entries (account, debit, credit) VALUES
    ('checking_123', 100.00, 0),      -- Debit checking
    ('savings_456', 0, 100.00);       -- Credit savings
-- Sum of debits always equals sum of credits

-- Use unique transaction IDs to prevent duplicate processing
INSERT INTO transaction_history (transaction_uuid, ...) 
VALUES (UUID(), ...)
ON DUPLICATE KEY UPDATE status = status;  -- No-op if exists

-- For every operation, have a reversal ready
CREATE PROCEDURE reverse_transfer(IN p_transaction_id INT)
-- Reverses a previous transfer by creating opposite entries

-- Implement deadlock retry logic
DELIMITER //
CREATE PROCEDURE transfer_with_retry(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10, 2),
    IN max_retries INT DEFAULT 3
)
BEGIN
    DECLARE retry_count INT DEFAULT 0;
    DECLARE exit handler for sqlexception
    BEGIN
        IF retry_count < max_retries THEN
            SET retry_count = retry_count + 1;
            -- Wait random time before retry
            SELECT SLEEP(RAND() * 0.1);
            RESIGNAL;
        ELSE
            ROLLBACK;
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Max retries exceeded';
        END IF;
    END;
    
    retry_loop: LOOP
        BEGIN
            CALL safe_transfer(p_from_account, p_to_account, p_amount);
            LEAVE retry_loop;
        END;
    END LOOP;
END //
DELIMITER ;

-- Increase timeout or optimize query
SET SESSION innodb_lock_wait_timeout = 10;  -- 10 seconds

-- Or use non-blocking approach
SELECT balance FROM accounts 
WHERE account_id = ? 
FOR UPDATE NOWAIT;  -- Fail immediately if locked

-- Use optimistic locking with version numbers
ALTER TABLE accounts ADD COLUMN version_number INT DEFAULT 1;

-- Update with version check
UPDATE accounts 
SET balance = balance - ?, version_number = version_number + 1
WHERE account_id = ? AND version_number = ?;

-- Check if update succeeded
IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Account modified by another transaction';
END IF;

-- Use appropriate isolation level
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
-- All reads in this transaction will be consistent
SELECT balance FROM accounts WHERE account_id = 1;  -- First read
-- ... other operations ...
SELECT balance FROM accounts WHERE account_id = 1;  -- Same value as first read
COMMIT;

-- Transaction Control
START TRANSACTION;
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

-- Locking
SELECT ... FOR UPDATE;          -- Exclusive lock
SELECT ... FOR SHARE;           -- Shared lock
SELECT ... FOR UPDATE NOWAIT;   -- Fail if locked

-- Isolation Levels
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Deadlock Handling
SET SESSION innodb_lock_wait_timeout = 10;
SHOW ENGINE INNODB STATUS;  -- View deadlock information

## Challenge Exercises

### Challenge 1: Stress Test Your Bank

Create a stress testing procedure:

__CODE_BLOCK_11__

### Challenge 2: Implement Account Interest

Add daily interest calculation with proper locking:

__CODE_BLOCK_12__

### Challenge 3: Build a Transaction Audit System

Create a comprehensive audit trail:

__CODE_BLOCK_13__

## Best Practices

### 1. Transaction Design Principles

**Keep Transactions Short and Sweet**
- Lock resources for minimal time
- Do validation BEFORE starting transaction
- Prepare all data before BEGIN

**Order Operations Consistently**
__CODE_BLOCK_14__

### 2. Isolation Level Selection Guide

| Isolation Level | Use When | Banking Example |
|----------------|----------|-----------------|
| READ UNCOMMITTED | Never in banking! | ❌ Never - could show wrong balance |
| READ COMMITTED | Standard operations | ✅ Checking balance |
| REPEATABLE READ | Multi-step calculations | ✅ Interest calculations |
| SERIALIZABLE | Critical operations | ✅ End-of-day reconciliation |

### 3. Deadlock Prevention Strategies

1. **Lock Ordering**: Always acquire locks in the same order
2. **Lock Timeout**: Set reasonable timeouts
   __CODE_BLOCK_15__
3. **Retry Logic**: Implement automatic retry for deadlocks
4. **Index Optimization**: Ensure locks are as granular as possible

### 4. Performance Optimization

**Use Appropriate Indexes**
__CODE_BLOCK_16__

**Batch Operations When Possible**
__CODE_BLOCK_17__

## Real-World Applications

### How Real Banks Handle Concurrency

1. **Event Sourcing**: Banks often store every action as an event
   - Never update, only append
   - Calculate balance by replaying events
   - Perfect audit trail

2. **Distributed Transactions**: Modern banks use:
   - Two-phase commit for multi-system transactions
   - Saga patterns for long-running processes
   - Event-driven architectures

3. **Read Replicas**: Separate read and write operations
   - Writes go to primary database
   - Reads from replicas (slight delay acceptable)

### Common Banking System Patterns

**1. Double-Entry Bookkeeping**
__CODE_BLOCK_18__

**2. Idempotent Operations**
__CODE_BLOCK_19__

**3. Compensating Transactions**
__CODE_BLOCK_20__

## Troubleshooting Guide

### Common Errors Students Encounter

#### Problem 1: "Deadlock Detected and Resolved"
**Error**: MySQL/PostgreSQL automatically kills one transaction in a deadlock
**Solution**:
__CODE_BLOCK_21__
**Prevention**: Always acquire locks in consistent order (e.g., ascending account ID)

#### Problem 2: "Lock Wait Timeout Exceeded"
**Error**: Transaction waits too long for a lock
**Solution**:
__CODE_BLOCK_22__
**Prevention**: Keep transactions short and acquire locks in proper order

#### Problem 3: "Lost Update Problem"
**Error**: Concurrent updates overwrite each other
**Solution**:
__CODE_BLOCK_23__
**Prevention**: Use versioning or timestamps for optimistic concurrency control

#### Problem 4: "Inconsistent Reads"
**Error**: Reading different values within same transaction
**Solution**:
__CODE_BLOCK_24__
**Prevention**: Understand isolation levels and choose appropriate one for use case

### Debugging Tips
1. **Enable query logging**: Track all SQL statements for analysis
2. **Monitor lock contentions**: Identify frequently locked resources
3. **Use EXPLAIN**: Analyze query execution plans for lock patterns
4. **Test with concurrent load**: Simulate real-world concurrent access patterns

## Key Takeaways

### Main Concepts Learned
1. **ACID Properties**: Foundation of reliable database transactions
2. **Concurrency Control**: Managing simultaneous access to shared data
3. **Isolation Levels**: Balancing consistency with performance
4. **Deadlock Prevention**: Strategies to avoid and handle deadlocks

### Practical Skills Gained
- Ability to design transaction-safe database operations
- Skills to handle concurrent access in multi-user systems
- Knowledge of isolation levels and their appropriate use cases
- Understanding of deadlock detection and prevention strategies

### Real-World Applications
- **Financial Systems**: Banking, payment processing, accounting
- **E-commerce**: Order processing, inventory management
- **Gaming**: Virtual currency, item trading, leaderboards
- **Booking Systems**: Hotel reservations, event tickets, resource scheduling

## Quick Reference

### Important Concepts and Commands
__CODE_BLOCK_25__

### Key Terms and Definitions
- **Transaction**: A logical unit of work that is either completed entirely or not at all
- **ACID**: Atomicity, Consistency, Isolation, Durability properties
- **Deadlock**: Circular dependency where transactions wait for each other
- **Lock**: Mechanism to prevent concurrent access conflicts
- **Isolation Level**: Degree of transaction separation from other concurrent transactions
- **Race Condition**: Undesirable outcome due to timing of concurrent operations

### Common Patterns
1. **Two-Phase Locking**: Acquire all locks before releasing any
2. **Lock Ordering**: Always acquire locks in same order to prevent deadlocks
3. **Optimistic Locking**: Check for conflicts at commit time
4. **Pessimistic Locking**: Acquire locks early and hold until commit

### ACID Compliance Checklist
- [ ] **Atomicity**: All operations in transaction succeed or all fail
- [ ] **Consistency**: Database constraints are never violated
- [ ] **Isolation**: Concurrent transactions don't interfere with each other
- [ ] **Durability**: Committed changes survive system failures

### Your Banking System Checklist

✅ **ACID Compliance**
- [ ] All transfers are atomic
- [ ] Balance never goes negative
- [ ] Concurrent transfers don't interfere
- [ ] Committed transactions survive crashes

✅ **Performance**
- [ ] Appropriate indexes on accounts table
- [ ] Transaction history is partitioned by date
- [ ] Lock wait timeout is configured
- [ ] Deadlock retry logic implemented

✅ **Security**
- [ ] All procedures validate inputs
- [ ] Audit trail for all operations
- [ ] Role-based access control
- [ ] Encryption for sensitive data

✅ **Monitoring**
- [ ] Track failed transactions
- [ ] Monitor lock wait times
- [ ] Alert on deadlocks
- [ ] Daily reconciliation reports

## Next Steps

Congratulations! You've built a banking system that handles millions in transactions safely. You've experienced firsthand why transactions are critical for data integrity and learned how to handle concurrent access like a pro.

In the next lesson, we'll explore how to optimize these systems for massive scale, learning about sharding, replication, and distributed databases. Your banking system is about to go global! 🌍