1

I want to use h2 database for multiple spring boot applications. I have already read a couple of articles where they tell you how to configure h2 db, but with in-memory database where the data storage is temporary. I just want to know if it is possible to have persistent storage, with multiple spring boot applications for h2 db. Any help or link would be appreciated. Thanks.

Improve this question

2 Answers 2

Reset to default
3

It seems that there is a way for multiple applications to have access to the same file for H2 database (not in memory database) using the property AUTO_SERVER=TRUE in the end of the datasource.url. This is called Automatic Mixed Mode for H2 database

Automatic Mixed Mode

Multiple processes can access the same database without having to start the server manually. To do that, append ;AUTO_SERVER=TRUE to the database URL. You can use the same database URL independent of whether the database is already open or not. This feature doesn't work with in-memory databases. Example database URL:

jdbc:h2:/data/test;AUTO_SERVER=TRUE Use the same URL for all connections to this database. Internally, when using this mode, the first connection to the database is made in embedded mode, and additionally a server is started internally (as a daemon thread). If the database is already open in another process, the server mode is used automatically. The IP address and port of the server are stored in the file .lock.db, that's why in-memory databases can't be supported.

The application that opens the first connection to the database uses the embedded mode, which is faster than the server mode. Therefore the main application should open the database first if possible. The first connection automatically starts a server on a random port. This server allows remote connections, however only to this database (to ensure that, the client reads .lock.db file and sends the random key that is stored there to the server). When the first connection is closed, the server stops. If other (remote) connections are still open, one of them will then start a server (auto-reconnect is enabled automatically).

All processes need to have access to the database files. If the first connection is closed (the connection that started the server), open transactions of other connections will be rolled back (this may not be a problem if you don't disable autocommit). Explicit client/server connections (using jdbc:h2:tcp:// or ssl://) are not supported. This mode is not supported for in-memory databases.

Here is an example how to use this mode. Application 1 and 2 are not necessarily started on the same computer, but they need to have access to the database files. Application 1 and 2 are typically two different processes (however they could run within the same process).

The problem OP faced was as he commented:

I am able to connect to the h2 database in my main application using file storage. However when I try to connect from my 2nd application to the same db, then the file lock error comes up

Reason was that this is allowed only on Automatic Mixed Mode which OP has not enabled.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks for your response. You were right on one part that I was using the same jdbc url on both the applications. However I am still getting the same error even after changing the url. This is my first application URL: server.port=8090 spring.datasource.url=jdbc:h2:file:D:/ATOP_BACKEND/database/db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE; this is my second application url: # For Tomcat server.port=9090 spring.datasource.url=jdbc:h2:tcp://localhost:8090/D:/ATOP_BACKEND/database/db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;
@Rik remove DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE; from the second url. It is not responsible for DB manipulation the second app
Removed it. Getting <Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Connection is broken: "unexpected status 1213486160">. Is my second url correct? I gave the port in which my first application is running.
@Rik try with jdbc:h2:tcp://localhost:8090/db for second url
@Rik try making both the 1st and the 2nd url in the following form spring.datasource.url=jdbc:h2:file:D:/ATOP_BACKEND/database/db;AUTO_SERVER=TRUE . If it works I can explain why it works then
|
3

h2 can store data in a file or in-memory. For file based storage, the jdbc url should be in the format jdbc:h2:[file:][]. For in-memory, the jdbc url looks like jdbc:h2:mem:. See http://www.h2database.com/html/features.html#database_url for details on the database url format.

Improve this answer

2 Comments

Thanks for your response. I have already checked these documentations though. I am able to connect to the h2 database in my main application using file storage. However when I try to connect from my 2nd application to the same db, then the file lock error comes up. If i close the first application then my 2nd application is able to connect without any issue. I have tried different url attributes like AUTO_SERVER=TRUE. FILE_LOCK=NO. But no luck using these as well. I just want to know if it is even possible or not, since there are no clear documentation regarding this.
@Rik check my answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.