Loop: Do some selects, including id on second iteration of the inserted id
Do the insert (function call), which also does a select on an id.
Save the newly inserted id for select on the next iteration. This id will be selected by the insert function on the next iteration, sometimes. It is being used the time that it fails.
end Loop;
do commit if the loop ever successfully gets done.
I'm presuming the OP is using the typical model of: conn = getConnection() id = doInsert(conn) rst = doSelect(conn, id) doSomething(rst) conn.commit() conn.relrease()
Robert DiFalco wrote > Two common cases I can think of: > > 1. The PERL framework is only caching the insert and does not actually > perform it until commit is issued.
Wouldn't the same mechanism cache the corresponding SELECT?
> 2. You really are not on the same transaction even though it appears you > are and the transaction isolation is such that you cannot see the insert > until it is fully committed.
Doubtful given the way most programs are coded (see assumption above) - the SELECT should be able to see the prior statement results whether committed or not.
The only thing I can think of on this line-of-though is that auto-commit is off and while the original INSERT succeeded the transaction it was in was not "COMMIT"ed and the connection used closed/returned-to-pool with an implicit ROLLBACK. Thus when the subsequent SELECT occurred the INSERT never appeared to happen.
Not knowing the whether the ID is visible post-program-completion limits the ability to diagnose, though.
