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

Thread view:

ndamianov - 15 Nov 2006 08:27 GMT

Hi All,

For about a week and a half I have been trying to solve a mysterious
issue with unique constraint violations on our database server without
any luck.

We basically have a simple table with a unique primary key (number) and
a few string columns that aren't part of the key.

With J2EE on the Sun App Server 7, we always make sure we get the
nextval from the table's sequence before inserting it into the table. I
have checked this numerous times.

The Oracle DB doesn't have any triggers or other procedures accessing
the table in any way.

>From time to time, we get a bunch of unique constraint key violations

in spaces of about 30 minutes after which its all good again...

Has anybody heard of this issue or know how to fix it?

Regards,

Nikk

Reply to this Message

joeNOSPAM@BEA.com - 15 Nov 2006 16:40 GMT

> Hi All,
>
[quoted text clipped - 19 lines]
>
> Nikk

Hi. It sounds like a thread safety issue. If multiple 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 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 Systems

Reply to this Message

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

Free Magazines