Prev: [HACKERS] C++ keyword in utils/rbtree.h
Next: [HACKERS] HS starting snapshot corrections
From: Jan Wieck on 11 May 2010 14:05
On 5/11/2010 12:39 PM, Florian Pflug wrote:
> On May 11, 2010, at 17:04 , Robert Haas wrote:
>> 2010/5/11 Florian Pflug <fgp(a)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-locked is allowed. I do wonder if this shouldn't be considered a bug - whether locks conflict or not does not usually depend 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 updated causes 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 the UPDATE/LOCK-ing transaction. In other words, any attempt to UPDATE, SHARE-lock or UPDATE-lock a row from within a SERIALIZABLE transaction fails if the visible row version isn't the latest row version. If, however, the order of the events is the other way around, such that the SHARE-locking or UPDATE-locking happens first, and the UPDATE afterwards, then no 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 existence of a newer row version) after it has been updated by a transaction as "something else". After all, as you pointed out, the lock is gone as soon as the transaction commits. If, however, you view that "taint" as a slightly strange kind of lock that a transaction holds on the rows it updated even *after* the transaction committed, then it stops making 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 deals with 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 this in 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

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.

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.

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.


Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 | 
Pages: 1
Prev: [HACKERS] C++ keyword in utils/rbtree.h
Next: [HACKERS] HS starting snapshot corrections