Re: More FOR UPDATE/FOR SHARE problems

Jeff Davis <pgsql(at)j-davis(dot)com> writes:

> It seems like it would be a challenge to know that the tuple with i=3
> would be updated to a value that matches the search condition j=10. So
> can you tell me a little more about the mechanism by which Sybase solves
> this problem?

This example is a case of the same issue we were discussing earlier involving
"predicate locking". To solve it you need a way to lock records that your
query *isn't* accessing and may not even exist yet to prevent them from being
turned into (or inserted as) records your query should be accessing.

As Kevin described it earlier Sybase locks the index pages containing the key
range you're accessing preventing anyone from inserting new index pointers in
that range. If there's no index it locks the entire table on every select to
prevent any updates or inserts in the table until your transaction finishes.

In any case note that your example is not *serializable*. (Though in Postgres
it can happen even in serializable mode, so that's not much of a defence.) I'm
unclear what whether it manifests any of the phenomenon which are prohibited
for READ COMMITTED.

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