Transactions & ACID Properties - Activity 08

Welcome to Activity 08! This template teaches you database transactions, ACID properties, and data integrity using a realistic banking system.

🎯 Learning Objectives

By completing this activity, you will:

• Master database transactions (BEGIN, COMMIT, ROLLBACK)
• Understand all four ACID properties deeply
• Implement atomic money transfers
• Handle concurrent access and race conditions
• Use SAVEPOINT for complex transactions
• Create audit trails with triggers
• Prevent data corruption and inconsistencies

🚀 Getting Started (See Results in 30 Seconds!)

IMPORTANT: This template includes WORKING CODE! You can see results immediately:

Option One: SQLite Browser (Easiest - No Setup Required)

1. Download SQLite Browser (if you don't have it):

   • Visit: https://sqlitebrowser.org/
   • Download for your operating system
   • Install (takes 2 minutes)

2. Open the database files:

   • Launch DB Browser for SQLite
   • Click "New Database" -> Save as banking.db
   • Click "Execute SQL" tab
   • Open sqlite/schema.sql -> Click "Execute" ▶️
   • Open sample-data.sql -> Click "Execute" ▶️
   • Click "Browse Data" tab -> See 10 accounts immediately!

3. Run the transaction examples:

   • Open sqlite/queries.sql
   • Copy Query 1 (Simple Transaction)
   • Paste in "Execute SQL" tab -> Click "Execute" ▶️
   • You just ran your first transaction! 🎉

Option 2: Docker (One Command Setup)

# Make sure you're in the activity folder, then:
docker-compose up -d

# Access MySQL:
docker exec -it w4-mysql mysql -u root -plearning123 contacts_db
# Then load schema:
source /docker-entrypoint-initdb.d/schema.sql;

# Access PostgreSQL:
docker exec -it w4-postgres psql -U postgres contacts_db
# Then load schema:
\i /docker-entrypoint-initdb.d/schema.sql

Option 3: Online (Supabase - Cloud PostgreSQL)

1. Visit https://supabase.com and create free account
2. Create new project (takes 2 minutes)
3. Open SQL Editor in dashboard
4. Copy/paste postgresql/schema.sql and sample-data.sql
5. Run transaction queries immediately!

🎯 What's Already Working

70% of the code is implemented for you:

• ✅ Complete 3-table banking schema (accounts, transactions, audit_log)
• ✅ Sample data: 10 accounts, 30 transactions, audit logs
• ✅ Query 1: Simple transaction example (working)
• ✅ Query 2: Transaction with ROLLBACK (working)
• ✅ Query 3: Atomic money transfer (working)
• ✅ Query 4: Transaction with validation (working)
• ✅ Query 5: SAVEPOINT example (working)
• ✅ Query 6: Isolation level demo (working)
• ✅ Query 7: Audit log trigger (working)
• ⚠️ Query 8: Implement money transfer (TODO for you)
• ⚠️ Query 9: Handle concurrent withdrawals (TODO for you)
• ⚠️ Query 10: Document isolation phenomena (TODO for you)

📝 Your Learning Tasks

1. First, test the 7 working transaction examples to understand patterns
2. Then complete the 3 TODO challenges applying ACID principles
3. Finally, try the bonus challenges for advanced scenarios

📋 Tasks to Complete

TODO 1: Atomic Money Transfer (Medium)

Location: sqlite/queries.sql line 229 Your Task: Implement a safe money transfer between two accounts

Success Criteria:

• Transfer $500 from Account CHK-1001 to CHK-1003
• Both account balances must update, or neither does (Atomicity)
• Check that sender has sufficient funds (``balance >= 500``)
• Record the transaction in transactions table with status='completed'
• Use proper transaction with BEGIN/COMMIT
• Handle errors with ROLLBACK if needed

Hints:

• Start with BEGIN TRANSACTION;
• Check balance first: SELECT balance FROM accounts WHERE account_number = 'CHK-1001';
• Use AND balance >= 500 in UPDATE to prevent overdraft
• Insert transaction record before COMMIT
• End with COMMIT;

Expected Output:

-- Before:
-- CHK-1001: $5,000.00
-- CHK-1003: $3,200.00

-- After:
-- CHK-1001: $4,500.00
-- CHK-1003: $3,700.00

-- Transactions table gains new row:
-- from_account_id=1, to_account_id=3, amount=500, status='completed'

Why This Matters: In banking, money must never disappear or duplicate. Atomic transactions ensure both accounts update together or not at all, preventing financial discrepancies.

TODO 2: Concurrent Withdrawal Handling (Hard)

Location: sqlite/queries.sql line 270 Your Task: Handle concurrent withdrawal scenario to prevent overdraft

Scenario:

• Account CHK-1004 has $8,750 balance
• Two withdrawals happen simultaneously: $5,000 and $4,500
• Total = $9,500 (exceeds balance by $750)
• Only ONE withdrawal should succeed

Success Criteria:

• Implement transaction with proper locking mechanism
• First withdrawal succeeds (balance becomes $3,750)
• Second withdrawal fails (insufficient funds error)
• Account never goes negative
• Handle failure gracefully without errors

Hints:

• Use BEGIN IMMEDIATE TRANSACTION; for write lock (SQLite)
• Check balance BEFORE withdrawal: SELECT balance FROM accounts WHERE id = 4;
• Use conditional UPDATE: UPDATE accounts SET balance = balance - 5000 WHERE id = 4 AND balance >= 5000;
• Check affected rows to verify success
• For PostgreSQL, use SELECT FOR UPDATE to lock the row

Expected Output:

-- Transaction 1:
BEGIN IMMEDIATE TRANSACTION;
-- Check: balance = $8,750 ✓
UPDATE accounts SET balance = balance - 5000 WHERE id = 4 AND balance >= 5000;
-- Success: balance = $3,750
COMMIT;

-- Transaction 2:
BEGIN IMMEDIATE TRANSACTION;
-- Check: balance = $3,750 (already reduced!)
UPDATE accounts SET balance = balance - 4500 WHERE id = 4 AND balance >= 4500;
-- Fails: 0 rows affected (insufficient funds)
ROLLBACK;

Why This Matters: Race conditions can cause overdrafts in real banking systems. Proper transaction isolation and locking prevent customers from withdrawing more than they have, even with simultaneous requests.

TODO 3: Transaction Isolation Documentation (Challenge)

Location: sqlite/queries.sql line 317 Your Task: Document and demonstrate transaction isolation levels and phenomena

Success Criteria:

• Document three isolation phenomena:
  1. Dirty Read: Reading uncommitted data from another transaction
  2. Non-Repeatable Read: Same query returns different results within transaction
  3. Phantom Read: New rows appear mid-transaction
• Provide SQL code examples for each phenomenon
• Explain how SERIALIZABLE isolation prevents each issue
• Test with two concurrent transactions

Hints:

• SQLite uses SERIALIZABLE isolation by default (highest level)
• To demonstrate, you need two database connections (two terminal windows)
• Structure examples with clear comments showing timing
• Show "before" and "after" states

Expected Documentation Format:

-- ========================================
-- PHENOMENON 1: Dirty Read
-- ========================================
-- Definition: Transaction reads data that another transaction has modified
-- but not yet committed. If the other transaction rolls back, the read
-- data becomes invalid.

-- Session 1 (Transaction A):
BEGIN TRANSACTION;
UPDATE accounts SET balance = 10000 WHERE id = 1;
-- Don't commit yet! This is uncommitted data.

-- Session 2 (Transaction B):
BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- SERIALIZABLE: Shows original value ($5000)
-- READ UNCOMMITTED (if supported): Would show $10000 (dirty read!)

-- Session 1 rolls back:
ROLLBACK;  -- Oops, that $10000 was wrong!

-- Session 2 continues:
SELECT balance FROM accounts WHERE id = 1;
-- Still shows $5000 (correct original value)
COMMIT;

-- Result: SERIALIZABLE prevents dirty reads. Transaction B never saw
-- the uncommitted $10000 from Transaction A.

Why This Matters: Understanding isolation levels prevents data corruption bugs in high-concurrency systems. Banking applications must prevent dirty reads to ensure accurate balances, while other systems might trade isolation for performance.

🛠 Database Reference

Schema Structure (3 Tables)

Accounts Table (10 records)

Transactions Table (30 records)

Audit Log Table (50+ records)

Sample Data Summary

• Total Accounts: 10
  • Checking: 5 accounts ($5K - $15K balance)
  • Savings: 3 accounts ($18K - $42K balance)
  • Business: 2 accounts ($50K - $75K balance)
• Total Transactions: 30
  • Completed: ~23 (76%)
  • Pending: ~3 (10%)
  • Failed: ~2 (7%)
  • Cancelled: ~2 (7%)
• Audit Log: 50+ entries (includes trigger-generated logs)

ACID Properties Explained

🔐 ACID = Data Integrity Guarantee

A - Atomicity
   "All or Nothing"
   - Transaction completes fully or not at all
   - No partial updates
   - Example: Money transfer updates both accounts or neither

C - Consistency
   "Valid State Always"
   - Database enforces all rules and constraints
   - No invalid data states
   - Example: Balance never goes negative (CHECK constraint)

I - Isolation
   "Transactions Don't Interfere"
   - Concurrent transactions don't see each other's changes
   - Prevents race conditions
   - Example: Two withdrawals don't cause overdraft

D - Durability
   "Changes are Permanent"
   - Committed data survives crashes
   - Logged to disk before success
   - Example: Committed transfer survives power outage

Transaction Commands

-- Start transaction
BEGIN TRANSACTION;
BEGIN;  -- Short form

-- Save changes permanently
COMMIT;

-- Undo all changes
ROLLBACK;

-- Create checkpoint within transaction
SAVEPOINT savepoint_name;

-- Rollback to checkpoint
ROLLBACK TO SAVEPOINT savepoint_name;

-- Release checkpoint
RELEASE SAVEPOINT savepoint_name;

-- Lock table (PostgreSQL/MySQL)
LOCK TABLE accounts IN EXCLUSIVE MODE;

-- Lock specific row (PostgreSQL)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Transaction isolation level (PostgreSQL/MySQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

🧪 Testing Your Work

Manual Testing Checklist

• All 7 working queries execute without errors
• TODO 8: Money transfer completes successfully
• TODO 8: Both account balances update correctly
• TODO 8: Transaction recorded in transactions table
• TODO 9: First withdrawal succeeds
• TODO 9: Second withdrawal fails (insufficient funds)
• TODO 9: Account never goes negative
• TODO 10: Documentation covers all three phenomena
• TODO 10: Examples demonstrate isolation behavior
• Audit log captures all changes

Debugging Tips

1. Transaction not committing:

   • Make sure you wrote COMMIT; at the end
   • Check for syntax errors that caused auto-rollback
   • Verify no CHECK constraint violations

2. Balance goes negative:

   • Add CHECK(balance >= 0) constraint
   • Use AND balance >= amount in UPDATE
   • Test insufficient funds scenario

3. Concurrent access issues:

   • Use BEGIN IMMEDIATE (SQLite) for write lock
   • Use SELECT FOR UPDATE (PostgreSQL) to lock rows
   • Check isolation level: PRAGMA read_uncommitted; (SQLite)

4. Audit logs not appearing:

   • Verify triggers were created: .schema accounts (SQLite)
   • Check trigger syntax matches database version
   • Manually query: SELECT * FROM audit_log ORDER BY timestamp DESC;

5. SAVEPOINT not working:

   • SQLite 3.6.8+ required for SAVEPOINT
   • Check nesting: SAVEPOINT names must be unique
   • Must ROLLBACK TO or RELEASE savepoints before COMMIT

Expected Results

🎓 Extension Challenges

Ready for more? Try these bonus features:

Beginner Extensions

• Create deposit function: Accept account and amount, validate, update balance
• Build transfer history view: Show all transfers with account names
• Implement daily transaction limit: Prevent accounts from exceeding $10K/day

Advanced Extensions

• Two-phase commit: Simulate distributed transaction across two databases
• Deadlock detection: Create scenario where two transactions wait for each other
• Optimistic locking: Use version numbers to detect concurrent modifications

Creative Extensions

• Automated reconciliation: Daily job to verify total balance matches transactions
• Fraud detection: Flag suspicious patterns (multiple large withdrawals)
• Interest calculation: Daily job to add interest to savings accounts

📚 Additional Resources

Transaction Learning

• Database Transactions Tutorial
• ACID Properties Explained
• SQLite Transaction Documentation

Isolation Levels

• Understanding Isolation Levels
• Concurrency Control
• Two-Phase Locking

Advanced Topics

• Distributed Transactions: Coordinating across multiple databases
• Event Sourcing: Store all changes as events, not just current state
• Saga Pattern: Long-running transactions in microservices

🔍 Database Syntax Comparison

Transaction Syntax

Isolation Levels

Locking Examples

PostgreSQL (explicit row lock):

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is locked until COMMIT/ROLLBACK
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

MySQL (table lock):

LOCK TABLES accounts WRITE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UNLOCK TABLES;

SQLite (automatic locking):

BEGIN IMMEDIATE TRANSACTION;
-- Entire database is locked for writing
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

🏆 Success Criteria

Your project is complete when:

• ✅ Schema created with triggers and constraints
• ✅ Sample data loaded successfully
• ✅ All 7 working queries execute without errors
• ✅ TODO 8 completed (atomic money transfer)
• ✅ TODO 9 completed (concurrent withdrawal handling)
• ✅ TODO 10 completed (isolation documentation)
• ✅ You understand all four ACID properties
• ✅ You can explain when to use transactions
• ✅ Audit logs capture all database changes

🎉 Congratulations!

Once you complete this activity, you'll have:

• Mastered database transaction management
• Deep understanding of ACID properties
• Experience with concurrency and locking
• Skills to prevent data corruption
• Knowledge of audit trails and compliance

What You've Learned

• Transaction lifecycle (BEGIN, COMMIT, ROLLBACK)
• Atomicity: All-or-nothing operations
• Consistency: Enforcing business rules
• Isolation: Preventing race conditions
• Durability: Surviving system failures
• Practical banking system implementation

These skills are critical for backend development, financial systems, and any application requiring data integrity!

Need Help?

• Review the working queries (1-7) to understand patterns
• Check the hints in each TODO section
• Use the debugging checklist above
• Test transactions one step at a time
• Try ROLLBACK first before COMMIT to test safely

Ready to Submit? Complete this activity and submit through the Activity Submission Form

Happy transacting! 🏦✨