Re: serializable lock consistency

> 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.

Exactly.

> 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!)

If only serializable transactions are involved and they all commit,
then yes. The only reason for serializable transactions A to wait for
a lock taken by another transaction B is that B might abort, in which
case A may proceed.

> 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.

Yes. Otherwise, B cannot verify that the database is consistent.

Note that it's sufficient to check if B can see the effects of the
*latest* locker of T. If it can see those, it must also see the
effects of any previous locker. But because of this, B cannot
distinguish different lock strengths on T - even if A locked T
in exclusive mode, some transaction A2 may lock T in shared mode
after A has committed but before B inspects T.

> Questions:
> 1. Is my understanding of the problem correct?
> 2. Is my understanding of the fix correct?

Seems fine to me.

> 3. Is that what this patch implements?

It is. There is a small additional twist, however.

For serializable transactions, everything is as you explained it. Taking a
lock amounts to saying "If you can't see what I did, leave this tuple alone".
A read-committed transactions, though, sees different things at different
times since it takes a new snapshot for every statement. Since we cannot
raise a serialization error in a read-committed transaction, an UPDATE
or DELETE statement within a read-committed transaction may very well
modify a previously locked row, even if *doesn't* see the effects of some
concurrent locker. Any snapshot taken after the UDPATE or DELETE hit the
locked row, however, *will* see those changes. This includes the snapshot
taken within any AFTER trigger fired on updating the locked row. Thus,
things for one fine for RI constraints enforced by triggers.

best regards,
Florian Pflug