Re: [BUGS] BUG #8290: broken/unexpected locking behavior
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 wrote: > 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 Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [BUGS] BUG #8290: broken/unexpected locking behavior
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 wrote: > > On Tue, Jul 9, 2013 at 9:02 AM, wrote: > >> 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. >> >> >> 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; > >
