H2 database error: Database may be already in use: "Locked by another process"

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?

Cartwheel

Answer 1

H2 is still running (I can guarantee it). You need to use a TCP connection for multiple users such as ->

<property name="javax.persistence.jdbc.url" value="jdbc:h2:tcp://localhost/C:\Database\Data\production;"/>

OR

DriverManager.getConnection("jdbc:h2:tcp://localhost/server~/dbname","username","password");

It also means you need to start the server in TCP mode. Honesetly, it is pretty straight forward in the documentation.

Force kill the process (javaw.exe for Windows), and make sure that any application that might have started it is shut down. You have an active lock.

Gish

A side note--unless it is a really insular application I really recommend using the TCP/IP mode. It get's around this exact problem when a user accidentally opens two instances. – Gish

Thank you for your answer. I was trying to use the embeded mode because according to the documentation it is faster. – Cartwheel

@Cartwheel it is much faster. You just end up with the situation you ran into easily. You might consider a framework in which you start the database and stop it gracefully from within the application thus avoiding the situation you find yourself in. Good luck, it is a great database for small data-sets. – Gish

With the current H2, the DriverManager example above produces the exception A file path that is implicitly relative to the current working directory is not allowed in the database URL. Instead if you replace server~ with ~, it seems to work fine. – Cormack

Answer 2

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

Ducat

Thanks, Saman, it helped me... I have written my use case also here. – Appenzell

AUTO_SERVER=TRUE is not anymore supported. – Pole

AUTO_SERVER=TRUE worked for me. Thanks! – Eunaeunice

Answer 3

I'm using h2db with a test T24 tafj application, I had the same problem but I managed to resolve it by identifying the application that is running h2 (launched when I attempted to setup a database connection).

ps aux|grep java

will give output as:

sysadmin 22755  3.2  0.1 5189724 64008 pts/3   Sl   08:28   0:00 /usr/java/default/bin/java -server -Xmx2048M -XX:MaxPermSize=256M -cp h2-1.3.175.jar:/r14tafj/TAFJ/dbscripts/h2/TAFJFunctions.jar org.h2.tools.Server -tcp -tcpAllowOthers -baseDir /r14tafj/t24/data

now kill this with its process id:

kill -9 22755

and at last remove the lock file:

rm -f dbname.lock.db

Banta

Worked perfectly. But I didn't have to remove the locked file. Just killing the process worked. – Tutorial

Answer 4

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

Appenzell

Using these flags in my url jdbc:h2:file:~/trash/mydb;mode=PostgreSQL;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE I could share the same file based instance between two Java applications and even with a web browser console. – Hobnob

Answer 5

You can also visit the "Preferences" tab from the H2 Console and shutdown all active sessions by pressing the shutdown button.

Secant

This is correct. The simple answer is to log out of the database and shutdown the server. See Disconnecting and Stopping the Application in the H2 Tutorial. – Marauding

Answer 6

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

then start your apllication

Boman

quickest fix.Thanks. – Trickster

Answer 7

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.

Preparative

Answer 8

If you are running same app into multiple ports where app uses single database (h2), then add AUTO_SERVER=TRUE in the url as follows:

jdbc:h2:file:C:/simple-commerce/price;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE;AUTO_SERVER=TRUE

Topside

Answer 9

According H2 Database Tutorial you can run the H2 Database in three different modes:

1. Server mode:

jdbc:h2:tcp://localhost/~/test

When using H2 db in server mode (also known as client/server mode) all data is transferred over TCP/IP. Before application can use H2 Database in server mode, you need to start the H2 DB within the same or another machine.

2. Embedded mode:

jdbc:h2:~/test

H2 db in embedded mode will be faster but the downside of it is that no other process can access the Database. In the above connection string, the Data will be saved into the ‘test’ folder, under the user’s home directory.

3. Mixed mode:

The mixed mode combines some features of the embedded and the server mode. The first application connecting to the H2 db does that in embedded mode, but at the same time it starts a server so that other applications can concurrently access the same data, even from different processes.

jdbc:h2:/data/test;AUTO_SERVER=TRUE

When using automatic mixed mode, you can share the JDBC URL for all applications using the DB. By default the server uses any free TCP port. The port can be set manually using AUTO_SERVER_PORT=9090.

Halfpenny

for me the mixed_mode solved the problem – Ecosphere

Mixed mode is what I wanted. Useful to notice that the JDBC URL must be the same in the second client settings. You can not set it with jdbc:h2:tcp... syntax. Second client will fallback in client/server mode automatically – Crt

Answer 10

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>

Lamed

Answer 11

Identify the H2 process id and kill it. For mac

ps -ef|grep h2

Then get the process id and kill it.

kill -9 PID

Ointment

Answer 12

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!

Jilljillana

Answer 13

I was facing this issue in eclipse . What I did was, killed the running java process from the task manager.

It worked for me.

Swordfish

Answer 14

In your application.properties file > edit the datasource into:

spring.datasource.url=jdbc:h2:file:C:/temp/test;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

Happy coding!

Lobelia

Answer 15

answer for this question => 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]

close all tab from your browser where open h2 database also Exit h2 engine from your pc

Clement

Answer 16

For InteliJ: right lick on your database in the database view and choose "Disconnect".

Flunkey

Answer 17

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?

Just add FILE_LOCK=NO;. FILE_LOCK=NO doesn't make dbname.lock.db.

spring.datasource.url=jdbc:h2:file:./testdb/h2;DB_CLOSE_ON_EXIT=false;FILE_LOCK=NO;

The detail for FILE_LOCK reference this.

Using the method NO forces the database to not create a lock file at all

Hornbill

extremely unsafe to use FILE_LOCK=NO. High chances of database getting corrupted – Humankind

you usually do not use H2 DB for production system, for development purposes this is a quick win - development scenario e.g. you persist data from your app and wanna look at them from DBeaver or even delete them without shutting down a server (restart may be costly) – Teeming

Answer 18

All these solutions didn't work for me and kept giving the same error.

This is how I solved it:

After you are done creating, deleting, or updating the data, you will need to stop the spring-boot application and then access the database from the H2. That is what worked for me.

Daves