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