Confirmed reproducible on version 9.1 as well. Very odd.
On Wed, Jul 3, 2013 at 1:30 PM, pg noob <pgn...@gmail.com> wrote: > > Hi all, > > I am trying to understand some odd locking behaviour. > I apologize in advance if this is a basic question and should be widely > understood but > I don't see it described in the documentation as far as I could find. > > 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? > > Thank you. > > -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com "Quality is not an act, it is a habit." -- Aristotle
