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.