On Fri, 2003-01-31 at 00:40, Stephan Szabo wrote:
> 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).
Thank you for the followup. If you take a look at the section I quoted,
you'll note that you're not addressing the specific question even though
you are addressing the greater question. ;)
He asked why he was getting a constraint violation rather than a serial
violation. I simply attempted to help illustrate why it makes sense
that it is a unique key constraint violation as he's attempting to
insert the same value twice. As such, the fact that he's attempting to
do so within a pair of serialized transactions doesn't change the fact
that he is still attempting to insert a duplicate value.
Regards,
--
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting