Обсуждение: odd locking behaviour

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.

Confirmed reproducible on version 9.1 as well. Very odd.

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

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

Confirmed reproducible on version 9.1 as well. Very odd.

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

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

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@neadwerx.com> wrote:

Confirmed reproducible on version 9.1 as well. Very odd.

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

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