🚀 FlashSale: High-Concurrency Inventory Manager

A hands-on mini-project demonstrating database concurrency control mechanisms through a simulated flash sale scenario.

📖 Project Overview

FlashSale simulates a high-concurrency e-commerce scenario where multiple users attempt to purchase limited stock simultaneously. This project demonstrates various concurrency anomalies and their solutions using different locking strategies and isolation levels.

Learning Objectives

• Understand and demonstrate concurrency anomalies (Lost Update, Dirty Read)
• Implement Two-Phase Locking (2PL) with row-level locks
• Implement Optimistic Concurrency Control (OCC) using versioning
• Analyze different transaction isolation levels
• Handle and understand database deadlocks

🛠️ Tech Stack

• Database: PostgreSQL 12+ (recommended) or MySQL 8+
• Programming Language: Python 3.8+
• Database Driver: psycopg2 (PostgreSQL) or PyMySQL (MySQL)

📁 Project Structure

FlashSale/
├── schema.sql                      # Database schema
├── config.py                       # Database configuration
├── requirements.txt                # Python dependencies
├── phase1_lost_update.py          # Phase 1: Anomaly demonstration
├── phase2_pessimistic_locking.py  # Phase 2: Pessimistic locking
├── phase2_deadlock_demo.py        # Phase 2 Bonus: Deadlock scenario
├── phase3_optimistic_locking.py   # Phase 3: Optimistic locking
├── phase4_isolation_levels.py     # Phase 4: Isolation analysis
└── README.md                       # This file

🚀 Setup Instructions

Step 1: Install PostgreSQL

Windows:

1. Download PostgreSQL from postgresql.org
2. Run the installer and follow the wizard
3. Remember your postgres user password
4. PostgreSQL will be available on port 5432

macOS:

brew install postgresql@15
brew services start postgresql@15

Linux:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Step 2: Create Database

Open PostgreSQL command line (psql) or use pgAdmin:

CREATE DATABASE flashsale;

Or from terminal:

# Linux/macOS
psql -U postgres -c "CREATE DATABASE flashsale;"

# Windows (PowerShell)
& 'C:\Program Files\PostgreSQL\15\bin\psql.exe' -U postgres -c "CREATE DATABASE flashsale;"

Step 3: Configure Database Connection

Edit config.py with your database credentials:

DB_CONFIG = {
    'host': 'localhost',
    'port': 5432,
    'database': 'flashsale',
    'user': 'postgres',
    'password': 'your_password_here'  # Change this!
}

Step 4: Install Python Dependencies

# Create virtual environment (optional but recommended)
python -m venv venv

# Activate virtual environment
# Windows:
venv\Scripts\activate
# macOS/Linux:
source venv/bin/activate

# Install dependencies
pip install -r requirements.txt

Step 5: Initialize Database Schema

# Linux/macOS
psql -U postgres -d flashsale -f schema.sql

# Windows (PowerShell)
& 'C:\Program Files\PostgreSQL\15\bin\psql.exe' -U postgres -d flashsale -f schema.sql

Or manually:

python -c "import psycopg2; from config import DB_CONFIG; conn = psycopg2.connect(**DB_CONFIG); cur = conn.cursor(); cur.execute(open('schema.sql').read()); conn.commit(); print('✓ Schema created')"

Step 6: Verify Setup

python -c "import psycopg2; from config import DB_CONFIG; conn = psycopg2.connect(**DB_CONFIG); print('✓ Database connection successful!')"

🎯 Running the Project

Phase 1: The Lost Update Problem

Demonstrates what happens WITHOUT proper concurrency control:

python phase1_lost_update.py

Expected Output:

• Both users successfully "buy" the last item
• Stock goes to -1 (anomaly!)
• Demonstrates the Lost Update problem

Phase 2: Pessimistic Locking

Implements Two-Phase Locking using SELECT ... FOR UPDATE:

python phase2_pessimistic_locking.py

Expected Output:

• One user acquires lock, the other waits
• Only one purchase succeeds
• Stock remains at 0 (correct!)

