From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Greg Copeland <greg(at)CopelandConsulting(dot)Net> |
Cc: | Ryan VanderBijl <rvbijl-pgsql(at)vanderbijlfamily(dot)com>, PostgresSQL General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: serialization errors |
Date: | 2003-01-31 06:40:44 |
Message-ID: | 20030130223523.T39697-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 30 Jan 2003, Greg Copeland wrote:
> On Thu, 2003-01-30 at 13:00, Ryan VanderBijl wrote:
> > I guess I'm starting to sound like a broken record here, but I'm struggling
> > to understand why it should say unique constraint violated instead of serial.
>
> Because, the "select max(node_order)+1" will select the identical value
> in multiple sessions. Done concurrently, it results in unique
> constraint violation on your insert, even if the inserts are serialized.
I think his argument is that since the two transactions (as a whole)
should be serialized, he shouldn't get the same max(node_order) in both
since in either order of serialization of the two transactions you can't
get 5 from both selects (one should return 6).
The problem with this is that it's probably pretty unimplementable, since
it would mean forcing a serialization error in any case that a
modification of a table would have changed a past select in a
serializable transaction which changed a table such that it would have
changed a past select for this serializable transaction.
So:
T1: select * from a where a=3;
T2: select * from b where b=4;
T1: insert into b(b) values (4);
T2: insert into a(a) values (3);
would be a serialization error, however:
T1: select * from a where a=3 and b>4;
T2: select * from b where b=4;
T1: insert into b(b) values (4);
T2: insert into a(a,b) values (3,2);
is not.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Meskes | 2003-01-31 07:00:11 | Re: Basic SQL join question |
Previous Message | Shridhar Daithankar<shridhar_daithankar@persistent.co.in> | 2003-01-31 06:33:40 | Re: Documentation needs significant improvement |