Re: Strange Behavior with Serializable Transcations

On Wed, 2006-06-28 at 21:20 +0200, Martijn van Oosterhout wrote:
> On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote:
> > I'm seeing something fairly unintuitive about serializable transactions.
> >
> > Taking the following test case:
>
> <snip>
>
> > http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
> >
> > "When a transaction is on the serializable level, a SELECT query sees
> > only data committed before the transaction began; it never sees either
> > uncommitted data or changes committed during transaction execution by
> > concurrent transactions."
>
> I think the issue here is that transaction begin is not when you type
> "begin" but at your first actual query.

The issue is the difference between start of transaction and time when
the serializable snapshot is taken. Since BEGIN and other commands may
be issued as separate network requests it makes sense to defer taking
the snapshot until the first time it is needed. The transaction is still
serializable, just that the manual is worded slightly incorrectly with
regards the exact timing.

> You can obviously only start a
> transaction once you know what serialisation level you want, and you
> don't see that till after the begin.

Isolation level can be set on the BEGIN statement, so sometimes you do
know, but as mentioned above that's no reason to take the snapshot at
that point.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com