H2 database error: Database may be already in use:

Question

I am trying to use the H2 database from a Java application.

I created the database and its tables through the H2 Console and then I try to connect from Java using

Connection con = DriverManager.getConnection("jdbc:h2:~/dbname", "username", "password");

However I receive the following error:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Database may be already in use: "Locked by another process". Possible solutions: close all other connection(s); use the server mode [90020-161]

I tried to delete the dbname.lock.db file but it is automatically re-created.

How can I unlock the database to use it from my Java program?

Answer 1

Ran into a similar issue the solution for me was to run fuser -k 'filename.db' on the file that had a lock associated with it.

Hope this helps!

Answer 2

You can also delete file of the h2 file database and problem will disappear.

jdbc:h2:~/dbname means that file h2 database with name db name will be created in the user home directory(~/ means user home directory, I hope you work on Linux).

In my local machine its present in: /home/jack/dbname.mv.db I don't know why file has a name dbname.mv.db instead a dbname. May be its a h2 default settings. I remove this file:

rm ~/dbname.mv.db 

OR:

cd ~/ 
rm dbname.mv.db 

Database dbname will be removed with all data. After new data base init all will be ok.

Answer 3

I ran into similar problems running with ORMLite from a web application. I initially got stuck on the syntax to use server mode in the url. The answers above helped with that. Then I had the similar user/password error which was easier to figure out. I did not have to shut anything down or erase any files. The following code worked:

protected ConnectionSource getConnectionSource() throws SQLException {
    String databaseUrl = "jdbc:h2:tcp://localhost/~/test";
    return new JdbcConnectionSource(databaseUrl,"sa","sa");
}

To use H2 in server mode on wildfly, I Modifed connection-url in standalone.xml

<datasource jndi-name="java:jboss/datasources/ExampleDS" pool- name="ExampleDS" enabled="true" use-java-context="true">
     <connection-url>jdbc:h2:tcp://localhost/~/test</connection-url>
               …
</datasource>

Answer 4

Simple step: Go to the task manager and kill the java process

then start your apllication

Answer 5

I had the same problem. in Intellj, when i want to use h2 database when my program was running i got the same error. For solve this problem i changed the connection url from

spring.datasource.url=jdbc:h2:file:~/ipinbarbot

to:

spring.datasource.url=jdbc:h2:~/ipinbarbot;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

And then my problem gone away. now i can connect to "ipinbarbot" database when my program is. If you use Hibernate, also don't forget to have:

spring.jpa.hibernate.ddl-auto = update

goodluck

Answer 6

I got clue from Saman Salehi above. My usecase: Preparing REST application for client-side load balancing(running two JVM instances of REST). Here my MVC application will call this REST application that has ActiveMQ backend for DATA. I had the problem when I ran two instances of REST application in eclipse and trying to run both instances at the same time with the following configuration

spring.datasource.url=jdbc:h2:file:./Database;
spring.jpa.properties.hibernate.hbm2ddl.auto=update

After adding DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

spring.datasource.url=jdbc:h2:file:./Database;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

Both instances are running and showing in Eureka dasboard.

Don't close the database when the VM exits : jdbc:h2:;DB_CLOSE_ON_EXIT=FALSE

Multiple processes can access the same database without having to start the server manually ;AUTO_SERVER=TRUE

Further reading: http://www.h2database.com/html/features.html