Re: odd locking behaviour

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
>