This is the effect of inserting or updating a row in a table that has a foreign key. See the following example:
CREATE TABLE a (id integer PRIMARY KEY);
CREATE TABLE b (id integer REFERENCES a);
INSERT INTO a VALUES (1);
Now in one session, start a transaction and lock the row in a
:
BEGIN;
SELECT id FROM a FOR UPDATE;
id
════
1
(1 row)
Then, in another session, try to insert a row into b
:
INSERT INTO b VALUES (1); -- hangs!
What happened?
The SELECT ... FOR UPDATE
took a FOR UPDATE
lock on the row in a
.
The INSERT
has to make sure that no concurrent transaction can delete the referenced row in a
(or modify any of its key columns) to avoid inconsistencies.
For that purpose, it runs
SELECT 1
FROM ONLY "public"."a" x
WHERE "id" OPERATOR(pg_catalog.=) 1
FOR KEY SHARE OF x;
Here ONLY
makes sure that no other tables are affected if a
is part of an inheritance hierarchy, and OPERATOR(pg_catalog.=)
is PostgreSQL syntax for a schema-qualified operator (since operators are also subject to search_path
, PostgreSQL has to make sure that the correct =
operator is used).
Since FOR UPDATE
and FOR KEY SHARE
locks conflict (see the documentation), the second session is blocked.
You can avoid that block by using SELECT ... FOR NO KEY UPDATE
instead of SELECT ... FOR UPDATE
. The latter is only needed if you plan to update a primary or unique key column or if you intend to delete the row.