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

On May 11, 2010, at 17:04 , Robert Haas wrote:
> 2010/5/11 Florian Pflug <fgp@phlo.org>:
>> C1: BEGIN
>> C1: INSERT INTO child (parent_id) VALUES (0)
>> C2: BEGIN
>> C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> C2: SELECT TRUE -- Take snapshot *before* C1 commits
>> C1: COMMIT
>> C2: DELETE FROM parent WHERE parent_id = 0 -- Works!
>> C2: COMMIT
>>
>> It seems that while SHARE-locking a concurrently deleted row causes a serialization error, deleting a concurrently
SHARE-lockedis allowed. I do wonder if this shouldn't be considered a bug - whether locks conflict or not does not
usuallydepend on the other in which they are taken. 
>
> Wait - I'm confused.  The DELETE in your example happens after C1
> commits, so C1 can't still be holding any locks (nor does C2 take any
> locks prior to the commit of C1).

I used the word "lock" a bit sloppy there.

What I did want to point out is that any UPDATE by a SERIALIZABLE transaction to a row that has been concurrently
updatedcauses a serialization error. The same happens when it instead SHARE- or UPDATE-locks the concurrently updated
row.This is also independent from the commit-time of the concurrent transaction, as long as it is deemed invisible by
theUPDATE/LOCK-ing transaction. In other words, any attempt to UPDATE, SHARE-lock or UPDATE-lock a row from within a
SERIALIZABLEtransaction fails if the visible row version isn't the latest row version. If, however, the order of the
eventsis the other way around, such that the SHARE-locking or UPDATE-locking happens first, and the UPDATE afterwards,
thenno serialization error occurs! 

That might seem sensible if you view SHARE-locks and UPDATE-locks as locks, and the "taint" that marks a row (the
existenceof a newer row version) after it has been updated by a transaction as "something else". After all, as you
pointedout, the lock is gone as soon as the transaction commits. If, however, you view that "taint" as a slightly
strangekind of lock that a transaction holds on the rows it updated even *after* the transaction committed, then it
stopsmaking sense. You now have a "locking" behavior with order-dependent conflicts. 

Viewing those "taints" as locks is consistent with how that true serializability algorithm Kevin Grittner is working on
dealswith those things, I believe - or at least it's probably that paper in the back of my mind that made me call it
"lock"in the first place. 

It would be interesting to formalize this in the language of that paper - unfortunately, I probably lack the time to do
thisin the near future :-(  

To avoid more confusion, here are the sequences of commands I have in mind:

No serialization error (and neither with "FOR UPDATE" instead of "FOR SHARE")
C1: BEGIN
C1: SELECT t.* FROM t WHERE id = 1 FOR SHARE
C2: BEGIN
C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
C2: SELECT TRUE --Take snapshot before c1 commits
C1: COMMIT
C2: UPDATE t SET id = 2 WHERE id = 1
C2: COMMIT

Serialization error (and also with "FOR UPDATE" instead of "FOR SHARE")
C1: BEGIN
C1: UPDATE t SET id = 2 WHERE id = 1
C2: BEGIN
C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
C2: SELECT TRUE --Take snapshot before c1 commits
C1: COMMIT
C2: SELECT t.* FROM t WHERE id = 1 FOR SHARE --serialization error
C2: COMMIT

best regards,
Florian Pflug