I wish one of the PG developers would respond to this...
On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgn...@gmail.com> wrote: > > Thank you for the responses. Is it a bug? > I discovered this because of a db deadlock that shows up in my application > logs. > I can probably work around it to avoid the deadlock (with some amount of > work) but I really don't understand why it behaves as it does. > > > > On Thu, Jul 4, 2013 at 8: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 >> > > -- 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
