Transaction Lab - Discovery Challenge

🎯 Learning Objectives

By completing this lab, you will:

• Understand ACID properties through hands-on banking scenarios
• Master transaction control with BEGIN, COMMIT, ROLLBACK, and SAVEPOINT
• Prevent race conditions using locks and isolation levels
• Handle concurrent database operations safely
• Implement optimistic locking for conflict resolution
• Recognize and prevent deadlocks in multi-user environments

🚀 Getting Started (See Results in 30 Seconds!)

Prerequisites

• SQLite installed (already on most systems)
• Docker Desktop installed (for PostgreSQL exercises)
• Basic SQL knowledge (INSERT, UPDATE, SELECT)

Quick Start (SQLite)

# 1. Navigate to the SQLite folder
cd sqlite

# 2. Create database and load schema
sqlite3 banking.db < schema.sql

# 3. Run the working examples
sqlite3 banking.db < transactions.sql

# You should see transaction demos running immediately!

Quick Start (PostgreSQL with Docker)

# 1. Start PostgreSQL and pgAdmin
docker-compose up -d

# 2. Access pgAdmin at http://localhost:5052
# Email: admin@telebortbank.com
# Password: admin123

# 3. Connect to PostgreSQL server:
# Host: postgres (in pgAdmin) or localhost:5435 (from host)
# Username: bankadmin
# Password: secure_password_123

🎯 What's Already Working

This template includes a TelebortBank system with transaction demos:

✅ Fully Working Features (70% Pre-Built)

One. Banking Database Schema (schema.sql)

• Account management with version tracking
• Transfer history logging
• Sample accounts with realistic balances

2. Transaction Safety Demos (transactions.sql)

• ✅ Unsafe Transfer: Shows race condition vulnerability
• ✅ Safe Transfer with Locks: Proper BEGIN/COMMIT usage
• ✅ ROLLBACK on Error: Automatic error handling
• ✅ SAVEPOINT Demo: Partial rollback scenarios
• ✅ Isolation Levels: READ COMMITTED vs SERIALIZABLE
• ✅ Deadlock Simulation: Two competing transactions
• ✅ ACID Properties: Atomicity, Consistency, Isolation, Durability examples

3. Docker PostgreSQL Setup (docker-compose.yml)

• PostgreSQL 15 database server
• pgAdmin 4 web interface
• Pre-configured networking

⚠️ Your Tasks to Complete (30% TODOs)

📋 Tasks to Complete

TODO 1: Implement Optimistic Locking (Medium)

Scenario: Multiple users try to update the same account simultaneously.

Location: sqlite/transactions.sql (search for -- TODO 1)

Success Criteria:

• Check version number before updating
• Increment version on successful update
• Detect and handle version conflicts
• Return meaningful error when conflict occurs

Example Flow:

-- User A reads account (version = 1)
-- User B reads account (version = 1)
-- User A updates successfully (version = 2)
-- User B's update should FAIL (version mismatch)

Hints:

• Use WHERE version = ? in UPDATE statement
• Check changes() function to verify update succeeded
• If no rows affected, version conflict detected

TODO 2: Handle Concurrent Withdrawal Attempts (Medium)

Scenario: Two ATMs try to withdraw from the same account at the same time.

Location: sqlite/transactions.sql (search for -- TODO 2)

Success Criteria:

• Both withdrawals should be processed if sufficient balance
• Second withdrawal should fail if balance becomes insufficient
• No overdrafts allowed (balance must ``stay >= 0``)
• Transaction isolation prevents dirty reads

Example Flow:

-- Account balance: $1000
-- ATM 1: Withdraw $700 (should succeed)
-- ATM 2: Withdraw $500 (should fail - insufficient funds)
-- Final balance: $300

Hints:

• Use BEGIN IMMEDIATE for write transactions
• Check balance AFTER acquiring lock
• Use ROLLBACK if balance check fails

TODO 3: Prevent Transfer Deadlocks (Hard)

Scenario: Two transfers happen simultaneously in opposite directions.

Location: sqlite/transactions.sql (search for -- TODO 3)

Success Criteria:

• Implement lock ordering strategy
• Always lock accounts in consistent order (e.g., by account_id)
• Complete both transfers without deadlock
• Maintain transaction atomicity

Example Flow:

-- Transaction 1: Transfer $100 from Account A to Account B
-- Transaction 2: Transfer $50 from Account B to Account A
-- Without ordering: DEADLOCK!
-- With ordering: Both succeed

Hints:

• Lock accounts in ascending order by account_id
• Use ORDER BY account_id when selecting for update
• PostgreSQL supports SELECT FOR UPDATE explicitly

🎓 Learning Path

Phase One: Understand the Problems (30 minutes)

1. Run the "unsafe transfer" demo
2. Observe the race condition in action
3. Compare with "safe transfer" demo
4. Study the isolation level differences

