Re: XMIN semantic at peril ?

On Thu, Oct 11, 2007 at 10:44:17AM -0400, Tom Lane wrote:

> > How likely is XMIN (or equivalent) to NOT change on each
> > successful (write) transaction commit anymore ?
>
> No chance of that, unless we abandon MVCC for something else, which
> again seems highly unlikely.
I figured as much but I'm just a loney MD.

> One question I'd have though is whether "freezing" of old tuples is
> likely to confuse your app. That process might get more aggressive
> in the future (it already is more aggressive in 8.2 than before,
> depending on where vacuum_freeze_min_age is set).

Well, what we do is this:

- read row including XMIN
- do some UI stuff without open transactions
- update row with "... where pk = ... and XMIN = old_xmin_from_read"

If in the meantime another writer changed the data we
originally read we would detect that by xmin having changed
hence no row to be updated. So, yes, there is a *tiny*
failure condition:

- read row including XMIN
- do UI stuff w/o open transaction
- wait a LONG time in which
- old_xmin_from_read gets frozen
- and recycled on the *very same* row
- attempt to write initial data with ... WHERE XMIN = old_xmin_from_initial_read

In those very circumstances we would not be able to detect
the collision. Now, how likely is that given sane values for
vacuum_freeze_min_age and average ambulatory care patient
encounter times of 5-20 minutes (which would in GNUmed be
equivalent to cross-tx medical record open times - IOW the
time our XMIN based optimistic locks would normally be
held).

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346