Re: serializable lock consistency

On Wed, Dec 15, 2010 at 9:20 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> On Dec14, 2010, at 15:01 , Robert Haas wrote:
>> On Tue, Dec 14, 2010 at 7:51 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>>>> - serializable lock consistency - I am fairly certain this needs
>>>> rebasing.  I don't have time to deal with it right away.  That sucks,
>>>> because I think this is a really important change.
>>> I can try to find some time to update the patch if it suffers from bit-rot. Would that help?
>>
>> Yes!
>
> I've rebased the patch to the current HEAD, and re-run my FK concurrency test suite,
> available from https://github.com/fgp/fk_concurrency, to verify that things still work.
>
> I've also asserts to the callers of heap_{update,delete,lock_tuple} to verify (and document)
> that update_xmax may only be InvalidTransactionId if a lockcheck_snapshot is passed to
> heap_{update,delete,lock_tuple}.
>
> Finally, I've improved the explanation in src/backend/executor/README of how row locks and
> REPEATABLE READ transactions interact, and tried to state the guarantees provided by
> FOR SHARE and FOR UPDATE locks more precisely.
>
> I've published my work to https://github.com/fgp/postgres/tree/serializable_lock_consistency,
> and attached an updated patch. I'd be happy to give write access to that GIT repository
> to anyone who wants to help getting this committed.

I am having a hard time understanding this patch. I decided to start
with the README, and I'm still lost. :-(

My understanding of the problem is as follows. Acquiring a lock on a
tuple prevents the tuple from being concurrently updated. You might
take such a lock on a tuple T, make some other modification to the
database M, and commit, in the hope that your lock will prevent a
concurrent transaction from updating T without seeing M. However, it
doesn't work. When your lock on T is released, a concurrent
serializable transaction will still be looking at the database using
its original snapshot, and therefore won't see M. In effect, you
might as well not have bothered obtaining a lock at all. (You'd get
the same wrong answer that way, without unnecessary blocking!)

To fix this, one must ensure that if a tuple T is locked - either for
update or for share - by transaction A, and if a serializable
transaction B whose snapshot doesn't permit it to see the effects of A
subsequently attempts to update T, it must roll back.

Questions:
1. Is my understanding of the problem correct?
2. Is my understanding of the fix correct?
3. Is that what this patch implements?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company