Re: Revisited: Transactions, insert unique.

Joachim Achtzehnter wrote:
>
> In a message to Ed Loehr and pgsql-general, Lincoln Yeoh wrote:
> >
> > Transaction A
> > begin;
> > insert into test (a,b) select 4,'four' from test
> > where not exists (select * from test where a=4);
> >
> > Transaction B
> > begin;
> > insert into test (a,b) select 4,'four' from test
> > where not exists (select * from test where a=4);
> >
> > Then you do a commit on both, and you end up with two rows.
>
> This is dissapointing indeed! What this means is that Postgresql
> transactions are, in fact, not truely serializable. The concurrent
> execution of these two transactions is not equivalent to some serial
> execution.
>
> Have to read up on the documentation, which I thought was claiming
> serializability.

Hmmm. This surprised me, too. The serialization doesn't work as I
expected it to. Maybe someone can illuminate. Here's what confused me...

The documentation says:

Serializable provides the highest transaction isolation.
When a transaction is on the serializable level, a query
sees only data committed before the transaction began and
never see either dirty data or concurrent transaction
changes committed during transaction execution. So, this
level emulates serial transaction execution, as if
transactions would be executed one after another, serially,
rather than concurrently.
- http://www.postgresql.org/docs/postgres/mvcc4508.htm (4/25/2000)

But the following sequence seems to contradict this:

-- Transaction A
DROP TABLE foo;
CREATE TABLE foo (id INTEGER);
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Transaction B
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id
= 1);
COMMIT;
SELECT * FROM foo;
-- Transaction A
SELECT * FROM foo;
INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id
= 1);
SELECT * FROM foo;
COMMIT;

This sequence allows B's inserts, AFTER A began, to be seen by A (at least,
in pgsql 7.0beta3).

Anyone understand what's going on here? Bug?

Regards,
Ed Loehr