Re: serialization errors

On 31 Jan 2003, Greg Copeland wrote:

> 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.

It's basically becoming an academic point, the question in my mind was
whether or not the system is allowed to get to the point of diagnosing a
unique constraint violation and to not diagnose the serializability
violation. Using the serializability as run these two transactions in
order definition and a requirement to raise an exception if this is
impossible, the serializability violation occurs first (since constraints
are checked after the row is inserted and the serializability violation
happens at that moment). So at the least, both violations would have to
be diagnosed.

However, the wording in SQL99 is interesting in that it uses may, and I'm
not sure what level of requirement that places upon the implementation.
I see in 4.32 "The execution of a <rollback statement> may be initiated
implicitly by an SQL-implementation when it detects the inability to
guarantee the serializability of two or more concurrent SQL-transactions.
When this error occurs, an exception condition is raised: transaction
rollback -- serialization failure." That seems to imply that it's allowed
for an implementation to not raise an error, which seems to make the
guarantee of serialized execution meaningless.