Hello community,

I know there are lot of topics concerning "Session is closed" problems, but I'm not so sure my problem is the same, so I decided to post, because I'd really appreciate your help. Ok, I'll sum up the problem and hope you can help:

Environment:
MySQL 5.0.21
Tomcat 4.1.31
commons-dbcp-1.1
mysql-connector-java-3.1.11
Hibernate 3.05
Quartz 1.5.2

Additional notes beforehand:
- Webapplication using threadlocal pattern for session and transaction handling (encapsulated in a HibernateUtil class)
- The problem (see description below) occured virtually overnight. The application is productive since ~ 4 months, the respective relevant code has not been changed since then, and the quartz job has been working flawlessly over that time. The only relevant factor that changed that I am able to determine currently is that it is currently subject to long times of inactivity (no user requests)
- The problem occurs on the productive system and the test system (which has been inactive for a while too).

Problem description:
The problem occurs inside a quartz job that basically selects a random user out of the user base (which has not been selected before = query condition) and persists a reference to that user in a database table as well as an entry in the usersystemmessages table. The job is executed once at nighttime. After persisting the data, a mail is generated and sent to the user.
The weird behaviour now is:
Since a few days a SessionException (Session is closed; more info below) occurs during transaction commit. However, the data is saved to the database!
If the job is executed more regularly (I set up an execution intervall of e.g. 10 minutes on our test server), the job executes perfectly, without the exception. Although this already ensured me the code works fine, I double-checked the application code again - there is no explicit session close.

Here is the relevant exception excerpt:
RandomUserJob.execute(RandomUserJob.java:263)
at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: org.hibernate.SessionException: Session is closed
at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:131)
at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:175)
at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:154)
...

Solution approach:
I assumed that the problem occured due to the "increased" inactivity and MySQL's behaviour of closing connections on it's side after wait_timeout is reached (value: default 8 h).
Thus, after some research, I've changed the Commons DBCP configuration (listed below) to no longer use the autoReconnect feature and instead configured it to test for and remove idle connections older than 5 mins. To no avail, it seems, as the problem persists.

I would really appreciate any helpful input on this, because, unless I've overlooked something, I'm running out of sensible ideas to fix this.

Here's the relevant part of the Commons DBCP configuration (I'm leaving out the irrelavant parts for better readability):


<ResourceParams name="jdbc/SomeApplicationDS">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>25</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>1000</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>defaultAutoCommit</name>
<value>false</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>SELECT 1</value>
</parameter>
<parameter>
<name>testWhileIdle</name>
<value>true</value>
</parameter>
<parameter>
<name>timeBetweenEvictionRunsMillis</name>
<value>10000</value>
</parameter>
<parameter>
<name>minEvictableIdleTimeMillis</name>
<value>300000</value>
</parameter>

[ ... snip ... ]
</ResourceParams>

The reason that the DB change is still happening looks to me to be that you have autoCommit on, so each insert/update is committed indepently.

If this thread is very long lived but executes very rarely, perhaps the threadlocal pattern isn't appropriate for it. If you can, you should try to get a completely new session for this job, and close it once the job is done. The overhead is tiny, and seeing as there's no advantage having an existing session (you're not going to find anything in the session cache if you're loading a different user every time) I can't think of a reason to go with the threadlocal pattern. And seeing as the session open and close are 1) very close together, and 2) under your control, you shouldn't run any risk of a SessionException.

Autocommit is definitely off, via the pool configuration parameter:
<parameter>
<name>defaultAutoCommit</name>
<value>false</value>
</parameter>
Hibernate's debug messages do also verify that.

The thread isn't long lived. The job executes rarely, but always in a new thread, and it hardly takes a second to fully execute. Also, a new session and an explicit session close is performed during each job execution.

Actually, I don't think it's any of your suggestions. Like I said before, the code has not been changed, and it ran about 4 months without this error. Weird.

Thank you for the input though, nevertheless.