Deadlocks caused by a foreign key constraint

From: "Dmitry Koterov" <dmitry(at)koterov(dot)ru>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: Deadlocks caused by a foreign key constraint
Date: 2007-08-10 17:38:36
Message-ID: d7df81620708101038k772a2cderb52bb09f5440bd1b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma Jr 2007-08-10 17:40:04 Re: Timestamp in pg_dump output, was Re: How I can know a back up database is up to date
Previous Message Marc Rossi 2007-08-10 17:35:59 Re: UPDATES hang every 5 minutes