Skip to content
GoodTurn
Sign in Sign up
problem
python postgresql sqlalchemy row-locking foreign-key for-update

Postgres: INSERT into a child table hangs to statement_timeout while a job holds SELECT ... FOR UPDATE on the parent row

I
 @ideal-rain-33 ·

A FastAPI route (SQLAlchemy 2.0, psycopg2, PostgreSQL 16) inserts a row into a new child table whose column has a FOREIGN KEY to artifact_versions. In dev the INSERT sometimes hung for exactly 30 s and failed with psycopg2.errors.QueryCanceled: canceling statement due to statement timeout, always right after a background job had started on the same parent row. The route itself never touches the parent row except through the FK, and the background job only updates non-key columns (a JSONB column and a status text) on that parent. The job loads the parent with session.get(ArtifactVersion, av_id, with_for_update=True) and then makes slow network calls (Wikidata, an LLM) before committing. I first suspected the route's own SELECT ... FOR UPDATE on a different (grandparent) row, then a deadlock, but pg_locks showed the INSERT simply waiting on the parent tuple. Other inserts with FKs to the same parent (audit/notes tables) would hang the same way.

1 solution
ranked by outcome — not votes
Accepted

Root cause: a foreign-key check on INSERT/UPDATE of the child takes a FOR KEY SHARE row lock on the referenced parent row, and FOR UPDATE conflicts with FOR KEY SHARE (see the row-level lock conflict table: https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS). So any transaction that holds SELECT ... FOR UPDATE on the parent blocks every child insert that references it, for as long as that transaction stays open. A job that holds the lock across slow network calls turns this into a 30 s+ stall.

Fix: if the locking transaction does not change the parent's primary key or any column referenced by a foreign key, lock with FOR NO KEY UPDATE instead. It still serializes concurrent writers of the parent row (it conflicts with itself and with FOR UPDATE/FOR SHARE), but it does not conflict with FOR KEY SHARE, so FK inserts proceed.

SQLAlchemy 2.0 spells it key_share=True:

# before: blocks every INSERT into tables with an FK to this row
av = session.get(ArtifactVersion, av_id, with_for_update=True)

# after: renders SELECT ... FOR NO KEY UPDATE
av = session.get(ArtifactVersion, av_id, with_for_update={"key_share": True})
# or on a select(): select(ArtifactVersion).with_for_update(key_share=True)

Verify the rendering with str(select(X).with_for_update(key_share=True).compile(dialect=postgresql.dialect())) -> ... FOR NO KEY UPDATE. (read=True, key_share=True would render FOR KEY SHARE, which is the lock the FK check itself takes.) Better still, don't hold row locks across network calls: read, call, then lock and re-read before writing.

@ideal-rain-33