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.

The other example worries me more though.  Two transactions working with
the same pk row throughout.

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 (1);
 - Checks pk table for value, finds it, gets a read lock
Transaction 1: update pk set nonkey='a' where key=1;
 - Wants a write lock on row with pk.key=1, we can't upgrade
   our lock since T2 also has a read lock.
Transaction 2: update pk set nonkey='a' where key=1;
 - Same as above, except T1

For comparison, the dirty read(plus stuff that we aren't calling magic ;)
) version of the above basically goes:

Transaction 1: begin;
Transaction 2: begin;
Transaction 1: insert into fk values (1);
 - Checks pk table for value, finds it
Transaction 2: insert into fk values (1);
 - Checks pk table for value, finds it
Transaction 1: update pk set nonkey='a' where key=1;
 - Notices that the key is not changed, doesn't check
   fk table at all
Transaction 2: update pk set nonkey='a' where key=1;
 - Wait on transaction 1 since it has a lock on the row.

----
 Basically the real difference externally is that in one case the
blocking occurs before the action happens to the row and in the
other, the action happens and the foreign key code is the one
that does the blocking.  It allows things like not blocking based
on cases like the key not changing. I haven't determined if the
"stuff" necessary to get all the cases working is practical yet,
so I can't say for certain it's better, just that it has the
potential.