Re: Foreign key quandries - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | Re: Foreign key quandries |
Date | |
Msg-id | 13070.1046546871@sss.pgh.pa.us Whole thread Raw |
In response to | Re: Foreign key quandries (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
List | pgsql-hackers |
Stephan Szabo <sszabo@megazone23.bigpanda.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
pgsql-hackers by date: