Re: Oracle 10 + Sun App Server 7: Unique Constraint Violation?!?

On Nov 15, 9:13 am, "Steve Howard" <stevedhow..._at_gmail.com> wrote:
> joeNOS..._at_BEA.com wrote:
>
> > Hi. It sounds like a thread safety issue. If multiple client threads
> > all ask the DBMS what the nextval is, and get the same value,
> > and then all try to send back a new row with that value, you'll
> > get your problem. You could run an experiment where you
> > add a string column, and inert the appserver's java thread ID
> > and a timestamp. Whenever your app gets the unique key
> > violation, query the row for what thread beat you to it, and
> > when.
>
> > Joe Weinstein at BEA SystemsHi Joe,
>
> If he is truly using Oracle sequences, then this won't occur, as they
> are incremented serially.
>
> Regards,
>
> Steve

But if his external client queries for a sequence value, does Oracle guarantee that a given value will only be returned to one query? If so, then it could be a client thread-safety issue where multiple threads are setting the same client variable with their (different) query values, but then they all use the one that is set by the slowest involved thread... The right way to do this is to never return the sequence value to the client, but do the whole logical insert operation in one DBMS call.
Joe Received on Wed Nov 15 2006 - 16:00:37 CST