in-memory db issue with JTA and non-auto-commit

[steve.ebersole]

I am having an issue using H2 (1.2.145) in in-memory mode with JTA.  This is part of the Hibernate testsuite.  Initially the JTA tests against H2 were erroneously passing because the set up was incorrectly using auto-commit.  So I tried I manually setting the auto-commit of Connections obtained from the driver manager to false, at which point the tests started failing.  The manner in which they failed indicate that data committed in one transaction was not visible in a second transaction as if the database was dropped between.

Note that this is NOT using H2 JTA/XA support.  It is simply using H2 connections obtained via DriverManager wrapped in an Enhydra DataSource.  The url is 'jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MVCC=TRUE'.

Anyone see anything obvious?  If it weren't for the 'DB_CLOSE_DELAY=-1', I would think the problem was the data cache being dropped across the multiple connections.  That or somehow the commit call on the connection is not being handled properly.

[steve.ebersole]

By the way, here is the relevant logging:

++++++++ Obtained connection -> conn2: url=jdbc:h2:mem:db1 user=SA
17:07:37,630 DEBUG SQL:104 - insert into Item (description, name) values (?, ?)
++++++++ Releasing connection -> conn2: url=jdbc:h2:mem:db1 user=SA
++++++++ Obtained connection -> conn3: url=jdbc:h2:mem:db1 user=SA
17:07:37,755 DEBUG SQL:104 - select item0_.name as name0_, item0_.description as descript2_0_ from Item item0_
++++++++ Releasing connection -> conn3: url=jdbc:h2:mem:db1 user=SA

The test in question fails because the SELECT fails to find any rows even though we clearly see the INSERT.

[steve.ebersole]

I just discovered TRACE_LEVEL_FILE.  I set that to 4 and see that in fact the connections are being rolled back :(

I'll have to investigate some more to see who is dropping the ball on the commit.

On Monday, March 26, 2012 5:06:43 PM UTC-5, steve.ebersole wrote:

[steve.ebersole]

I figured it out.  Was a misunderstanding of how Enhydra (XAPOOL) works.

[Noel Grandin]

On 2012-03-27 00:06, steve.ebersole wrote:
>
> Note that this is NOT using H2 JTA/XA support. It is simply using H2
> connections obtained via DriverManager wrapped in an Enhydra
> DataSource. The url is 'jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MVCC=TRUE'.
>
> Anyone see anything obvious? If it weren't for the
> 'DB_CLOSE_DELAY=-1', I would think the problem was the data cache
> being dropped across the multiple connections. That or somehow the
> commit call on the connection is not being handled properly.
>

Do you realise your URL says to __negative__ one seconds for the close
delay?
That means that the moment that the last connection closes, the DB will
be destroyed.

I think you should be using __positive__ one seconds, and I've added
some code to the next version of H2 to catch such mistakes i.e.
DB_CLOSE_DELAY must be >= 0

[steve.ebersole]

Hey Noel, you might want to read through http://www.h2database.com/html/features.html#in_memory_databases

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

[Noel Grandin]

Oops, thanks for that.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/kYET7JiZTakJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[raj]

I have following settings in my persistence.xml, but still data isn't there.

<properties>

            <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />

            <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MVCC=TRUE" />

            <property name="javax.persistence.jdbc.user" value="sa" />

            <property name="javax.persistence.jdbc.password" value="" />

            <property name="hibernate.show_sql" value="true" />

            <property name="hibernate.hbm2ddl.auto" value="create" />

        </properties>

Thanks.

[Thomas Mueller]

Hi rajandeepvij,

Please don't hijack Steves thread. Please use a new subject.

> I have following settings in my persistence.xml, but still data isn't there.

> jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1;MVCC=TRUE

Well, this is an in-memory database.

Regards,
Thomas