SEO

I've a problem occurring daily for me, I get quite a few deadlocks every day,
and the concurrency is not so high. Happens with postgresql 8.0 and 8.1. as
well...

Here's a self-contained testcase, which I think it might be the problem I have
in our production database. While it might be some sort of theoretical
problem, it happens, the referenced tables are never really updated, but are
just lookup-tables. In the production systen it's a lot more complicated,
there are at least 10 different lookup tables, and not all table contain
references to all lookup-tables:

create table lookup1 (
id int primary key,
t text
);

create table lookup2 (
id int primary key,
t text
);

insert into lookup1 values (1, 'test1');
insert into lookup1 values (2, 'test2');

insert into lookup2 values (3, 'test3');
insert into lookup2 values (4, 'test4');

create table master1 (
id int primary key,
l1_id int references lookup1(id),
l2_id int references lookup2(id),
t text
);

create table master2 (
id int primary key,
l2_id int references lookup2(id),
l1_id int references lookup1(id),
t text
);

insert into master1 values (1000, 1, 3);
insert into master2 values (1001, 3, 1);


T1: BEGIN;
T2: BEGIN;
-- these are the queries similar to those from the foreign key code
T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
-- DEADLOCK OCCURS!
T1: UPDATE master1 set t='foo' where id=1000;
T2: UPDATE master2 set t='foo' where id=1001;


IMO it should be possible to solve this IF the foreign key code reorders the
"for update" queries in a well-defined order, maybe ordered by the oid of the
pgclass entry.

In my case, it always happens on INSERT activity (no updates on those tables,
just inserts), but I hope the above problem might be the solution for the
insert deadylock too.

Does this sound reasonable?

Regards,
Mario Weilguni

p.s. Is it possible to modify logging so that the "SELECT 1 FROM ONLY...." are
logged? Maybe this could help me finding out which queries the foreign key
code really issues.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Mario Weilguni wrote:

Hi,

> T1: BEGIN;
> T2: BEGIN;
> -- these are the queries similar to those from the foreign key code
> T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
> T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
> T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
> T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
> -- DEADLOCK OCCURS!
> T1: UPDATE master1 set t='foo' where id=1000;
> T2: UPDATE master2 set t='foo' where id=1001;

Actually, in 8.1 the FK code issues queries like

T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x;

which takes only a share lock on the tuple, not an exclusive lock, which
solves the blocking and deadlocking problem. If you have a test case
where it fails on 8.1 I certainly want to see it.


> p.s. Is it possible to modify logging so that the "SELECT 1 FROM ONLY...." are
> logged? Maybe this could help me finding out which queries the foreign key
> code really issues.

Hmm, actually, those queries should be logged normally, because AFAIK
they are issued just like any other query, via SPI.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Ok, this my fault, and you're right.

I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1
DB on a testing system without thinking too much.

Still I think reordering those queries might prevent a deadlock.

Best regards

Am Mittwoch, 16. November 2005 12:21 schrieb Alvaro Herrera:
> Mario Weilguni wrote:
>
> Hi,
>
> > T1: BEGIN;
> > T2: BEGIN;
> > -- these are the queries similar to those from the foreign key code
> > T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
> > T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
> > T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x;
> > T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x;
> > -- DEADLOCK OCCURS!
> > T1: UPDATE master1 set t='foo' where id=1000;
> > T2: UPDATE master2 set t='foo' where id=1001;
>
> Actually, in 8.1 the FK code issues queries like
>
> T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x;
>
> which takes only a share lock on the tuple, not an exclusive lock, which
> solves the blocking and deadlocking problem. If you have a test case
> where it fails on 8.1 I certainly want to see it.
>
> > p.s. Is it possible to modify logging so that the "SELECT 1 FROM
> > ONLY...." are logged? Maybe this could help me finding out which queries
> > the foreign key code really issues.
>
> Hmm, actually, those queries should be logged normally, because AFAIK
> they are issued just like any other query, via SPI.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Mario Weilguni wrote:
> Ok, this my fault, and you're right.
>
> I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1
> DB on a testing system without thinking too much.
>
> Still I think reordering those queries might prevent a deadlock.

Well, if we could reorder them, we could have solved the problem long
ago. I'm not totally sure it can't be done, but we tackled the problem
in a different way so it's moot now.

--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org