Re: Repeatable read and serializable transactions see data committed after tx start

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Kevin Grittner wrote:

(wording change suggestion)
>> | sees a snapshot as of the start of the first query within the
>> | transaction, not as of the start of the current query within the
>> | transaction.
>
> Would that have prevented the confusion here?

I think it may have, but I also think the wording should be much
stronger and clearer, as this is unintuitive behavior. Consider
this snippet from Bruce's excellent MVCC Unmasked presentation:

"A snapshot is recorded at the start of each SQL statement in
READ COMMITTED transaction isolation mode, and at transaction start
in SERIALIZABLE transaction isolation mode."

This is both correct and incorrect, depending on whether you consider
a transaction to start with BEGIN; or with the first statement
after the BEGIN. :) I think most people have always assumed that
BEGIN starts the transaction and that is the point at which the snapshot
is obtained.

>> But what about creating a flag to BEGIN and SET TRANSACTION
>> commands, called "IMMEDIATE FREEZE" (or something similar), which
>> applies only to REPEATABLE READ and SERIALIZABLE? If this flag is set
>> (and may be off by default, but of course the default may be
>> configurable via a guc parameter), freeze happens when it is present
>> (BEGIN or SET TRANSACTION) time. This would be a backwards-compatible
>> change, while would provide the option of freezing without the nasty
>> hack of having to do a "SELECT 1" prior to your real queries, and
>> everything will of course be well documented.

> What is the use case where you are having a problem? This seems
> like an odd solution, so it would be helpful to know what problem
> it is attempting to solve.

Seems like a decent solution to me. The problem it that having to execute
a dummy SQL statement to start a serializable transaction, rather
than simply a BEGIN, is ugly.and error prone. Perhaps their app
assumes (or even requires) that BEGIN starts the snapshot.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201411060922
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlRbhD4ACgkQvJuQZxSWSsg/kwCdE9E+d3jDDpLOo4+08wCOMMxE
EHkAnj4uMO8cY6Jl0R19C/6lE6n3bae5
=syg9
-----END PGP SIGNATURE-----