I have some code that commits some data to the database, inside a transaction (Oracle 8i).

The code works for a while, and then stops. What I mean by "working" is that the transaction is committed, and I can see it in TOAD. After a few hours, I can't see it in TOAD, but the code can see it. I am running as read-committed (the Oracle default) so TOAD should be able to see it.

Here's the code that executes the transaction:


BLExchangeSites bles = (BLExchangeSites)iterBoats.next();
bles.setPicturesProcessedInd(new Integer(1));
bles.setPicturePriorityInd(new Integer(0));
bles.setImageProcessedDate(new java.util.Date());

BoatListing bl = bles.getBoatListing();
bl.setLastModifiedDate(new java.util.Date());

Transaction tx = oSession.beginTransaction();
oSession.save(bles);
oSession.flush();
tx.commit();

m_sLogger.info("\tPicturesProcessedInd for this boat is " + bles.getBooleanPicturesProcessedInd() + ", or " + bles.getPicturesProcessedInd());

m_sLogger.info("\tDone saving boat " + bl.getBoatListingId().toString());

bles = (BLExchangeSites)oSession.load(BLExchangeSites.class, bles.getId());
m_sLogger.info("DB Values: " + bles.getPicturesProcessedInd() + ", " + bles.getImageProcessedDate());


Here's the tracing that log4j dumps out...

15-Jun-2006 13:20:30 1375 Images are done; saving [1] boats to database with updated image info...
15-Jun-2006 13:20:30 1396 PicturesProcessedInd for this boat is true, or 1
15-Jun-2006 13:20:30 1398 Done saving boat 10277852
15-Jun-2006 13:20:30 1401 DB Values: 1, Thu Jun 15 13:20:30 PDT 2006


Auto-commit is turned off. As you can see, I re-load the object and test the value (I added this once I discovered this behaviour).

As I said, this works for a few hours. And then stops. Over 900 rows were committed today - I've been stopping and starting it.

Inconsistent behaviour like this is really tough to track down. Has anyone seen anything like this before? I'd appreciate any advice that might point me in the right direction. I've just turned on debugging, but as I said, it takes several hours for this to start happening...

David

Hello,

I suppose that you executed an hibernate query to get your "bles" instance from the database, what i advise you is to start your transaction before this read.

Regards,

I don't think that's a very good idea.

Sessions are supposed to be short-lived (a unit of work), and transactions exist only within sessions, which means that they shouldn't be long. Perhaps I should try closing and re-opening my session for each iteration.

Just to follow up....

Closing and re-opening the session for each iteration solved the problem.

Turning on debugging didn't provide any insight as to why the data stopped being committed after an hour or so.

I am not sure where the bug is, but there is definately a bug if the transaction returns ok from a "commit" yet the data is not committed.

At the very least an exception should be thrown.