3

We have a simple Java application developed using Spring Batch framework to load the data that we receive in form of files into the database. We have used H2 database to store the statistics of how many records read/loaded/failed. We get situations where we are required to load multiple files into different tables at same time, that means, running multiple instances of the same Jar file. The problem is, H2 database doesn't provide more than one connection at a time and it repeatedly throws the File is locked exception when running the second instance.

Platform: AIX

Java Version: 1.8

H2 DB Version: 1.4.193

H2 Config in application.properties

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:file:./h2/DataLoaderApp
spring.datasource.username=sa
spring.datasource.password=

I have tried different solutions but nothing seems to work at all.

Try 1: This did not work

Try 2: Appended FILE_LOCK=NO, it still throws File Locked exception though I do not see the *lock.db file being created.

Please help me out, Thanks a lot for your time.

Dilip Kumar
  • 31
  • 1
  • 8

1 Answers1

2

Take a look on H2 manual for server-mode connection.
Change your connection url to jdbc:h2:tcp://localhost/~/DataLoaderApp and before make a connection, H2db instance must be running. You can execute a standalone H2 instance before run your app or start H2 in main method:

public static void main(String[] args) {
        Server.createTcpServer().start();
        SpringApplication.run(YourApplication.class, args);
    }
M-Razavi
  • 3,327
  • 2
  • 34
  • 46
  • 1
    Can you suggest me any other options, because its just a data loader app and I don't think my people would accept having server and tcp connections and all for the requirement that we have. Is there any way to do it using embedded mode only ? Thanks – Dilip Kumar Sep 22 '18 at 11:50
  • @DilipKumar You know that H2db database doesn't provide more than one connection at a same time. You have to use server mode or mixed mode. – M-Razavi Sep 22 '18 at 13:37
  • I tried appending AUTO_SERVER=TRUE but, it did not work for me. It still reports the same error when multiple instances are run. – Dilip Kumar Sep 22 '18 at 19:57
  • @DilipKumar H2db can be started in main method, so you don't need to run separated H2 instance.I added it to answer. for more information https://stackoverflow.com/questions/37068808/how-to-start-h2-tcp-server-on-spring-boot-application-startup – M-Razavi Sep 23 '18 at 05:08
  • Ok this seems to be working for me. But, I want to get some clarity on whats happening here. I just changed the url to `spring.datasource.url=jdbc:h2:tcp://localhost/~/h2/DataLoaderApp` and added `Server.createTcpServer().start();` to main method. I tested this with running more than 5 instances of the Java app and it didn't throw the File Lock error that I was getting. I am assuming that each of my app instances might be starting its own instance of TCP Server/DBConnection connected to the same DB file. Is that correct. – Dilip Kumar Sep 23 '18 at 16:24
  • @DilipKumar you can debug it. I checked the source code of start() method and it has a mechanism to run only one server. https://github.com/h2database/h2database/blob/144f743ed27f57c27b4794ec984acf9997b726e4/h2/src/main/org/h2/tools/Server.java – M-Razavi Sep 25 '18 at 07:08
  • @DilipKumar Please accept this answer if it solved your problem. – M-Razavi Sep 25 '18 at 07:15
  • Sorry I am responding late, this solution did not work for me after doing several tests. This time I started receiving `java.net.ConnectException: Connection refused: connect: localhost` – Dilip Kumar Sep 27 '18 at 21:36
  • I have even tried with latest version of H2 database, no use. I should have not chosen H2 for my requirement. – Dilip Kumar Sep 27 '18 at 21:39
  • When used with `jdbc:h2:file:/home/DataLoaderApp;AUTO_SERVER=TRUE;DB_CLOSE_ON_EXIT=FALSE`, I sometimes see `Connection is broken: "java.net.ConnectException: A remote host refused an attempted connect operation"` especially when process A and B are started at a time but A is ending faster than B. So I see that above exception in process B console. – Dilip Kumar Sep 27 '18 at 21:50
  • @DilipKumar The H2 database is closed when the virtual machine exits normally. set "DB_CLOSE_ON_EXIT=FALSE" in your connection url. – M-Razavi Oct 01 '18 at 06:01
  • I am not getting why `Server.stop();` method does not really stop the server sometimes. – Dilip Kumar Oct 05 '18 at 15:30