From: | rmcm(at)compsoft(dot)com(dot)au |
---|---|
To: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com> |
Cc: | Rex McMaster <rmcm(at)compsoft(dot)com(dot)au>, Joachim Achtzehnter <joachim(at)kraut(dot)bc(dot)ca>, pgsql-general(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Revisited: Transactions, insert unique. |
Date: | 2000-04-26 23:17:45 |
Message-ID: | 14599.31001.686365.250319@fellini.mcmaster.wattle.id.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Sorry, mistake in my previous email -
> - only 1 row inserted
this was before the second commit. After both commits, 2 rows are
visible. Neither transactions can see effects of the other till both
are commited.
A: CREATE TABLE foo (id INTEGER);
===> CREATE
BEGIN;
===> BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
===> SET VARIABLE
INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
===> INSERT 959179 1
B: BEGIN;
===> BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
===> SET VARIABLE
SELECT * FROM foo;
===> 0 rows
A: SELECT * FROM foo;
===> 1 rows <== the row inserted in A:
B: INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
===> INSERT 959155 1
SELECT * FROM foo;
===> 1 rows <== the row inserted in B:
A: SELECT * FROM foo;
===> 1 rows
B: COMMIT;
===> END
SELECT * FROM foo;
===> 1 rows
A: SELECT * FROM foo;
===> 1 rows
COMMIT;
===> END
SELECT * FROM foo;
===> 2 rows
B: SELECT * FROM foo;
===> 2 rows
Ed Loehr writes:
> rmcm(at)compsoft(dot)com(dot)au wrote:
> >
> > Doesn't
> >
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> >
> > have to come within transaction - ie
> >
> > BEGIN;
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> >
> > In this order your test seems to behave correctly - only 1 row inserted.
>
> Yes, my initial ordering was in error. But even after reordering, the
> point is not that only 1 row was inserted, but rather that Transaction A
> was able to see the effects of transaction B when it clearly should not.
>
> Regards,
> Ed Loehr
--
Rex McMaster rex(at)mcmaster(dot)wattle(dot)id(dot)au
http://www.compsoft.com.au/~rmcm/pgp-pk
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2000-04-26 23:27:12 | Re: storing large amounts of text |
Previous Message | Tom Lane | 2000-04-26 23:04:22 | Re: [HACKERS] 7.0 weirdness (maybe solaris?) |