Thread: Strange Behavior with Serializable Transcations

Strange Behavior with Serializable Transcations

From
Brad Nicholson
Date:
I'm seeing something fairly unintuitive about serializable transactions.

Taking the following test case:

CREATE TABLE foo (id integer);

t1                       t2
--                       BEGIN;
--                       SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO foo (id)     --
VALUES (1);              --
--                       SELECT * from foo;

The select in t2 sees the row inserted from t1, which it shouldn't.

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."

Now, if I modify the case as such:

t1                       t2
--                       BEGIN;
--                       SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--                       SELECT * from foo;
INSERT INTO foo (id)     --
VALUES (1);              --
--                       SELECT * from foo;

The select in t2 (the last one, obviously) does not see the insert from t1.

What's up?


--
Brad Nicholson  416-673-4106    bnichols@ca.afilias.info
Database Administrator, Afilias Canada Corp.


Re: Strange Behavior with Serializable Transcations

From
Martijn van Oosterhout
Date:
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. 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.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: Strange Behavior with Serializable Transcations

From
Bruno Wolff III
Date:
On Wed, Jun 28, 2006 at 14:48:01 -0400,
  Brad Nicholson <bnichols@ca.afilias.info> wrote:
> I'm seeing something fairly unintuitive about serializable transactions.
>
> "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."

This has come up on the lists before. Take a look at:
http://secure.linuxports.com/pgsql-admin/2006-01/msg00256.php

Re: Strange Behavior with Serializable Transcations

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> I think the issue here is that transaction begin is not when you type
> "begin" but at your first actual query. 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.

Right, the snapshot does not become set until you do a non-utility
command (normally, SELECT/INSERT/UPDATE/DELETE).  This is a feature, not
a bug, because it lets the transaction take table locks before its
snapshot becomes set.  I believe the Fine Manual's discussion of
concurrency includes some examples where such locks are needed ...

            regards, tom lane

Re: Strange Behavior with Serializable Transcations

From
Simon Riggs
Date:
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


Re: Strange Behavior with Serializable Transcations

From
Martijn van Oosterhout
Date:
On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> 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.

I've always interpreted it as "there exists a serialised order for the
transactions" but the database makes no guarentees about what it might
be. I can't think of any real world case where you actually care about
the order, just as long as one exists.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: Strange Behavior with Serializable Transcations

From
Simon Riggs
Date:
On Thu, 2006-06-29 at 14:27 +0200, Martijn van Oosterhout wrote:
> On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> > 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.
>
> I've always interpreted it as "there exists a serialised order for the
> transactions" but the database makes no guarentees about what it might
> be. I can't think of any real world case where you actually care about
> the order, just as long as one exists.

Agreed, the ordering is irrelevant.

Deferring the snapshot provides you with the most accurate *and*
consistent view of the database.

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


Re: Strange Behavior with Serializable Transcations

From
"Qingqing Zhou"
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote
>
> Right, the snapshot does not become set until you do a non-utility
> command (normally, SELECT/INSERT/UPDATE/DELETE).  This is a feature, not
> a bug, because it lets the transaction take table locks before its
> snapshot becomes set.
>

Hm, mostly I understand it as an optimization. What do you mean by "it lets
the transaction take table locks before its snapshot becomes set"?  If we
take a snapshot at BEGIN, then the transaction can't take table locks?

test=# begin;select count(*) from k;
BEGIN
 count
-------
  9999
(1 row)

test=# lock table k;
LOCK TABLE

Regards,
Qingqing



Re: Strange Behavior with Serializable Transcations

From
Tom Lane
Date:
"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote
>> Right, the snapshot does not become set until you do a non-utility
>> command (normally, SELECT/INSERT/UPDATE/DELETE).  This is a feature, not
>> a bug, because it lets the transaction take table locks before its
>> snapshot becomes set.

> Hm, mostly I understand it as an optimization. What do you mean by "it lets
> the transaction take table locks before its snapshot becomes set"?  If we
> take a snapshot at BEGIN, then the transaction can't take table locks?

It can, but there are cases where you want the lock to be taken before
the snapshot is set.  Otherwise, there could be committed changes in the
database that you can't see in your snapshot.  I think there are some
examples in the manual, or check the archives.

            regards, tom lane

Re: Strange Behavior with Serializable Transcations

From
"Qingqing Zhou"
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote
>
> It can, but there are cases where you want the lock to be taken before
> the snapshot is set.  Otherwise, there could be committed changes in the
> database that you can't see in your snapshot.  I think there are some
> examples in the manual, or check the archives.
>

Ok, here are the links:

http://archives.postgresql.org/pgsql-bugs/2002-12/msg00169.php  - a bug
analysis of the take-snapshot at wrong point;

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00133.php - recent
redesign notes.

Regards,
Qingqing



Re: Strange Behavior with Serializable Transcations

From
Bruno Wolff III
Date:
On Thu, Jun 29, 2006 at 14:27:30 +0200,
  Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> > 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.
>
> I've always interpreted it as "there exists a serialised order for the
> transactions" but the database makes no guarentees about what it might
> be. I can't think of any real world case where you actually care about
> the order, just as long as one exists.

Postgres' serializable mode doesn't guaranty that. To get that effect you
may need to do some extra locking.