Re: Fix FK deadlock, but no magic please

I am a little confused on your examples

On Thursday 16 January 2003 12:00 pm, Stephan Szabo wrote:
>
> Well, for example (assuming two pk rows with 1 and 2 as keys)
>
> T1: begin;
> T1: insert into fk values (1);
> T2: begin;
> T2: insert into fk values (2);
> T1: update pk set nonkey='a' where key=2;
> T2: update pk set nonkey='b' where key=1;
>

Maybe I don't understand this example.  If T2 inserted fk 2, how did T1 manage
to update a record that references it before T2 committed?  For T1, fk 2
doesn't exist yet so there couldn't be any records referencing it.

> Should this deadlock?  I'd say no, barring triggers/rules, because
> those two updates shouldn't affect the constraint and so whenever
> possible the constraint's behavior shouldn't interfere.  But the
> obvious record read lock solution would afaics.  The inserts would grab a
> read lock on the two pk rows, neither transaction would be able to
> get the write lock it wants and it'd deadlock.  Or, what about
>
> T1: begin;
> T1: insert into fk values (1);
> T2: begin;
> T2: insert into fk values (1);
> T1: update pk set nonkey='a' where key=1;
> T2: update pk set nonkey='a' where key=1;
>
> Without those inserts this would be okay, one would wait for the other
> and everything would be okay, but with them I think this deadlocks as
> well.

Actually, T2 should block until T1 is committed or rolls back.  The system
cannot determine if the T2 fk should succeed or fail until T1 is committed or
rolled back.  Either that or I am mis-understanding your example again.
Maybe you should use table names.

>
> Maybe I'm misunderstanding the scheme you'd expect the foreign keys to use
> with the read locks.

The concept of a read lock is to make sure the record doesn't change until the
end of the transaction.  Selects on the record are allowed.  Other read locks
are allowed.  A write look should fail or wait until all read locks are
released.  A read lock should fail or wait for a write lock.

What I was trying to point out is that once a write lock is granted, the
grantee should be able to do anything they want with the record that is legal
in that transaction's view of the DB.  Otherwise, updates and deletes may
fail without any way for the code to determine why (it can't select to find
the offending record).  Here is an example:

TEST_TYPE table has a record with PK of 1
TEST_REC table has record (5) that references TEST_TYPE (1)

T1: wants to change all references to (1) so locks TEST_TYPE (1)
T1: inserts TEST_TYPE (2)
T1: updates TEST_REC (5) to reference TEST_TYPE (2)
T2: inserts TEST_REC (6) referencing TEST_TYPE (1)
I am saying that T2 should error or wait but lets say that you allow it
T1: attempts to delete TEST_TYPE (1)
At this point a SQL error would occur because of TEST_REC (6)
T1: doesn't know what to do because it still can't see or change TEST_REC (6)
no matter how good the code is on catching SQL exceptions and handling them,
T1 can't complete.

By the way, you can change the T2 entry to the very top of the list with the
same effect.  One of the great things about transactions and locking is that
you can write code that, short of a DB failure, can handle every situation.
Your proposing doing away with that.