Introduction
Two desks sell places at a craft workshop. A guest expects a confirmed place, not a counter that merely looks nonnegative. You will first make a normal group booking, then reproduce two confirmations for one place on a disposable practice workshop. Finally, you will protect that place and let another workshop keep serving guests.
You need basic terminal navigation and PostgreSQL SELECT, WHERE, INSERT and UPDATE. Transactions, connection identity, row locks, RETURNING and recovery are explained here. PostgreSQL and synthetic data are prepared. Workshop Desk is a shell; Desk A and Desk B are separate native psql connections to the same database on one machine. Keep all three tabs open. Text and runtime labels are English. Reading and ordinary mistakes are included in the author's estimated duration; this is not a novice timing study.
Only add the named trial bookings and update their workshops' places_left. The race practice explicitly permits deleting your own trial bookings. Preserve original bookings, workshop IDs, titles, capacities, notes, table definitions and prepared tools. Do not change server settings or create tables. This exercise uses PostgreSQL's READ COMMITTED isolation on one database; payments, retries and multi-workshop deadlocks are outside its scope.
Make a Normal Group Booking
In this step, you will connect a real group confirmation to the number of places it consumes.
Start in Workshop Desk, at the shell. ./ runs a program from the current folder. desk status reads the actual tables; it does not repair them. Its first result lists workshop_id|title|capacity|places_left|booked_seats. The next lists request_code|workshop_id|seats|desk. The vertical bars separate fields.
./desk status
Workshop 101 has capacity 5, one original booked seat and 4 places left. Workshop 102 has one place and no bookings. Keep original-family on workshop 101 exactly as it is.
Switch to Desk A. Its Desk A workshops=> prompt means you are inside PostgreSQL's psql client, connected to database workshops. Enter SQL here, not shell commands. A semicolon finishes a SQL statement. A connection is a conversation with the database server; pg_backend_pid() identifies its server process. A different value in Desk B will identify an independent connection.
SELECT pg_backend_pid(), current_user;
The user is workshop_write; the process ID varies. BEGIN starts a transaction: a group of changes that become visible together at COMMIT. Until then, another connection cannot see your new booking. ROLLBACK discards uncommitted changes. In this first example, no other desk is selling workshop 101.
RETURNING displays the rows actually changed by UPDATE. Subtract 2 places, and insert the matching two-seat booking in the same transaction. Text uses single quotes; the request code uniquely identifies this booking.
BEGIN;
UPDATE workshops
SET places_left = places_left - 2
WHERE workshop_id = 101
RETURNING workshop_id, places_left;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('normal-group', 101, 2, 'A');
The update returns 101 | 2. The prompt contains *, indicating an open transaction. Switch to Workshop Desk and inspect the committed ledger:
./desk status
It still shows 4 places and only the original booking for workshop 101. Switch back to Desk A and finish:
COMMIT;
Switch to Workshop Desk:
./desk status
Now 101 has 2 places left and 3 booked seats: the original one plus your group of two. Its actual booking is normal-group|101|2|A. A successful transaction preserves capacity = places_left + booked_seats. The first check reads these business facts; it does not infer how you typed them.
If you mistype SQL inside a transaction, the prompt can change to !. The transaction is then aborted: further SQL will fail until ROLLBACK;. Roll back in that same desk, inspect ./desk status in Workshop Desk, and retry only work that did not commit. Never repeat an already committed request blindly. A request-code conflict is an error, not a second confirmation.
Reproduce a Stale Availability Decision
In this step, you will deliberately produce an invalid practice ledger, observe why a nonnegative counter is insufficient, and keep the damage confined to workshop 102.
Switch to Desk A. This experiment deliberately writes a stale number instead of calculating a conditional claim. Do not use it as the final booking method. The isolation level READ COMMITTED gives each statement a view of committed data when that statement begins; an earlier SELECT does not reserve its result.
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT workshop_id, places_left FROM workshops WHERE workshop_id = 102;
Switch to Desk B. Confirm that its process ID differs from Desk A's, then start its own transaction and read the same place:
SELECT pg_backend_pid(), current_user;
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT workshop_id, places_left FROM workshops WHERE workshop_id = 102;
Both SELECTs return one place. Neither SELECT has reserved it. Switch to Desk A and save a decision based on that earlier number:
UPDATE workshops SET places_left = 0 WHERE workshop_id = 102;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('unsafe-A', 102, 1, 'A');
Leave A's transaction open. Switch to Desk B and enter only this UPDATE:
UPDATE workshops SET places_left = 0 WHERE workshop_id = 102;
The prompt does not return yet. That is an expected lock wait, not a broken connection. A has changed this row and holds its row lock until the transaction ends. Switch to Workshop Desk:
./desk waits
The actual Desk B backend has wait_event_type Lock, and blocked_by contains A's process ID. The IDs vary. The observer only reads PostgreSQL's activity; no animation or timer supplies this state.
Switch to Desk A:
COMMIT;
Switch to Desk B. Its UPDATE now finishes. It can still set the already-zero balance to zero because its WHERE condition only names the workshop. Add its confirmation and commit:
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('unsafe-B', 102, 1, 'B');
COMMIT;
Switch to Workshop Desk:
./desk status
Workshop 102 now has capacity 1, places left 0 and 2 booked seats. Both unsafe-A and unsafe-B exist. The balance satisfied the database's nonnegative check, yet one guest has no place. Waiting alone did not fix the stale business decision. The next step removes only these explicitly disposable trials and replaces the decision. The step check accepts this practice state or the correctly repaired final state; it does not use terminal history as proof of your investigation.
Claim the Place Before Confirming It
In this step, you will require an actual conditional claim before writing a confirmation, and recover a cancelled waiting request.
Switch to Desk A. DELETE removes only rows selected by its WHERE condition. IN names the two disposable request codes; do not delete the original or normal booking. Restore only practice workshop 102, in the same transaction:
BEGIN;
DELETE FROM bookings WHERE request_code IN ('unsafe-A', 'unsafe-B');
UPDATE workshops SET places_left = 1 WHERE workshop_id = 102;
COMMIT;
First practice cancelling a lock wait on the separate workshop 104. A will release its change with ROLLBACK, so this trial creates no booking:
BEGIN;
UPDATE workshops SET places_left = 0 WHERE workshop_id = 104;
Switch to Desk B:
BEGIN;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 104 AND places_left >= 1
RETURNING workshop_id;
While B waits, press Ctrl+C in Desk B. PostgreSQL reports canceling statement due to user request, with SQLSTATE 57014. SQLSTATE is PostgreSQL's five-character error category. A cancelled statement leaves this explicit transaction aborted. Demonstrate the distinction from an ordinary empty result:
SELECT 1;
This is an expected error: current transaction is aborted, SQLSTATE 25P02. Recover in Desk B:
ROLLBACK;
Switch to Desk A and release its practice change:
ROLLBACK;
Now protect the real last place in workshop 102. The WHERE condition checks sufficient places as part of the UPDATE itself. After waiting for a competing update, READ COMMITTED rechecks this condition against the updated row. RETURNING tells you whether this transaction actually claimed the place.
In Desk A:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 102 AND places_left >= 1
RETURNING workshop_id, places_left;
A receives 102 | 0: it claimed the place. Only because a row was returned, create its matching confirmation:
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('safe-last-A', 102, 1, 'A');
Leave it uncommitted. Switch to Desk B:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 102 AND places_left >= 1
RETURNING workshop_id, places_left;
B waits. Switch to Workshop Desk and read the actual wait:
./desk waits
A row lock should not stop unrelated workshops. Still in Workshop Desk, open a short third connection. ./db --desk A uses the same prepared database login; this temporary shell invocation is independent of the already-open Desk A tab. The quoted here-document sends all lines up to the closing SQL to this client and then closes it; keep the closing marker alone on its line. The C stored in the booking records this third request.
./db --desk A <<'SQL'
BEGIN;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 103 AND places_left >= 1
RETURNING workshop_id, places_left;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('other-workshop', 103, 1, 'C');
COMMIT;
SQL
It returns 103 | 2 and commits while workshop 102 is still held. Switch to Desk A:
COMMIT;
Switch to Desk B. Its UPDATE now returns zero rows and UPDATE 0: the new zero balance fails places_left >= 1. This is a successful SQL statement with no claim, not an SQL error. Do not insert a confirmation. End B's transaction:
ROLLBACK;
Switch to Workshop Desk:
./desk status
Workshop 102 has one booking, safe-last-A|102|1|A, and zero places. Workshop 103 has other-workshop|103|1|C and two places. Workshop 104 still has its original one place and no booking; 101 retains its normal group and original family. Every workshop now satisfies the balance equation.
For an ordinary mistaken new trial confirmation, first ROLLBACK any open transaction in that desk. In this practice environment you may remove only your own mistaken code and recalculate only its workshop balance in one transaction. For example, if you accidentally committed typo-trial on workshop 103, use the following in Desk A, then inspect status before retrying the intended request. The subquery sums actual booked seats; COALESCE(..., 0) uses zero when no bookings remain. This is explicit practice recovery, not part of the booking algorithm:
BEGIN;
DELETE FROM bookings WHERE request_code = 'typo-trial' AND workshop_id = 103;
UPDATE workshops
SET places_left = capacity -
(SELECT COALESCE(SUM(seats), 0) FROM bookings WHERE workshop_id = 103)
WHERE workshop_id = 103;
COMMIT;
The example makes no change when you have not created that mistake. Never use this to delete an original or legitimate requested booking. The next lab links claim and confirmation in one reusable statement, so the caller cannot accidentally insert after an empty claim.
Summary
You created a real normal booking, reproduced a stale-read overbooking and protected the last place with a conditional UPDATE and a confirmation in the same transaction. Native lock observations showed a waiting competing desk while another workshop continued. You also cancelled a waiting statement and recovered its aborted transaction with ROLLBACK. Your final ledger preserves original bookings and matches every confirmed seat to the remaining capacity.
