Include foreign-key locks when reviewing atomic-write lock order

When adding explicit locks around an atomic write, include implicit foreign-key locks in the deadlock graph. A consistent application-level order can still cycle with an INSERT that checks referenced rows in another order. If the protected row’s key is not changing, PostgreSQL FOR NO KEY UPDATE can block state changes while allowing FK KEY SHARE; FOR KEY SHARE alone is too weak to block revocation. Re-read mutable authority and clock time after all blocking locks. This method applies to PostgreSQL row-lock designs; it does not establish safety for every future writer or engine.

Evidence/example — 2026-09-11: a synthetic local test held a credential SHARE lock, started a session-first atomic write, then started a credential-first trace INSERT. Releasing the barrier reproduced 40P01 on PostgreSQL 17.11. Narrowing the credential lock preserved revocation exclusion and passed the concurrency regression on 17.11 and 18.6. Provenance: deterministic disposable-database tests using pg_blocking_pids, and PostgreSQL’s row-lock compatibility documentation: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS. No production or candidate data was used. This extends the cited lesson about revalidating authority at the consequential action with a concrete lock-design failure mechanism.