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, blah3I 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 blocksI 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
Also on 9.3 beta2.
On Thu, Jul 4, 2013 at 5:40 AM, Moshe Jacobson <moshe@neadwerx.com> wrote:
Show quoted text
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, blah3I 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 blocksI 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:
Show quoted text
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, blah3I 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 blocksI 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
I wish one of the PG developers would respond to this...
On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube@gmail.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@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, blah3I 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 blocksI 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
--
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
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@neadwerx.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@gmail.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@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, blah3I 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 blocksI 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
--
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, Jul 21, 2013 at 9:15 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
hello
It can be artefact of RI implementation.
Try to 9.3, please, where RI uses more gently locks
It still behaves this way in 9.4dev.
On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube@gmail.com> wrote:
Thank you for the responses. Is it a bug?
I don't think so. While PostgreSQL of course strives for maximum
concurrency, it makes no guarantee that it uses the weakest
theoretically possible locking in all possible cases. But it is kind
of unfortunate that updating the same row twice causes a lock
escalation when it is not obvious it should do so, because as you
found that makes avoiding deadlocks quite difficult.
I'm rather surprised it doesn't block at the first update of the 2nd
session, rather than waiting for the 2nd update of that session.
Anyway, when the 2nd session re-updates the same row in the same
transaction, it uses a 'multixact' to record this. Doing that
apparently defeats some locking optimization that takes place under
simpler cases.
Sorry, that probably isn't the definitive answer you were hoping for.
Cheers,
Jeff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank you Jeff and others for the responses.
One concern that I have is that even cases where there is no deadlock it is
still acquiring stronger locks than necessary. I only discovered it
because of the deadlock issue but I presume that there are many cases where
it is acquiring a lock on the foreign table and really doesn't need to.
That would seem to lead to higher lock contention in general which although
it may not cause a deadlock in every case, does affect the overall
performance.
In my opinion this should be a case where it ought not to acquire any locks
on the foreign table at all.
It's not as though the columns relevant to the foreign key reference have
changed.
Anyway, at least it's good to understand it more. I'll have to find some
way to work around this in my application.
Thank you!
On Mon, Jul 22, 2013 at 12:48 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Show quoted text
On Sun, Jul 21, 2013 at 9:15 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:hello
It can be artefact of RI implementation.
see
Try to 9.3, please, where RI uses more gently locks
It still behaves this way in 9.4dev.
On Mon, Jul 8, 2013 at 9:54 AM, pg noob <pgnube@gmail.com> wrote:
Thank you for the responses. Is it a bug?
I don't think so. While PostgreSQL of course strives for maximum
concurrency, it makes no guarantee that it uses the weakest
theoretically possible locking in all possible cases. But it is kind
of unfortunate that updating the same row twice causes a lock
escalation when it is not obvious it should do so, because as you
found that makes avoiding deadlocks quite difficult.I'm rather surprised it doesn't block at the first update of the 2nd
session, rather than waiting for the 2nd update of that session.Anyway, when the 2nd session re-updates the same row in the same
transaction, it uses a 'multixact' to record this. Doing that
apparently defeats some locking optimization that takes place under
simpler cases.Sorry, that probably isn't the definitive answer you were hoping for.
Cheers,
Jeff
Import Notes
Reply to msg id not found: CAPNY-2Wfuu4pnU9BJjp4ACCbmhDzwqL_He4GODXmbOrELK40zw@mail.gmail.com
Moshe Jacobson escribi�:
I wish one of the PG developers would respond to this...
Have you seen the thread in pgsql-bugs?
/messages/by-id/E1UwaMw-0000VH-CD@wrigleys.postgresql.org
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general