Re: Fix FK deadlock, but no magic please

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.