hello It can be artefact of RI implementation.
see http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-for-key-share-and-for-no-key-update/ Try to 9.3, please, where RI uses more gently locks Regards Pavel Stehule Dne 21.7.2013 21:43 "Moshe Jacobson" <mo...@neadwerx.com> napsal(a): > > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
