Re: odd locking behaviour

Confirmed reproducible on version 9.1 as well. Very odd.

On Wed, Jul 3, 2013 at 1:30 PM, pg noob <pgnube(at)gmail(dot)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
moshe(at)neadwerx(dot)com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle