On May 11, 2010, at 20:05 , Jan Wieck wrote:
> The problem really is that in the case of deleting a PK row while a concurrent transaction creates such a reference
cannotbe solved with user level visibility rules in case of a serializable transacton, unless you go really expensive
routes.
Yeah. The information to detect this is there, though - the xmax of the PK row will be a multixact in this case, and
onemember of that set won't be deemed visible by the deleting transaction.
> One corner case is that the transaction doing the FK INSERT commits after the serializable transaction doing the PK
DELETEgot its snapshot and also does the PK check before the PK DELETE got the lock on it. No user level visibility
allowsit to see that newly created reference. And unless the FK INSERTer actually UPDATE's the PK row (expensive), the
PKDELETE will not throw anything. It will wait to get the lock and go ahead with the delete.
Exactly. It consciously waits for the lock (knowing that it was held by a concurrent transaction *not* visible to the
deletingtransaction), and after obtaining the lock goes on to delete the row. If the concurrent transaction hadn't held
amere lock, but had instead UPDATEd the row, this would cause a serialization error.
> The PK DELETE needs to be able to do some sort of dirty scan in order to see those new references. That is what I
thinkTom was referring to.
Yeah. Though the need for that "dirty scan" (it's not actually a scan with DIRTY READ semantics, but rather one with
READCOMMITTED semantics) might vanish if a SHARE lock had the same effect (causing a serialization error) on concurrent
transactionsthat an UPDATE has.
I'm not yet convinced that this is true, nor do I necessarily think that making all SHARE locks behave that way would
bea good idea. But if my assertion is in fact true it would allow for robust user-level referential constraints by
eithermodifying SHARE-lock behavior or adding a new row-lock type.
best regards,
Florian Pflug