Banking System Challenge

Welcome to the Concept 11: Transactions and Concurrency challenge!

Your Mission: Fix the Broken Bank! 🏦💔

TelebortBank's system has a critical bug - money is disappearing during transfers! Your mission is to diagnose the problem, experience the chaos firsthand, and then implement a bulletproof solution using transactions.

Challenge Overview

In this hands-on challenge, you will:

1. Witness the disaster - Run concurrent transfers and watch money vanish
2. Diagnose the problem - Understand why race conditions occur
3. Implement the fix - Use transactions to make transfers atomic
4. Stress test your solution - Prove your bank is now unbreakable

Part One: Set Up Your Banking System

First, create your test environment with the provided SQL scripts from the lesson. You'll need:

• The BankDB database
• The accounts table with test data
• The unsafe_transfer procedure (the buggy one!)
• The safe_transfer procedure (your solution)

Part 2: The Challenge Tasks

Task One: Reproduce the Bug 🐛

Using two separate MySQL sessions, execute simultaneous transfers from the same account. Document:

• The initial balance
• The transfers you attempted
• The final balance
• How much money "disappeared"

Task 2: Implement Safety Measures 🛡️

Create your own version of a safe transfer procedure that:

• Uses proper transaction boundaries
• Implements row-level locking
• Handles insufficient funds gracefully
• Records all transfers in the history table

Task 3: Stress Test Your Solution 💪

Run the stress test procedure with 100+ concurrent transfers. Your bank should:

• Never allow negative balances
• Never lose or create money
• Handle all transfers atomically
• Maintain a complete audit trail

Task 4: Advanced Challenge - Deadlock Detective 🔍

Create a scenario that causes a deadlock between two transactions. Then:

• Document how the deadlock occurs
• Implement a solution to prevent it
• Test your deadlock prevention strategy

Submission Requirements

Complete the following and document your work:

1. Bug Report: Screenshot showing money disappearing during concurrent transfers
2. Your Safe Transfer Code: Your implementation of the safe transfer procedure
3. Stress Test Results: Evidence that your solution handles 100+ concurrent transfers
4. Deadlock Analysis: Explanation of how you created and prevented a deadlock
5. Performance Metrics: Compare the speed of safe vs unsafe transfers

Bonus Challenges 🌟

Bonus 1: Multi-Currency Support

Extend your banking system to handle multiple currencies with atomic exchange rate conversions.

Bonus 2: Scheduled Transfers

Implement a system for scheduling future transfers that maintains ACID properties.

Bonus 3: Transaction Rollback History

Create a system that can show what would have happened if a transaction was rolled back.

Evaluation Criteria

Your solution will be evaluated on:

• Correctness: No money is lost or created
• Concurrency: Handles simultaneous access properly
• Performance: Efficient use of locks and indexes
• Error Handling: Graceful handling of edge cases
• Code Quality: Clear, well-commented implementation

Resources

• Review the lesson materials on ACID properties
• MySQL documentation on transactions and locking
• The provided safe_transfer procedure as a reference

Ready to Save the Bank? 🚀

Your code will be handling millions of dollars in transfers. Make it bulletproof!

✅ Submission Instructions:

• Complete all tasks in your MySQL environment
• Document your solutions with screenshots and explanations
• Submit your work through the form below

🔗 Access the Original Worksheet

🔗 Submit Your Banking System Solution