Re: More FOR UPDATE/FOR SHARE problems

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:

>> I think Greg has it right: without predicate locking we can't really
>> achieve the behavior you're expecting. So how would we better approach the
>> semantics you want without it?
>
> Well, this thread was talking about dealing with situations where
> queries using FOR UPDATE/FOR SHARE return something other than what is
> requested, or results based on viewing only part of what was committed
> by another transaction. My feeling is that we should be looking
> harder at recognizing these cases and rolling back a transaction with
> a serialization failure before returning bad data.

Well that's precisely what our SERIALIZABLE isolation mode does.

What I thought was intriguing was the thought of applying the serializable
logic to individual commands instead of the whole transaction. That is, repeat
the same command with a new snapshot instead of having to restart the whole
transaction over again. That's something you can't actually emulate right now.
The only way to get notified by Postgres that there's been an update is to be
in serializable mode which has to be set for the whole transaction, so even if
you catch the error you don't get a new snapshot.

> This doesn't seem to me to be that different from other situations
> where people have said "It's easy to return results quickly if you
> don't care whether they're accurate."

Well I think the fundamental point is that SELECT FOR UPDATE gives you the
right data *for a subsequent update* of that row. If you never actually peek
at that data except to use it in a subsequent update then you get the same
results as if you had just issued the UPDATE and the resulting data in the
table is reasonable. If you use the data for purposes external to that row
then strange things do indeed result.

> Regarding the broader issues -- during discussion of documentation for
> the anomalies in snapshot isolation I was made aware of recent work,
> published by the ACM last year, which provides techniques for a more
> general and comprehensive solution.

Hopefully it's not patent encumbered? Might be better not to check actually.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!