Re: Foreign key quandries

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Sat, 1 Mar 2003, Tom Lane wrote:
>> ISTM the only thing we really need to do to address the complaints about
>> FKs is to invent some kind of sharable row-level lock.

> That gets rid of most of the problems. There are problems with read locks
> locking more than they may need to (insert into fk followed by another
> transaction doing a modification of the referenced row that doesn't affect
> the key).

Agreed, it's not a 100% solution ... but it would be way better than
what we have. (Hm, I wonder whether a read lock could specify that only
certain columns are locked? Nah, probably too much trouble.)

>> Of course, it's easy to say "sharable row-level lock" and not so easy to
>> come up with an implementation that has decent performance.

> As a thought exercise, what all is involved in making sharable row-level
> locks...

ISTM the problem is where to keep the state. You can't keep track of an
indefinite number of shared-lock holders in the on-row header ... but if
you try to keep the state in shared memory, you can't keep track of a
very large number of locked rows, either. Perhaps some scheme could be
implemented to keep lock state in memory but spill to disk when there
get to be too many locked rows. I don't see how to make that work
efficiently, though.

We talked about this a week or two back, and someone (was it Rod?) asked
essentially "do we *need* any state --- would a marker on the row that
it's share-locked be enough?". I suppose we could use an infomask bit
to indicate share-locking and overload xmax as a count of the number of
lockers. Then each transaction with read locks would have to have local
state remembering every row it's read-locked (this is much less bad than
shared state, since local RAM is more easily expansible), and at either
transaction commit or abort you'd have to run around and decrement those
counts. (But when you decrement a count to zero, what next? You still
need to figure out who's blocked on that row and release them.) This
could perhaps be made to work with reasonable efficiency, but it makes
me nervous. If someone crashes while holding read locks, how do you
recover? Seems like you need to scan the *entire database* during
restart to zero out shared-lock counts. In general we have stayed away
from the notion of requiring transactions to do end-of-transaction
cleanup on disk, and I think that is a good design choice not to be
tossed away lightly.

So I don't see how to do that efficiently. But still, it seems a more
tractable problem than trying to prove a dirty-read implementation correct.

regards, tom lane