Phase 2 Bonus: Deadlock Detection

Intentionally creates a deadlock scenario:

python phase2_deadlock_demo.py

Expected Output:

• Two users lock resources in opposite order
• Database detects circular wait
• One transaction is aborted (deadlock victim)

Phase 3: Optimistic Locking

Implements Optimistic Concurrency Control using versioning:

python phase3_optimistic_locking.py

Expected Output:

• Both users read stock and version
• One update succeeds, the other detects version conflict
• Failed transaction retries automatically

Phase 4: Isolation Level Analysis

Compares different isolation levels:

python phase4_isolation_levels.py

Expected Output:

• Comparison of READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE
• Shows how each level handles concurrent updates during a long report

📊 Expected Results Summary

Phase	Test	Expected Behavior
1	Lost Update	Stock = -1, Both succeed (anomaly)
2	Pessimistic	Stock = 0, One waits for lock
2 Bonus	Deadlock	One transaction aborted by DB
3	Optimistic	Stock = 0, One retries due to version conflict
4	Isolation	Different consistency guarantees per level

🧩 Key Concepts Demonstrated

1. Lost Update Problem

# WITHOUT locking:
User A reads: stock = 1
User B reads: stock = 1
User A writes: stock = 0
User B writes: stock = 0  # ❌ Lost A's update!

2. Pessimistic Locking (2PL)

BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- Lock acquired
-- Other transactions must wait here
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;  -- Lock released

3. Optimistic Locking (OCC)

-- Read with version
SELECT stock, version FROM products WHERE id = 1;
-- Update with version check
UPDATE products 
SET stock = 0, version = version + 1 
WHERE id = 1 AND version = 0;  -- Fails if version changed!

4. Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ COMMITTED	❌ No	✅ Possible	✅ Possible
REPEATABLE READ	❌ No	❌ No	❌ No (PostgreSQL)
SERIALIZABLE	❌ No	❌ No	❌ No

🐛 Troubleshooting

Connection Error

psycopg2.OperationalError: could not connect to server

Solution: Verify PostgreSQL is running and credentials in config.py are correct.

Module Not Found

ModuleNotFoundError: No module named 'psycopg2'

Solution: Install dependencies: pip install -r requirements.txt

Permission Denied

ERROR: permission denied for table products

Solution: Ensure your database user has proper permissions:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user;

Stock Already Negative

If running tests multiple times without cleanup, reset with:

UPDATE products SET stock = 10, version = 0;
DELETE FROM orders;

📝 Deliverables for Assessment

1. Source Code: All Python scripts (completed ✓)
2. SQL Schema: schema.sql (completed ✓)
3. Technical Report: Document analyzing:
   • Why Phase 1 failed (write-write conflict)
   • When to use Pessimistic vs Optimistic locking
   • Deadlock explanation with cycle diagram
   • Performance observations

Suggested Report Structure

1. Introduction
2. Phase 1: Anomaly Analysis
   - Screenshot of negative stock
   - Explanation of Lost Update
3. Phase 2: Pessimistic Locking
   - Code snippet with FOR UPDATE
   - Deadlock scenario explanation
4. Phase 3: Optimistic Locking
   - Version check logic
   - Retry mechanism
5. Phase 4: Isolation Level Comparison
   - Table comparing results
   - Performance notes
6. Conclusion: Trade-offs

🎓 Learning Resources

• PostgreSQL Documentation - Concurrency Control
• Transaction Isolation Levels
• Database Deadlocks Explained

🤝 Contributing

This is an educational project. Feel free to:

• Add more test scenarios
• Implement MySQL version
• Add performance benchmarks
• Create visualization scripts

📄 License

This project is for educational purposes as part of the ASAP Unit 4 curriculum.

👨‍💻 Author

Athul A Created as part of the Database Concurrency Control module.

---

Happy Learning! 🎉

If you encounter any issues or have questions, please review the troubleshooting section or consult the PostgreSQL documentation.