Re: Oracle sequence problem ... I think ?

Without meaning any insult to your code or developers, "that's impossible".

Make sure code is getting sequence numbers via a

select sqname.nextval from dual

or whatever, and is NOT EVER storing a sequence number in some variable, once gotten, and carrying it outside the transaction that INSERTS the row and COMMITs (in other words, the logical unit transaction; and that failed LUW's are rolled back).

Make sure nothing wierd like shared common memory and a sequence number once pulled, is stored there and grabbed by a different thread.

Make sure you don't have tricky (and erroneous) code around that assumes that

currval - 1 = lastval

there is no such thing, which is why

select blah.currval from dual

is undefined/error unless a

select blah.nextval from dual

has been executed; in which case currval contains the value of the last selected new sequence, and Oracle allows the select blah.currval construct only as a convenience as opposed to maintaining it in your own variables; and programmers should be strongly encouraged to use that to refer to the value of the "last selected sequence" in their code, within LUW blocks defined in ending in either explicit commits or rollbacks, and exception handling always resulting in the rollback of the entire :logical unit of work.

The only explanation I can offer is somewhere in your code, a sequence number value is stored in some variable rather than being pulled out of Oracle, and it's being carried across a defined LUW and being used again by accident in a subsequent transaction, skipping the logic to initialize the variable and stuff a newly selected sequence number into it.

I've seen this problem more times than I can count (maybe I should use a sequence number! oh, that would only guarantee uniqueness....) and it boils down to either programmers trying to outsmart Oracle, or just a plain honest bug.

That's one reason I suggest using a simple, short trigger to populate the sequence number, and if it's a multi-table insert, etc, having the trigger manage the insertion / update of rows into the related tables and the sequence number.

Either that, or, "your Oracle's broken".

RSH, "Ed Barrett" <ed.barrett_at_bigfoot.com> wrote in message news:aa18qo$4ce$1_at_knossos.btinternet.com...
> Guys,
>
> All very good points and taken on board.
>
> What I still cannot understand is that when two transactions are run
> serially (from where I stand as a human - one runs and then the other a
few
> seconds later) that the sequence number is the same.
>
> That is why I asked if there was any way of seeing what the actual SQL is
(I
> am running through a JDBC driver).
>
> Alternatively, is there a way to switch a log on in Oracle so that all
> failed SQL statements and the reason for failure is outputted?
>
> regards
>
> Ed
>
> "RSH" <RSH_Oracle_at_worldnet.att.net> wrote in message
> news:QHtw8.38141$Rw2.2887272_at_bgtnsc05-news.ops.worldnet.att.net...
> > Remember, the Oracle Sequence function is designed to produce UNIQUE
> > numbers, not necessarily sequential numbers assigned to each transaction
> in
> > order in which they were made. Employing the CACHE feature reduces disk
> > overhead but does not necessarily ensure any better luck with
> serialization.
> > (Though with any cache enabled at all, any unused
(unrequested/unrequired)
> > sequences vanish when the DB is brought down or it crashes, and the next
> > block of 10 or 100 or whatever is preallocated in memory.)
> >
> > If you need serialization as distinct from uniqueness, you might wish to
> > consider SYSDATE to populate a DATE column in your record, and by all
> means
> > also use a column populated by a sequence number as well; the
granularity
> of
> > DATE would generally preclude any two people buying Angelfish at the
same
> > instant, certainly checking out from the same cash register; adding
> > STORE_ID, POS_ID, EMP_ID to that would pretty much conclude the deal, I
> > should think.
> >
> > An ON INSERT trigger on your sales table could be conveniently used to
> > populate the DATE and whatever you call your sequence number column..
> >
> > Depending on how large your application design and architecture are, the
> > sequence number could be used as a convenient way to denormalize and
> > collapse keys instead of carrying them all through the entire database.
> >
> > RSH.
> >
> >
> > "Ed Barrett" <ed.barrett_at_bigfoot.com> wrote in message
> > news:a9mrhp$rsd$1_at_knossos.btinternet.com...
> > > I am posting here in the hope that I have hit a simple Oracle
> > configuration
> > > issue - one that is not simple to me !!
> > >
> > > I have written a test to perform the same functions twice in the Java
> > > reference application - Pet Store (add an AngelFish to a basket, go to
> > > checkout, log on, commit purchase, log out).
> > >
> > > When I run the test and tell it to run twice sequentially I get the
> > > following error on the second commit: (please scoll down for more
> > > information)
> > >
> > > <18-Apr-02 16:54:16 BST> <Error> <HTTP>
> > > <[WebAppServletContext(1628959,petstore,
> > > /petstore)] Servlet failed with ServletException
> > > javax.servlet.ServletException: MainServlet: unknown exception:
> > > com.sun.j2ee.blu
> > > eprints.petstore.control.exceptions.EStoreAppException
> > > at
> > > com.sun.j2ee.blueprints.petstore.control.web.MainServlet.doProcess(Ma
> > > inServlet.java:125)
> > > at
> > > com.sun.j2ee.blueprints.petstore.control.web.MainServlet.doGet(MainSe
> > > rvlet.java:91)
> > > at

javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> > > at

javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > > weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
> > > pl.java:265)
> > > at
> > > weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
> > > pl.java:200)
> > > at
> > > weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppSe
> > > rvletContext.java:2495)
> > > at
> > > weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestIm
> > > pl.java:2204)
> > > at

weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
> > > at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)
> > > >
> > >
> > > Through investigation I have identified that the underlying exception
is
> > > being thrown from the OrderDAOOracle class in the insertOrder method.
I
> > > have trapped the SQL statements beign issued and they are identical
with
> > the
> > > order number being generated with "ordernum.nextval".
> > >
> > > Would I be right in thinking I have hit a duplicate key (row?) error
and
> > if
> > > so anyone care to guess why? Is there a problem with Oracle
> sequencing -
> > > these tests are running sequentially as opposed to in parallel ?
> > >
> > > Regards
> > >
> > > Ed
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Mon Apr 22 2002 - 13:48:39 CDT