From: | Mario Weilguni <mweilguni(at)sime(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Reproducable deadlock situation (possibly with foreign keys) |
Date: | 2005-11-16 11:47:43 |
Message-ID: | 200511161247.43693.mweilguni@sime.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2005-11-16 12:20:52 | Re: Reproducable deadlock situation (possibly with foreign keys) |
Previous Message | Alvaro Herrera | 2005-11-16 11:21:58 | Re: Reproducable deadlock situation (possibly with foreign keys) |