Hi.
I'm hoping that someone might be able to point me in the right direction with this issue.
I'm in the process of migrating an application from WebSphere 5.1 to Glassfish 3.0.1
So far it has gone fairly smoothly until last week.

My setup:
Galssfish 3.0.1 full profile
mysql jdb driver 5.1.12
Connection pooled with thread association enabled.
Hibernate 3.2.7 configured with a jndi connection pool via glassfish using CMTTransactionFactory. Optimistic locking with version columns.
Mysql 5.1.43. All tables INNODB.
Stateless session ejbs used for transaction demarkation.

The issue:
Every time I use a finder method (hql) during a process that modifies data, I get a StaleObjectStateException (org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect))
It happens consistently when the following pattern is executed:
1) modify some data.
2) Use a finder (hql) method
3) modify some more data

When the hql is executed hibernated flushes the session to the database to ensure that no stale data is return by the query.
This flush and all other queries inside the ejb method all use the same jdbc connection (connection 1).
But when the ejb method completes and the container calls the completeNewTx / commit and hibernate then flushes the remain data a separate connection is then used (connection 2). Because the connection 1 is not committed yet, connection 2 can not see the changes that connection 1 made, which then causes the optimistic locking to fail as the expected version number doesn't match. Then the transaction from connection 1 is rolled back, but connection 2 is not. Any queries that are executed on connection 2 are not rolled back and leave the database in a inconstant state.
It seems like the flush on commit is being executed outside of the JTA transaction context. But not quite as when then exception is thrown the transaction (connection 1 that is) is actually rolled back.
Very odd.

If no hql is used then it works as expected, but a separate connection is still used at commit.
If I manually flush the session at the end of the ejb method, it works as there are no more pending writes to be written at commi.
If I set the hibernate flush mode to 'COMMIT' then it works as well, but then the hql methods return stale data, and a separate connection is still used a commit.

I wanted to test this on Glassfish 2.1 but have been unable to as unfortunately have made use of some v3 specific features.

In WebSphere the same connection is used for the duration of the ejb call, including when the session is committed.
I was under the impression that the same connection should be used when the 'associate connection with thread' was used.

Has anyone seen this behaviour before? Any way to force the Glassfish connection pool to return the same connection? Or force Hibernate to ask for the same connection?
It appears to me to be a bug in Glassfish, but as I'm very new to Glassfish I can't say for sure. Could be a hibernate bug as well.

Any assistance would be greatly appreciated.

Cheers,
Jason

Update:
It is a bug with Glassfish v3.
I have successfully run the same code on v2.1.1 without issue.
v3 is using a second db connection when it should be reusing the same connection associated with the jta transaction and thread.

cheers,
Jason