jdbc connection not being released after indexing

gcatanese · **Posted:** Wed Mar 16, 2011 11:40 am

Hi
I have a session leaking (Oracle DB, Hibernate Search 3.3.0) after a manual re-indexing.

In my app I run a Lucene index update every 60 min using a Java thread

Indexing code:

Code:

private void batchIndex(Criteria query, Integer batchSize) {
        Session session = null;
        int batch = 0;

        try {
            session = getSession();  // using  SessionFactoryUtils.getSession(sessionFactory, true) 

            FullTextSession fullTextSession = Search.getFullTextSession(session);

            query.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
                    .setCacheMode(CacheMode.IGNORE) 
                    .setFetchSize(batchSize)
                    .setFlushMode(FlushMode.MANUAL); 

            ScrollableResults scroll = query.scroll(ScrollMode.FORWARD_ONLY); 

            batch = 0;
            while (scroll.next()) {
                batch++;
                fullTextSession.index(scroll.get(0)); //indexing of a single entity
                if (batch % batchSize == 0) {
                    // commit batch
                    fullTextSession.flushToIndexes();
                    fullTextSession.clear();
                }
            }

            // flush what is left
            fullTextSession.flushToIndexes();
            fullTextSession.clear();

            // close to release closing DB open cursor
            scroll.close();

        } finally {
            try {
                // close to release JDBC connection
                //closeSession(session);  // using  SessionFactoryUtils.closeSession
            } catch (Exception e) {
                logger.warn("AbstractIndexer.batchIndex - Exception while closing the Hibernate Session");
                logger.warn(e);
            }
        }

        logger.info("Indexing work completed  for " + getIndexedClass() + ". " + batch + " entity indexed.");
    }

Whenever the index is not updated (no db changes) I can see the jdbc connection being released:

Code:

2011-03-16 16:24:22 INFO  indexer.AbstractIndexer -> Indexing work completed  for class com.sapienza.dccm.model.entity.Document. 0 entity indexed.
2011-03-16 16:24:22 DEBUG jdbc.ConnectionManager -> releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2011-03-16 16:24:22 DEBUG jdbc.ConnectionManager -> transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!

if there is a change (therefore an update to Lucene index) the JDBC connection doesnt seem to be returned back to the pool...

If I un-comment //closeSession(session) then the JDBC connection seems to go back to the pool but in this case (still figuring out why) my thread dies.

My question:
- why do I need to close the session manually in the second case only (index update is required) but doesnt seem to be the same in the first case?

Tnx in advance

Beppe

sanne.grinovero · **Posted:** Wed Mar 16, 2011 12:40 pm

Hi Beppe,
welcome.
If you don't close the Session, I'm not surprised that it doesn't get back to the pool.

You should investigate why your thread dies. Wrap the whole body of your thread with a try/catch statement able to log all kind of errors, usually if a thread dies it's because some exception occurred: not being your main thread, you won't get a stacktrace.

I think it might have something to do with the way you get the Session.
I don't know what this means:

Code:

SessionFactoryUtils.getSession(sessionFactory, true)

?
make sure that the "getSession" is actually opening a new Session for you. otherwise the close() will prevent the second indexing from working fine.

Did you try the MassIndexer ? should be much easier than to deal with your own threads, and is likely faster too.

gcatanese · **Posted:** Wed Mar 16, 2011 5:03 pm

Ciao Sanne :-)

I need to update incrementally the index every hour (whilst the app is running) so I dont think the MassIndexer suits me.

Tnx for the advice, I am looking into my thread now, it is possibly the way I open the session: SessionFactoryUtils.getSession(sessionFactory, true) .. I am using the getSession() method from SessionFactoryUtils, which returns the session bound to thread, it doesnt seem right like you say, I would lose the session after the first iteration

Beppe

sanne.grinovero · **Posted:** Wed Mar 16, 2011 7:07 pm

yes that must be it,
fammi sapere come va :)

vriendelijke groeten,

gcatanese · **Posted:** Fri Mar 18, 2011 6:58 am

That did it :-)

I open a new session and process my index updates, closing the session (finally), a bit of code cleaning and refactoring, now it is looking better

Grazie ancora ;-)

Beppe