H2 DB in Auto Server Mode

[Ashish Srivastava]

Hi All,

I am facing an issue with accessing H2 db ( opening connection for read/write ) from shared location from 2 processes concurrently. I have appended AUTO_SERVER=TRUE in url and same url is being used to open DB connection from 2 tomcat servers.

Example url - jdbc:h2:%base%data\nim-sm-customizations;AUTO_SERVER=TRUE

When 2 server processes try to open connection exactly at same time, I hit below issue -

Database may be already in use: "Locked by another process". Possible solutions: close all other connection(s); use the server mode [90020-164]

Can someone explain me why I hit above issue in AUTO_SERVER=TRUE ?

Quick response is highly appreciated.

Thanks

Ashish

[Ashish Srivastava]

Can someone help me please ?

[MNGoldenEagle]

 What does the JDBC URL resolve to when you actually run the code?  I'm guessing %base% is supposed to be an environment variable that's resolved by a shell script...

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Ashish Srivastava]

Hi,

thanks for reply. %base% variable we manipulate in java code to replace with actual shared path based for H2 db file based on some configuration. Si it will resolve to a proper path.

My main concern is - if 2 tomcat instances try to open h2 db in server mode right at the exact same time, will there be any problem of locking ?

The use case is - H2 db file with tables/data will be available in a common shared location accessible to both tomcat web apps and during servlet initialization, both read configuration data from H2 db and I see above error.

Thanks
Ashish

[Thomas Mueller]

Hi,

I suggest to use a newer version of H2. According to the error code, you use version 1.3.164, which is very old, from 2011. Maybe that feature was not available back then. 

Regards,

Thomas

[Ashish Srivastava]

Hi,

I will try with latest version of h2 jar and get back.

But as such, based on the theoretical explanation of the feature, my use case should be fulfilled by AUTO_SERVER=true ?

Thanks

Ashish

[Thomas Mueller]

Hi,

Yes it should work. I'm wondering which shared file system you use?

Regards,

Thomas

[Dipan Bhattacharyya]

Hi All,

I am facing a similar problem with H2 running in the Automatic Mixed mode. Let me provide the context first.

1. Using H2 as the database in a Spring MVC App, with MyBatis ORM (via MyBatis-Spring) with data source ppoled using HirakiCP

2. Spring version 4.1.6-RELEASE. 

3. MyBatis-Spring - 1.2.3

4. Mybatis - 3.3.0

5. HirakiCP - 2.4.1

6. H2 - 1.4.189

I am using H2 in an encrypted mode. The URL is jdbc:h2:file:$dbhome}/databases/${dbname};CIPHER=AES;IFEXISTS=TRUE;AUTO_SERVER=TRUE

The app runs fine when I start it up. With the App running, when I try to connect my IDE (IntelliJ IDEA 14 Ultimate) to the database, with exactly the same URL, I get an error: "unexpected status 16843008", and the IDE fails to connect. 

When I restart the app again, this time WITHOUT the "AUTO_SERVER=TRUE" directive, both the app and the IDE (which is also now changed to remove the AUTO_SERVER directive) connect and work fine. 

As of now - this setup provides me with the intended functionality. However, is this the right way? I am planning to deploy a few auto-scheduled spring batch jobs that connect to this database, so I am keen to ensure that I do it the right way. 

I posted this in the same thread because the topic is the same. I hope that is the right thing to do. I can post it separately as well if that is required. 

Thanks!

[Thomas Mueller]

Hi,

The "unexpected status" exception usually occurs if you have "incompatible" H2 client and server. You should to ensure all applications that use H2 are using a similar version.

Background: in theory, old and new versions of H2 should work together (old server with new client, new server with old client), because when they connect, they have a handshake to define which protocol version to use. However, in some versions of H2 there was a bug where the protocol version was not checked correctly in some cases.

Regards,

Thomas

--

[Rinse Lemstra]

Op dinsdag 6 oktober 2015 07:45:03 UTC+2 schreef Thomas Mueller:

Hi,

Yes it should work. I'm wondering which shared file system you use?

Regards,

Thomas

There are versions of SMB witch use caching. On these versions it can take up to 10 seconds before a new file becomes visible for other users, also file delete is a affected by this delay.

regards,

Rinse