Also on 9.3 beta2.
On Thu, Jul 4, 2013 at 5:40 AM, Moshe Jacobson <mo...@neadwerx.com> wrote: > 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 >
