Re: Fix FK deadlock, but no magic please - Mailing list pgsql-general

From Stephan Szabo
Subject Re: Fix FK deadlock, but no magic please
Date
Msg-id 20030116143605.D9839-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: Fix FK deadlock, but no magic please  (Jon Swinth <jswinth@atomicpc.com>)
Responses Re: Fix FK deadlock, but no magic please
List pgsql-general
On Thu, 16 Jan 2003, Jon Swinth wrote:

> I am a little confused on your examples
>
> On Thursday 16 January 2003 12:00 pm, Stephan Szabo wrote:
> >
> > Well, for example (assuming two pk rows with 1 and 2 as keys)
> >
> > T1: begin;
> > T1: insert into fk values (1);
> > T2: begin;
> > T2: insert into fk values (2);
> > T1: update pk set nonkey='a' where key=2;
> > T2: update pk set nonkey='b' where key=1;
> >
>
> Maybe I don't understand this example.  If T2 inserted fk 2, how did T1 manage
> to update a record that references it before T2 committed?  For T1, fk 2
> doesn't exist yet so there couldn't be any records referencing it.

Noone has completed in the above.  They're two concurrent transactions
that may deadlock.

AFAICT, what you want is a sequence like the below (including lock
annotations) for the above.

Transaction 1: begin;
Transaction 2: begin;
Transaction 1: insert into fk values (1);
 - Checks pk table for value, finds it, gets a read lock
Transaction 2: insert into fk values (2);
 - Checks pk table for value, finds it, gets a read lock
Transaction 1: update pk set nonkey='a' where key=2;
 - Wants a write lock on row with pk.key=2, can't get it because
   Transaction 2 has a read lock. It has to wait.
Transaction 2: update pk set nonkey='a' where key=1;
 - Wants a write lock on row with pk.key=1, can't get it because
   Transaction 1 has a read lock. It has to wait.



pgsql-general by date:

Previous
From: "Brian Johnson"
Date:
Subject: Insert from CSV (comma separated values) file ?
Next
From: Jon Swinth
Date:
Subject: Re: Fix FK deadlock, but no magic please