How to fix HSQL DataSource + TxM where identity al

Question

I am using Hibernate to do my ORM stuff w/ HSQL for tests. It seems that a connection is fetched to do the insert and then returned. Straight after that HIbernate gets a connection then tries to fetch the identity from HSQL but that returns 0 which is obviously wrong.

WHen i was running w/out a tm and datasource using a plain pooled connection everything worked but with the new tm + ds i am getting this problem.

Answer 1

The answer is the DataSource should be tx aware, so that connections are sticky for each new tx. The original problem was a different connection was used to fetch the identity of the new row which of course resulted in 0 being returned. If the connection remained sticky for the initial insert and call identity then everything works.