I85PREP
⌘ K
SQU IT Exit study materials
SQU IT Exit study resources

Fourteen Double Bookings at the Clinic Desk

SQU IT Exit · Put your knowledge to work in context.

All case studies

Fourteen Double Bookings at the Clinic Desk

Kitale Polyclinic books appointments through a small PHP front end over PostgreSQL 13. Two receptionists work the same desk, and doctors' slots are 20 minutes long. In the last month the clinic recorded 14 double bookings, all created within about two seconds of each other by the two receptionists. The booking code runs SELECT count(*) FROM appointments WHERE doctor_id = $1 AND slot = $2 AND status <> 'cancelled', and if the count is zero it inserts a row. The connection is left at the default READ COMMITTED. Reception also has a dashboard tile that reads a free_slots counter rebuilt by a nightly cron job; on Monday it advertised 12 free slots that were already taken, and three patients were sent home. Fourteen duplicate rows already exist in the table, some carrying the only clinical notes for that visit, and the practice manager wants the constraint added today without losing those notes. The clinic runs 24 hours and cannot accept a maintenance window longer than five minutes.

1. Explain precisely how two receptionists produce a double booking at READ COMMITTED, then give two remedies with their trade-offs.

Read the worked answer

Mechanism. Neither transaction sees the other's uncommitted row, and SELECT count(*) takes no lock that would block a concurrent insert. Receptionist A and B both run the SELECT, both get 0, both insert, both commit. READ COMMITTED guarantees only that each statement sees a snapshot taken at its own start; it does not stop a row appearing between the check and the write. Check-then-act across two statements is never safe without something that serialises the pair.

Remedy 1: a unique partial index plus error handling. CREATE UNIQUE INDEX CONCURRENTLY appointments_doctor_slot_uq ON appointments (doctor_id, slot) WHERE status <> 'cancelled'; The database now refuses the second row whatever the application code does. Trade-offs: the application must catch SQLSTATE 23505 and show 'that slot was just taken, please choose another', otherwise the second receptionist sees a 500. Because the index is partial, a cancelled slot can be rebooked; and it enforces the rule only over the indexed columns, so a future 'tentative' status needs a matching index.

Remedy 2: SERIALIZABLE with retry. Set the transaction to SERIALIZABLE and retry on SQLSTATE 40001. This is correct and general, but every query must be inside the transaction, every code path needs a bounded retry loop, and at desk-level concurrency the receptionists will see aborts that look like random failures.

A cheaper option worth naming: pg_advisory_xact_lock keyed on doctor and slot before the count serialises only the contending pair with no retries, but it works only if every writer remembers to take the lock, which is its weakness.

2. The free_slots tile advertised 12 slots that were already taken. Diagnose the cause and propose a fix, stating what must be corrected first.

Read the worked answer

Diagnosis. free_slots is a derived value, materialised once a night. Three things make it wrong and one hides the error:

  • The cron computes the figure at 02:00 and nothing invalidates it afterwards, so any booking, cancellation or reschedule during the day is invisible. On Monday it advertised 12 slots that had been taken since Sunday night.
  • It inherits bad data: the 14 double bookings each consume capacity the counter may have counted only once.
  • When the job fails, it fails silently, and yesterday's number is served as though it were today's.

Fix. Do not store a count you can compute. Serve the tile from a query or a view over appointments with an index on (doctor_id, date), so the number is true at read time; a 20-doctor day is a trivial query. If the dashboard is genuinely heavy at peak, make the cache explicit and short-lived (60 seconds, recomputed on read) rather than a nightly batch, and add a monitor that compares cached and live counts and alerts on any divergence above zero. The failure mode to design out is the silent stale number, not the slow query.

Order of work. The duplicate fix comes first. Otherwise the tile is only as correct as the table it summarises, and the team will be tempted to correct the display instead of the data.

3. Write the migration plan for adding the constraint given the 14 existing duplicates and a five-minute window.

Read the worked answer

The DDL must not hold an exclusive lock over a live booking table, so almost all the work happens outside the window.

Before the window (no lock needed):

  1. Identify the duplicate groups: group by (doctor_id, slot), filter status <> 'cancelled', keep groups where count(*) > 1.
  2. Decide the survivor per group: the earliest booking, unless the later row holds the only clinical notes, in which case keep the earlier booking's identity and merge the notes into it. Write the rule down and have the practice manager confirm it, because the rule decides which patient keeps the appointment.
  3. Build a data-fix script that appends the losing rows' notes to the survivor and sets them to status='cancelled', cancelled_reason='duplicate_merged'. Never delete: the audit trail is the defence if a patient complains.
  4. Rehearse on a restored copy, then verify the duplicate query returns zero rows.

In the window: 5. Deploy the application change that catches 23505 first; before the index exists it is harmless. 6. Run CREATE UNIQUE INDEX CONCURRENTLY. It does not block writes and may outlast the window. If it fails it leaves an INVALID index, which must be dropped and rebuilt rather than assumed to be working.

After: confirm the index is valid, re-run the duplicate query, and watch the application log for 23505 in the first hour. A burst means some receptionist flow is still doing check-then-insert.