Re: Fix FK deadlock, but no magic please

Now I understand what you are trying to say, but what you are describing is
normal (happens in most DBs) and rather uncommon (in my experience). General
DB design is done so reference tables end up with a lot of read locks and
rarely have a write lock. It would be cool if you could remove that
contention, but not at the expense of expected write lock behaivor.

On Thursday 16 January 2003 02:43 pm, Stephan Szabo wrote:
> 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.