Re: RI_FKey_check: foreign key constraint blocks parall

On Fri, 15 Nov 2002, Mikheev, Vadim wrote:

> Just wonder how are you going to implement it - is it by using
> some kind of "read-locks", ie FK transaction "locks" PK to prevent
> delete (this is known as "pessimistic" approach)?
> About two years ago we discussed with Jan "optimistic" approach
> with using "dirty reads", when PK/FK transactions do not check
> existence of FK/PK untill constraint should be checked (after
> statement processed for immediate mode, at the commit time/
> set constraint immediate for deferred constraints).
>
> So, at the check time, FK transaction uses dirty reads to know
> about existence/"status" of PK:
> 1. No PK -> abort.
> 2. PK (inserted?/)deleted/updated/selected for update by concurrent
> transaction P -> wait for P commit/abort (just like transactions do
> for concurrent same-row-update); go to 1.
> 3. Else (PK exists and no one changing it right now) -> proceed.
>
> PK transaction does the same:
> 1. No FK -> proceed.
> 2. FK inserted/updated/selected for update by concurrent transaction
> F -> wait for F commit/abort; go to 1.
>
> This would be more in MVCC style -:)

Right now, it's similar to the above, but only one direction is doing
the dirty reads right now. I don't do the dirty reads on the fk
transactions right now. It'll still see delete/update/selected for
update on a row that would have otherwise existed for the transaction,
but not see the new rows (I'd like to switch it to dirty both
directions, but I'm having enough trouble with deadlocks as it is).
Or, at least that's the intention behind the code if not the actual
effect. It gets rid of the concurrency issues of two fk transactions, but
it doesn't get rid of deadlock cases.

T1: insert into fk values (1);
T2: delete from pk;
T1: insert into fk values (1);
shouldn't need to deadlock. The "lock" stuff is actually more like
an un-lock to make it not wait on the second T1 statement. It's
broken, however, as I just thought of some more things it doesn't handle
correctly. Oh well.