Jeff Davis <pg...@j-davis.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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers