Question About Serializable

From: Aaron Carlisle <aaron(dot)carlisle(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Question About Serializable
Date: 2013-09-19 04:57:22
Message-ID: CAMSsDqXJDR3_mW1bSmT3rCCP6GvJqB+4Lh-oFHDatQRMHVn7fQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The documentation states that "concurrent execution of a set of
Serializable transactions is guaranteed to produce the same effect as
running them one at a time in some order."

I'm not sure how the following behavior fits that definition. (Note that
this is just an experiment, not a use case. Purely academic.) I run these
transactions sequentially, and I get a different result than if I run them
concurrently.

This is in 9.3.0

First, I set up a table.

create table x (value int);

Then I run the following transactions. If I run them sequentially, in
either order, I get one row in table x. If I run them concurrently, I get
no rows in x.

It seems like one of these should error out and not commit, so I must be
missing some stipulation.

Feel free to repeat this result.

=========
begin;

set transaction isolation level serializable;

create table z ();

select pg_sleep(5);

insert into x (value)
select 0
where exists (select relname from pg_class
where relname = 'y')
and exists (select relname from pg_class
where relname = 'z');
commit;

=========
begin;

set transaction isolation level serializable;

create table y ();

select pg_sleep(5);

insert into x (value)
select 0
where exists (select relname from pg_class
where relname = 'y')
and exists (select relname from pg_class
where relname = 'z');
commit;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jayadevan 2013-09-19 05:16:27 Re: Query - CPU issue
Previous Message Jayadevan 2013-09-19 04:55:20 Re: Query - CPU issue