I think I see what you are trying to acheive. I agree with you and like the
idea of forgoing read lock when the FK field is not modified. This should be
a nice performance gain on blindly creating a read lock on a record. I guess
I just got so worried about the term "dirty read" that I didn't understand
the rest.
Now, if we could only have the feature like Oracle of SELECT ... FOR UPDATE
NOWAIT, so I can control how long we wait for a lock. Wait... can't do that
until SQL exceptions stop voiding the transaction (I want to be able to retry
the lock several times before giving up).
Hey, forget that, if you can fix FK deadlock then I'll deal with the rest.
On Thursday 16 January 2003 03:47 pm, Stephan Szabo wrote:
> 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.