Practice & Assessment
Test your understanding of Transactions and Concurrency Control
Multiple Choice Questions
5Which ACID property ensures that a committed transaction's changes survive a server crash?
What concurrency problem occurs when Transaction A reads data modified by Transaction B, which then rolls back?
What is MVCC (Multi-Version Concurrency Control)?
Which isolation level prevents dirty reads but allows non-repeatable reads?
What is a deadlock in database concurrency?
Coding Challenges
1Implement Safe Concurrent Operations
Design SQL for a ticket booking system that handles concurrent bookings safely. Tables: events (event_id, name, date, total_seats, booked_seats), bookings (booking_id, event_id, user_id, booking_time, status), payments (payment_id, booking_id, amount, status). Write: (1) A transaction that books a ticket: checks availability, creates the booking, reserves the seat count, and records payment — all atomically. (2) A transaction that cancels a booking: updates booking status, releases the seat, and marks payment as refunded. (3) Demonstrate how SELECT FOR UPDATE prevents overbooking. (4) Show a query that detects any data inconsistency (booked_seats count mismatches actual booking count).
Mini Project
Banking System with Full Transaction Safety
Implement the database layer for a banking application. Tables: accounts (account_id, user_id, account_type, balance, status, created_at), transactions (txn_id, from_account, to_account, amount, txn_type, status, created_at, description), users (user_id, name, email, phone). Implement: (1) A fund transfer transaction with full ACID compliance — debit source, credit destination, log the transaction, all atomically with ROLLBACK on any failure. (2) A stored procedure check_and_transfer that validates: source account exists and is active, sufficient balance exists, destination account exists, amount is positive. (3) An audit trigger that logs every account balance change to a balance_history table with old value, new value, timestamp, and transaction ID. (4) Queries for account statement (all transactions for an account in date range), daily transaction volume report, and detection of suspiciously large transactions (above 3 standard deviations from the account's average).