Session never dies

cash1981 · **Joined:** Mon Sep 04, 2006 7:18 am **Posts:** 45

Hibernate version: 3.1

Hello.
I have a J2EE application. And sometimes when I execute a query, the query just keeps going and going, it doesnt stop.

It can be a simple query like getSomethingById
And then I will have to close the browser, and then when I try to execute the same query, it is locked.

Even if I try to do it in the database, its locked. So it means the session is still trying to execute the query.
I have put a timeout on 100 seconds or something, but this doesnt seem to help.

Here is my hibernate.properties file:

Code:

######################
### Query Language ###
######################

## define query language constants / function names
hibernate.query.substitutions yes 'Y', no 'N' 

#####################
### JDBC Settings ###
#####################

## specify a JDBC isolation level
#hibernate.connection.isolation 4

## set the JDBC fetch size
hibernate.jdbc.fetch_size 5
hibernate.max_fetch_depth 5

## set the maximum JDBC 2 batch size (a nonzero value enables batching)
hibernate.jdbc.batch_size 5

## enable batch updates even for versioned data
hibernate.jdbc.batch_versioned_data true

## print all generated SQL to the console 
hibernate.show_sql false

## format SQL in log and console 
hibernate.format_sql false

#################################
### Plugin ConnectionProvider ###
#################################

## use a custom ConnectionProvider (if not set, Hibernate will choose a built-in ConnectionProvider using hueristics)
hibernate.connection.provider_class org.hibernate.connection.C3P0ConnectionProvider

##########################
### c3p0 configuration ###
##########################
hibernate.c3p0.min_size = 10
hibernate.c3p0.max_size = 100
hibernate.c3p0.timeout = 100
hibernate.c3p0.max_statements = 50
hibernate.c3p0.idle_test_period = 30

##########################
### Second-level Cache ###
##########################

## optimize chache for minimal "puts" instead of minimal "gets" (good for clustered cache)
hibernate.cache.use_minimal_puts true

## enable the query cache
hibernate.cache.use_query_cache true

## store the second-level cache entries in a more human-friendly format
hibernate.cache.use_structured_entries true

## choose a cache implementation
hibernate.cache.provider_class org.hibernate.cache.HashtableCacheProvider

## enable use of JDBC 2 scrollable ResultSets (specifying a Dialect will cause Hibernate to use a sensible default) 

## use streams when writing binary types to / from JDBC 
hibernate.jdbc.use_streams_for_binary true

And this is my hibernate.cfg.xml file:

Code:

<?xml version='1.0' encoding='ISO-8859-15'?> 
<!DOCTYPE hibernate-configuration PUBLIC 
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN" 
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> 
<hibernate-configuration> 
    <session-factory> 
        <property name="connection.url">jdbc:postgresql://xxx</property>
      <property name="connection.username">xxx</property>
      <property name="connection.driver_class">org.postgresql.Driver</property>
      <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
      <property name="connection.password">xxx</property>
        <property name="hibernate.connection.useUnicode">true</property> 
      <property name="hibernate.connection.characterEncoding">latin1</property>
      <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

      <property name="current_session_context_class">thread</property>
      <property name="cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
      
        <!-- Map Båtkatalogen pojo classes --> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/DrevData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/KategoriData.hbm.xml" />
        <mapping resource="no/vimenn/baatkatalogen/pojos/UtstyrData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ProdusentData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/BaatData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/FabrikatData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ImportorData.hbm.xml" />
        <mapping resource="no/vimenn/baatkatalogen/pojos/MotorData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/StedData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ForhandlerData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ForhandlerMotorData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ForhandlerImportorData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ForhandlerBaatData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/ImportorBaatData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/TestData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/MotorTestData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/BaatTestData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/BaatMotorTestData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/MotorDrevData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/BaatUtstyrData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/BaatMotorData.hbm.xml" /> 
        <mapping resource="no/vimenn/baatkatalogen/pojos/BrukerData.hbm.xml" /> 
        
    </session-factory> 
</hibernate-configuration>

Can anybody see why my timeout doesnt affect?

The code that sometimes affect is something like this:
(Note its not only this code, it can be any of my queries)

Code:

    public void removeUtstyr(UtstyrData utstyr) throws BaatkatalogenException {
   Session session = HibernateUtil.getSessionFactory().getCurrentSession();
   session.beginTransaction();
   session.delete(utstyr);
   session.getTransaction().commit();
    }

Hope somebody can help

anandbn · **Posted:** Wed Mar 28, 2007 11:46 am

You might want to print the SQL query being executed. The locking is because "Delete" in most databases lock the row till the delete operation is completed. In addition since you have included it in a transaction and the "commit" does'nt occur properly (because you close the window etc.).

What I would reccomend is unlock the rows, and then try to do the "Delete" and capture the SQL. Then run the SQL using a standalone SQL client and determine if that SQL takes long to run and why that is the case. Once you have that figured out the rest should be easier to fix.

cash1981 · **Joined:** Mon Sep 04, 2006 7:18 am **Posts:** 45

Thank you for your answer. Please see comments below.

anandbn wrote:

You might want to print the SQL query being executed. The locking is because "Delete" in most databases lock the row till the delete operation is completed.

The locking doesnt always appear when I am trying to delete. It may happen when I am just trying to retrieve some data.

anandbn wrote:

What I would reccomend is unlock the rows, and then try to do the "Delete" and capture the SQL.

How can I unlock the rows?

anandbn wrote:

Then run the SQL using a standalone SQL client and determine if that SQL takes long to run and why that is the case.

I have done this. And the queries run fine. They even run fine from my J2EE application. Its very random that some columns get locks.
My sql looks just fine, I have printed it out and it looks ok.

Thats why I am a bit confused of what is going on.
I think If I had a timeout on the session/transaction, it would greatly help.
But I don't know how I can do this other than saying timeout on c3p0.
But c3p0 only timeouts the connections which are in the pool.

anandbn · **Posted:** Wed Mar 28, 2007 12:10 pm

You might want to engage a DBA to determine if there are issues on the database side.

I think they can set transaction timeouts on the database side after which the transaction will auto-rollback. Not familiar with how JTA accomplishes this.