Re: Yet another "drop table vs delete" question

On Tue, 2009-04-21 at 14:30 -0700, Christophe wrote:
> Indeed so, and I understand that part. But since Session1 didn't try
> to access 'bar', it can't distinguish that sequence from:
>
> Session2:
> BEGIN;
> TRUNCATE bar;
> COMMIT;
>
> Session1:
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SELECT * FROM foo;
> SELECT * from bar;
> COMMIT;

Add something else into the mix, like if the transaction in Session2
updates "foo", and I think it will cause the MVCC violation you're
looking for.

Session0:
INSERT INTO foo VALUES(1);
INSERT INTO bar VALUES(2);

Session1:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM foo;

Session2:
BEGIN;
INSERT INTO foo VALUES(3);
TRUNCATE bar;
COMMIT;

Session1:
SELECT * from bar;
COMMIT;

Atomicity says that Session1 should either see 1 and 3 in foo, and
nothing in bar (if it happens after Session2); or it should see 1 in foo
and 2 in bar (if it happens first). So the rule that a SERIALIZABLE
transaction should get one consistent snapshot for its duration is
broken in this case.

I don't think it's an issue if only using READ COMMITTED (but I've been
wrong on similar issues in the past).

Regards,
Jeff Davis