Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> I don't know what you mean by "get-a-new-snapshot strategy" or
> how it is different from the current read committed behavior.
Our current Read Committed level, if it blocks on a competing UPDATE
or DELETE, can provide a view of data which is based on a mix of
snapshots, causing various anomalies. (See the recent thread titled
"determine snapshot after obtaining locks for first statement" that
veered off into a discussion of this issue.)
Basically, if an UPDATE or DELETE blocks on a competing UPDATE or
DELETE (within a READ COMMITTED database transaction), it will, once
it is free to proceed, find the modified version of the row on which
it was blocking. So that will be committed data as of a later
transaction ID than other data we may be viewing, which would be
based on the snapshot from the start of the statement. As described
in the thread, that causes a target row not to be visible unless it
is present in both snapshots, and may cause joined tables or
subquery data to be out of sync with data from target rows.
To avoid these anomalies PostgreSQL would need to roll back the
affected statement, get a new snapshot, and start over. This would
apparently be rather messy to implement, and would probably be a bit
slower. I, for one, would willingly pay that price for better data
integrity -- if it affected me. Since we do all data modifications
in serializable transactions (or as close as we can come to that),
it doesn't actually affect me directly. I just thought that others
might be interested. Well, that and the possibility that if this
*was* implemented, it might allow for a nice optimization to reduce
serialization conflicts in true serializable mode. :-)
> We obviously want to be careful changing the default isolation
> level's behavior.
Of course. I've been sort of assuming that we would default to
maintaining current behavior, although that may be overly
conservative. I certainly would not want to eliminate either of the
existing isolation mode implementations, as they are likely to be
useful performance/integrity trade-offs for a significant percentage
of the user base.
-Kevin