Practice & Assessment
Test your understanding of Transactions, Concurrency, and Locking
Multiple Choice Questions
5What happens to a SAVEPOINT if the outer transaction is rolled back?
You run the same SELECT twice in one transaction under READ COMMITTED and get different results. Why?
What does SELECT FOR UPDATE SKIP LOCKED do in a job queue implementation?
What does VACUUM do in PostgreSQL that DROP TABLE does not require?
How does PostgreSQL resolve a deadlock?
Coding Challenges
1Safe Bank Transfer with Transactions
Write a SQL transaction that transfers funds between two bank accounts. Tables: accounts (id, owner_name, balance NUMERIC). The transfer must: (1) Lock both rows using SELECT FOR UPDATE with consistent ascending ID order to prevent deadlocks, (2) Verify the source account has sufficient balance, raising an error if not, (3) Deduct from source and add to destination in the same transaction, (4) Log the transfer in a transfers table (from_id, to_id, amount, transferred_at). If any step fails, the entire transaction must roll back. Inputs: source_id, dest_id, transfer_amount. Time estimate: 25 minutes.
Mini Project
Concurrent Job Queue System
Build a PostgreSQL-backed job queue that supports concurrent workers. Create a jobs table with columns: id (identity PK), type TEXT, payload JSONB, status TEXT CHECK IN ('pending','processing','done','failed'), attempts INT DEFAULT 0, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ, locked_by TEXT. Write five SQL operations: (1) Enqueue a job (INSERT). (2) Dequeue the next available job using SELECT FOR UPDATE SKIP LOCKED, updating status to 'processing' and locked_by to a worker ID — wrapped in a transaction. (3) Mark a job complete (UPDATE to 'done'). (4) Mark a job failed and increment attempts. (5) Requeue stuck jobs where status='processing' and updated_at < now()-interval '5 minutes'. Add appropriate indexes. Write a query using pg_locks to show any currently waiting locks. Document the isolation level choice for each operation.