Hello.
I have a number of deadlock because of the foreign key constraint:
Assume we have 2 tables: A and B. Table A has a field fk referenced to
B.id as a foreign key constraint.
-- transaction #1
BEGIN;
...
INSERT INTO A(x, y, fk) VALUES (1, 2, 666);
...
END;
-- transaction #2
BEGIN;
UPDATE B SET z = z + 1 WHERE id = 666;
...
UPDATE B SET z = z + 1 WHERE id = 666;
...
UPDATE B SET z = z + 1 WHERE id = 666;
END;
You see, table A is only inserted, and table B is only updated their field z on its single row.
If we execute a lot of these transactions concurrently using multiple parellel threads, sometimes we have a deadlock:
DETAIL: Process 6867 waits for ShareLock on transaction 1259392; blocked by process 30444.
Process 30444 waits for ShareLock on transaction 1259387; blocked by process 6867.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE "id" = $1 FOR SHARE OF x"
If I delete the foreign key constraint, all begins to work fine.
Seems Postgres thinks that "UPDATE B SET z = z + 1 WHERE id = 666" query may modify
B.id field and touch
A.fk, so it holds the shareable lock on it.
The question is: is it possible to KEEP this foreign key constraint, but avoid deadlocks?