Phase 2: Master Transaction Control (45 minutes)

1. Experiment with SAVEPOINT and ROLLBACK
2. Trigger errors intentionally to see ROLLBACK
3. Test different isolation levels in PostgreSQL
4. Analyze the deadlock scenario

Phase 3: Complete the TODOs (90 minutes)

1. Start with TODO 1 (optimistic locking)
2. Test with multiple concurrent sessions
3. Move to TODO 2 (concurrent withdrawals)
4. Finish with TODO 3 (deadlock prevention)

🧪 Testing Your Solutions

Test Concurrent Transactions (SQLite)

Terminal 1:

sqlite3 banking.db
.mode column
.headers on
BEGIN IMMEDIATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Wait 10 seconds before COMMIT
COMMIT;

Terminal 2 (within 10 seconds):

sqlite3 banking.db
BEGIN IMMEDIATE;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
-- This will be blocked until Terminal 1 commits
COMMIT;

Test in PostgreSQL (Better Concurrency)

# Terminal 1
docker exec -it w4-postgres psql -U bankadmin -d telebortbank

# Terminal 2
docker exec -it w4-postgres psql -U bankadmin -d telebortbank

# Run competing transactions in both terminals

🚀 Extension Challenges

Want to go further? Try these advanced exercises:

Challenge One: Audit Trail (Medium)

Create an audit_log table that records:

• All balance changes
• Transaction timestamps
• User/session information
• Success/failure status

Bonus: Use triggers to automate audit logging

Challenge 2: Distributed Transactions (Hard)

Simulate a two-phase commit:

• Transfer between two different databases
• Implement PREPARE, COMMIT, ROLLBACK phases
• Handle coordinator failure scenarios

Challenge 3: Performance Optimization (Hard)

Compare transaction throughput:

• Measure transactions per second
• Test different isolation levels
• Optimize lock contention
• Use connection pooling

Challenge 4: Real-World Banking Features (Advanced)

Implement:

• Daily withdrawal limits
• Pending vs. cleared transactions
• Interest calculation with transactions
• Multi-currency transfers with atomic exchange rates

📚 Key Concepts Reference

ACID Properties

Atomicity: All operations succeed or all fail

-- Either BOTH updates happen, or NEITHER
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Consistency: Database always in valid state

-- Balance cannot go negative (constraint maintained)
CHECK (balance >= 0)

Isolation: Transactions don't interfere

-- Transaction A doesn't see Transaction B's uncommitted changes
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Durability: Committed data survives crashes

-- After COMMIT, data is permanent
COMMIT; -- Data now safe even if power fails

Isolation Levels (PostgreSQL)

SQLite Note: Only supports SERIALIZABLE (default)

🐛 Common Issues & Solutions

Issue 1: "Database is locked" (SQLite)

Cause: Another process has a write lock Solution:

-- Use WAL mode for better concurrency
PRAGMA journal_mode=WAL;

Issue 2: Deadlock Detected

Cause: Circular lock dependency Solution: Implement consistent lock ordering (TODO 3)

Issue 3: "Version conflict" on optimistic locking

Cause: Another transaction modified the row Solution: Retry with fresh data or ask user to resolve

Issue 4: Docker containers won't start

Cause: Port already in use Solution:

# Check what's using the port
lsof -i :5435

# Stop conflicting service or change port in docker-compose.yml

📊 Verification Checklist

Before considering this activity complete, verify:

• All 7 working demos run successfully
• TODO 1: Optimistic locking detects conflicts
• TODO 2: Concurrent withdrawals maintain balance integrity
• TODO 3: No deadlocks occur with lock ordering
• PostgreSQL setup works (pgAdmin accessible)
• Can run transactions in multiple terminals simultaneously
• All ACID properties demonstrated and understood
• Extension challenge attempted (at least one)

🎯 Success Metrics

You've mastered transactions when you can:

1. Explain why transactions are necessary (not just "for safety")
2. Choose appropriate isolation levels for different scenarios
3. Prevent race conditions using locks or optimistic locking
4. Debug deadlocks and implement prevention strategies
5. Write production-quality banking code with proper error handling

📖 Additional Resources

• SQLite Transaction Documentation
• PostgreSQL Concurrency Control
• Martin Kleppmann - Designing Data-Intensive Applications
• Jepsen: Distributed Systems Safety Research

🆘 Need Help?

Debugging Steps:

1. Check account balances before and after transactions
2. Use SELECT * FROM transfers to see transaction history
3. In PostgreSQL, check pg_locks system view
4. Enable query logging to see exact SQL execution

Discussion Questions:

• Why does SQLite use SERIALIZABLE by default?
• When would you choose optimistic over pessimistic locking?
• How do NoSQL databases handle transactions differently?
• What are the trade-offs between consistency and performance?

TelebortBank Mission: "Banking without race conditions since 2025"

Happy transaction wrangling! 💰🔒