Re: odd locking behaviour

I wish one of the PG developers would respond to this...

On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube(at)gmail(dot)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 <moshe(at)neadwerx(dot)com> wrote:
>
>> 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
>>
>
>

--
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