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.idas 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?