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.