Re: SHARE locks vs. DELETE in SERIALIZABLE mode (Was: Partitioning/inherited tables vs FKs)

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 cannot be 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 one member 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 DELETE got its snapshot and also does the PK check before the PK DELETE got the lock on it. No user level visibility allows it to see that newly created reference. And unless the FK INSERTer actually UPDATE's the PK row (expensive), the PK DELETE 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 deleting transaction), and after obtaining the lock goes on to delete the row. If the concurrent transaction hadn't held a mere 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 think Tom 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 READ COMMITTED semantics) might vanish if a SHARE lock had the same effect (causing a serialization error) on concurrent transactions that 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 be a good idea. But if my assertion is in fact true it would allow for robust user-level referential constraints by either modifying SHARE-lock behavior or adding a new row-lock type.

best regards,
Florian Pflug