Обсуждение: BUG #8290: broken/unexpected locking behavior
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?
On Tue, Jul 9, 2013 at 9:02 AM, <pgnube@gmail.com> wrote: > 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. > > > ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) > MATCH FULL; > Try those steps again with the FK "DEFERRABLE INITIALLY DEFERRED" Eg. ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) MATCH FULL deferrable initially deferred;
Doing that just moves the problem from the time of the UPDATE to the time of the COMMIT. It is still possible to get a deadlock and I'm not sure how making it deferrable helps in this case. You can still end up with a deadlock like this: CON1: BEGIN; CON1: SELECT * FROM A WHERE id = 1 FOR UPDATE; CON2: BEGIN; CON2: UPDATE B SET blah1 = 42 WHERE id = 1; -- OK, UPDATE1 CON1: UPDATE B SET blah3 = 42 WHERE id = 1; -- blocks because of the transaction in CON2 CON2: UPDATE B SET blah2 = 42 WHERE id = 1; -- OK, UPDATE1 CON2: COMMIT; -- causes deadlock ERROR: deadlock detected On Tue, Jul 9, 2013 at 12:57 PM, bricklen <bricklen@gmail.com> wrote: > > On Tue, Jul 9, 2013 at 9:02 AM, <pgnube@gmail.com> wrote: > >> 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. >> >> >> ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) >> MATCH FULL; >> > > Try those steps again with the FK "DEFERRABLE INITIALLY DEFERRED" > Eg. > ALTER TABLE B ADD CONSTRAINT fkrefa FOREIGN KEY (a_id) REFERENCES A(id) > MATCH FULL deferrable initially deferred; > >
pgnube@gmail.com escribió: > 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. See here: http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/ There are further posts on the same topic in that blog. The patch dealing with it was finally committed for the 9.3 version, due to be released later this year: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182 At the bottom of the commit messages there are some message-ids on (rather long) discussions about that patch. You can search for them at http://www.postgresql.org/list/ (just enter the msgid in the box and click search). If you try a 9.3 snapshot, you should be able to replace the FOR UPDATE in your queries with FOR NO KEY UPDATE and there should be no deadlock. Even if it does not, I hope the aforementioned posts explain what is going on. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thank you Alvaro. Yes, this explains it. It doesn't help to fix it but at least I know now that it's a known "featur= e". I'll have to see about coming up with a work-around as we likely won't get = to 9.3 anytime soon. Thank you! -----Original Message----- From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owner@postgresql.o= rg] On Behalf Of Alvaro Herrera Sent: Tuesday, July 09, 2013 2:59 PM To: pgnube@gmail.com Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #8290: broken/unexpected locking behavior pgnube@gmail.com escribi=F3: > I sent the following information to pgsql-general to ask if it is=20 > expected locking behavior. The only responses that I got said that=20 > 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=20 > be a bug, so I am filing this bug report. > The exact steps on how to reproduce the problem are shown below. =20 > Thank you for putting together a great DB and for working on this bug rep= ort. See here: http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_ke= y_deadlocks/ There are further posts on the same topic in that blog. The patch dealing = with it was finally committed for the 9.3 version, due to be released later= this year: http://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dcommitdiff;h=3D0ac= 5ad5134f2769ccbaefec73844f8504c4d6182 At the bottom of the commit messages there are some message-ids on (rather = long) discussions about that patch. You can search for them at http://www.= postgresql.org/list/ (just enter the msgid in the box and click search). If you try a 9.3 snapshot, you should be able to replace the FOR UPDATE in = your queries with FOR NO KEY UPDATE and there should be no deadlock. Even if it does not, I hope the aforementioned posts explain what is going = on. --=20 =C1lvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make change= s to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Jamey Poirier escribió: > > Thank you Alvaro. Yes, this explains it. > It doesn't help to fix it but at least I know now that it's a known "feature". > I'll have to see about coming up with a work-around as we likely won't get to 9.3 anytime soon. Perhaps you can use FOR SHARE instead of FOR UPDATE in the first connection, for instance .. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
pg noob escribió: > Alvaro, > > Is there a PostgreSQL bug number that I could refer to for this problem? #8290 ? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro, Is there a PostgreSQL bug number that I could refer to for this problem? Thank you. On Tue, Jul 9, 2013 at 4:53 PM, Alvaro Herrera <alvherre@2ndquadrant.com>wr= ote: > Jamey Poirier escribi=F3: > > > > Thank you Alvaro. Yes, this explains it. > > It doesn't help to fix it but at least I know now that it's a known > "feature". > > I'll have to see about coming up with a work-around as we likely won't > get to 9.3 anytime soon. > > Perhaps you can use FOR SHARE instead of FOR UPDATE in the first > connection, for instance .. > > -- > =C1lvaro Herrera http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >