A PostgreSQL advisory lock for concurrent content initialization
Content initialization is one ordered pipeline: create tables, correct the schema, seed static data, and upsert notes and courses. Even idempotent steps can observe an intermediate state when an operator double-clicks o…
Table of contents
Content initialization is one ordered pipeline: create tables, correct the schema, seed static data, and upsert notes and courses. Even idempotent steps can observe an intermediate state when an operator double-clicks or two console replicas run the pipeline together.
Server concurrency contract
POST /api/db/init first calls pg_try_advisory_lock. If another session owns the lock, it returns 409 INIT_IN_PROGRESS without touching the database. The lock session stays alive for the whole callback, then runs pg_advisory_unlock and releases the client on success, failure, or exception.
State the operator can understand
- Verify that only the first request acquires the lock.
- Verify that an overlapping request returns
409without writing seed rows. - Verify lock release on both success and exception paths.
Disabling a UI button is only a usability aid, not a concurrency or security contract. Show the server state in the role=status region and explain 409 as “another initialization is running; retry after it finishes,” rather than hiding it as an unknown failure.
Contention decision table
| Situation | Server behavior | UI message | Retry |
|---|---|---|---|
| Lock acquired | Start and record each stage | In progress | Not before completion |
| Lock already held | 409 INIT_IN_PROGRESS, no writes |
Another operation is running | Later |
| Stage fails | Roll back and return the stage | No change completed | After fixing the cause |
| Unlock fails | Emit an operational warning | Check state before another run | After ownership check |
SELECT pg_try_advisory_lock($1); -- return 409 when false
-- run the whole initialization on this session
SELECT pg_advisory_unlock($1); -- execute in finally
Do not return a client that owns a session-level lock to the pool. Keep the same client from acquisition through unlock so another request cannot inherit its lock state.
Related course: Expand/Contract and idempotent backfills