The following bug has been logged on the website:
Bug reference: 8290
Logged by: pgnoob
Email address: pgnube@gmail.com
PostgreSQL version: 8.4.13
Operating system: CentOS Linux
Description:
I experienced a db deadlock. After tracking down the problem I attributed
it to some unusual locking behavior in postgresql where it acquires locks in
an unexpected way that contributed to the deadlock.
I sent the following information to pgsql-general to ask if it is expected
locking behavior. The only responses that I got said that the behavior is
reproducible on 9.1 and 9.3 beta 2.
Nobody said that this is expected locking behavior and I believe it to be a
bug, so I am filing this bug report.
The exact steps on how to reproduce the problem are shown below. Thank you
for putting together a great DB and for working on this bug report.
I'm using Postgres 8.4.13
I have two tables, call them A & B for example purposes.
Table A, with column id
Table B
- foreign key reference a_id matches A.id FULL
- some other columns blah1, blah2, blah3
I do this:
db1: begin
db2: begin
db1: select A FOR UPDATE
db2: update B set blah1 = 42; --- OK, UPDATE 1
db2: update B set blah2 = 42; --- This blocks waiting for a lock on A!!
Here are the exact steps to reproduce:
CREATE TABLE A (id bigint NOT NULL);
CREATE TABLE B (id bigint NOT NULL, a_id bigint NOT NULL, blah1 bigint,
blah2 bigint, blah3 bigint);
ALTER TABLE ONLY A ADD CONSTRAINT a__pkey PRIMARY KEY (id);
ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id)
MATCH FULL;
INSERT INTO A VALUES (1);
INSERT INTO B VALUES (1, 1, 1, 2, 3);
Now, in two DB connections, CON1 and CON2.
CON1:
BEGIN;
SELECT * FROM A WHERE id = 1 FOR UPDATE;
CON2:
BEGIN;
UPDATE B SET blah1 = 42 WHERE id = 1;
UPDATE B SET blah2 = 42 WHERE id = 1; -- this blocks
I have verified that if I drop the foreign key constraint requiring B.a_id
match A.id
that this behaviour does not happen and both updates succeed without
blocking.
I can perhaps understand why it acquires a shared lock on A when updating B
because of
the foreign key reference, even though it doesn't seem like it should
require it because
the columns being updated are not relevant to the foreign key constraint.
That behaviour would be less than ideal but at least it would be
understandable.
However, why does it only try to acquire the lock on the second update????
If I do a ROLLBACK in CON1, then I see CON2 finish the UPDATE and it
acquires a
lock on table A. Why?
