The following bug has been logged on the website: Bug reference: 8290 Logged by: pgnoob Email address: pgn...@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? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs