Re: Fix FK deadlock, but no magic please

On Thu, 16 Jan 2003, Jon Swinth wrote:

> > Record read locks are not quite as good a solution as dirty reads from a
> > performance standpoint, which is why we've been aiming that direction
> > first. You'd need column locks pretty much to get equivalent behavior
> > afaict. The issue is that with record read locks, you prevent updates to
> > rows that do not affect the key values.
> >
>
> >From the standpoint of expected behaviour, I don't think you have any choice
> but to use record read locks. When someone does a write lock on a FK table
> record they have the expectation that they can do anything they want with the
> record including changing the PK or deleting the record. That is as long as
> there were no referencing records before the write lock was obtained. This
> means that someone else shouldn't be able to insert a record referencing
> while the FK table record has a write lock.
>
> Not being able to get a read lock when someone else has a write lock is
> expected behaviour. A single record should be able to have one write lock or
> multiple read locks, but not both. If I have a program that checks for
> referencing records, deletes them if found, and obtains a write lock on the
> FK record then I should reasonably assume that I can change anything about
> that record including delete it. If you don't prevent the write lock when a
> read lock is there then the person obtaining the write lock to very well get
> errors that they wouldn't normally expect.

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;

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.

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