From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Moshe Jacobson <moshe(at)neadwerx(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>, pg noob <pgnube(at)gmail(dot)com> |
Subject: | Re: odd locking behaviour |
Date: | 2013-07-22 04:15:57 |
Message-ID: | CAFj8pRDYfuG7Qb=1o-WT-fxCsg9zj7gTMtVxAXOc=Y8CxUtixw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
hello
It can be artefact of RI implementation.
Try to 9.3, please, where RI uses more gently locks
Regards
Pavel Stehule
Dne 21.7.2013 21:43 "Moshe Jacobson" <moshe(at)neadwerx(dot)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 <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
From | Date | Subject | |
---|---|---|---|
Next Message | Ondrej Chaloupka | 2013-07-22 08:09:59 | Different transaction log for database/schema |
Previous Message | Moshe Jacobson | 2013-07-21 19:41:51 | Re: odd locking behaviour |