What
create_spec (via MCP and the dashboard) could hang indefinitely — validation passed, then the create wedged server-side and MCP clients timed out. This bounds every spec write so it fails fast instead of hanging.
Root cause
create_spec acquires the gapless-numbering advisory lock with the blocking pg_advisory_xact_lock (Hive.Specs.lock_specs_for_numbering/0), which has no timeout. The earlier lock hardening only landed as global connection parameters in config/runtime.exs — TCP keepalives + idle_in_transaction_session_timeout: 60s, but no lock_timeout.
idle_in_transaction_session_timeout only rolls back a holder that goes idle in transaction. A holder that is active — itself blocked waiting on another lock — is never cleared, so it wedges every subsequent create forever (matching the observed >1-day persistence: reads fine, create hangs). lock_timeout is the only bound that also covers pg_advisory_xact_lock acquisition.
The fix
- Add
Hive.Specs.write_transaction/1, which wraps create/update in a transaction that issues SET LOCAL lock_timeout = 10000 (bounds both the advisory lock and the optimistic-lock row UPDATE) and rescues Postgrex :lock_not_available (SQLSTATE 55P03) into {:error, :locked}.
- Route
create_spec/update_spec through it.
- Surface
{:error, :locked} in the MCP create_spec/update_spec tools ({"error":"locked","message":"…try again…"}) and the new/edit/show dashboard LiveViews (retry flash) instead of crashing.
Tests
- Two new Mimic-based contract tests (
create_spec_test, update_spec_test) prove the tools return locked rather than crash when a write is contended.
mix compile --warnings-as-errors, mix format, mix credo clean; 159 spec/LiveView/MCP-tool tests pass.
Reviewer notes
- Deploying also clears any current wedge: the BEAM restart drops the stuck DB connection and releases its locks, so the existing jam clears on deploy and
lock_timeout prevents recurrence.
- The advisory-lock numbering is kept intentionally (gapless numbers — the sequence was dropped in migration
20260618000000); this only bounds the wait, it doesn’t change numbering semantics.
- Out of scope / follow-up:
Hive.Drops numbering (lock_drops_for_numbering/0) uses the same unbounded pg_advisory_xact_lock and has the identical latent bug. Left untouched here to keep the change tight; worth the same treatment.
GA