Preventing Overbooked Workshops is a PostgreSQL project for learners who can already read and update simple tables. Make real workshop reservations, reproduce two confirmations for one place, and deliver reusable SQL that either books an entire group or changes nothing.
Two Guided Labs explain the normal booking flow, real lock waits, transaction recovery and a saved atomic booking statement. One independent Challenge changes the activities, group sizes and desk order. Each unit starts with its own prepared data; the final work is your SQL and the actual booking ledger.
What You Will Learn
- Connect each confirmed group to its exact seat count and remaining capacity.
- Reproduce a stale availability decision in two real database connections.
- Make a conditional claim and use its RETURNING rows to create a confirmation.
- Observe a waiting request while another workshop continues accepting bookings.
- Distinguish insufficient places from an SQL error, and verify that a failed confirmation rolls back its claim.
- Recover an aborted transaction and preserve original bookings, workshop details and prepared tools.
Who This Course Is For
This course suits SQL learners and beginning application developers who want to understand a concrete concurrency problem through real database work.
Prerequisites: Basic terminal navigation and PostgreSQL connections, SELECT, WHERE, INSERT and UPDATE. Transactions, row locks, psql variables, integer casts and data-modifying common table expressions are explained before use.
Learning environment: PostgreSQL and synthetic workshop data are ready in your browser. Workshop Desk provides the shell; Desk A and Desk B provide separate native psql connections to the same database on one machine. You write SQL without building a Python program or website.
Frequently Asked Questions
Do I need to understand database locking already?
No. Start with a normal booking, then observe a controlled overbooking and an actual lock wait before applying the safe method. The course assumes the basic SQL prerequisites above.
What do I deliver?
A reusable reserve.sql file and real whole-group confirmations. For each workshop, booked seats plus remaining places must equal its capacity. Practical checks also preserve original records and exercise your unchanged SQL against different isolated data.
Does this cover a complete production booking platform?
No. You practice READ COMMITTED transactions in one PostgreSQL database. Payments, automatic retries, expiring holds and distributed coordination are outside this project. The Challenge independently combines the mechanisms taught in the Guided Labs.
