Re: Fix FK deadlock, but no magic please

On Thu, 16 Jan 2003, Jon Swinth wrote:

> 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.

I think I may have also misunderstood which lock behavior you were worried
about.  In either scheme if someone does something like:

Transaction 1: begin;
Transaction 2: begin;
Transaction 1: select for update from pk where key=1;
 - Gets a write lock on row with pk.key=1
   [Or does an update or a delete or whatever]
Transaction 2: insert into fk values (1);
 - Needs to wait on the write lock above

That will stay true even in the dirty read scheme.