Tracing sequences (was re: freelist tesing)

Anjo has convinced me that I was indeed barking up the wrong tree (and even if I was the tree didn't care)

He lead me into tests that shows my problem exhibits exactly the same characteristics of trying to insert the same value into a unique index simultaneously. That somehow, one user does an insert with one value, and before he commits, another session (usually different user -- not always) tries to insert the same value.

Previously I had examined the developers code and convinced myself that this could not be the case as he selects a sequence nextval into a variable, then immediately uses that variable to create a the value list for the insert.

I haven't found anything that makes me want to mistrust a sequence nextval. (If anyone knows of one in 8.0.5 on Solars 2.7 please let me know.) So I've got to mistrust something going on in the developers VB based COM object running under MTX serving up ASP pages for IIS.
(Notice the long string of MS products there and you can guess how
that influences my suspicions.)

Since I've come to this realization, the event has not recurred, so I don't have any statistics. But we do know that when it starts, we see incidents from all 8 webservers simultaneously. Past evidence collected for a blocker and a blocked session shows that they were on the same webserver, but that's just 1 data point and we don't have any other to confirm or deny that relationship. Also when it starts, it happens at a furious rate, dozens of sessions at once. Then it suddenly stops. Curiously, the same applications on the same webservers are handling 30 other databases which experience no problems. This points me back to the database. <sigh>

One of the things I would like to do, is to record what the database thought it answered for the select of the sequence nextval, and have that for comparison when the application tries to do its insert. My dream would be to have a trace/log/journal/something that recorded the nextval returned,user,session,serial#,and sysdate for every time the sequence was read. This would allow me to see discrepancies in the select/insert and sessions that were trying to insert without actually making the select.

Has anyone tried this level of tracing/logging before?

-rje

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robert Eskridge
  INET: bryny_at_dfweahs.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).