Analysis and Solutions for H2 Database "Locked by Another Process" Error

Problem Background and Error Analysis

During Java application development using H2 database, developers frequently encounter the following error message:

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]

This error typically occurs when attempting to connect to an H2 database via JDBC, indicating that the database file is already locked by another process or connection. H2 database uses file locking mechanism by default to ensure data consistency, and this error is triggered when multiple processes attempt to access the same database file simultaneously.

Solution 1: Using TCP Connection Mode

The most recommended solution is to use H2's TCP server mode. In this mode, the database runs as an independent server process, allowing multiple clients to connect via TCP/IP protocol, thus avoiding file lock conflicts.

First, you need to start the H2 TCP server. This can be achieved through the following Java code:

import org.h2.tools.Server;

public class H2ServerStarter {
    public static void main(String[] args) throws Exception {
        Server server = Server.createTcpServer(
            "-tcp", "-tcpAllowOthers", "-tcpPort", "9092"
        ).start();
        System.out.println("H2 TCP Server started successfully");
    }
}

Then use TCP connection string in your application:

String url = "jdbc:h2:tcp://localhost:9092/~/dbname";
Connection con = DriverManager.getConnection(url, "username", "password");

Or in Spring Boot configuration:

spring.datasource.url=jdbc:h2:tcp://localhost:9092/~/dbname

Solution 2: Configuring AUTO_SERVER Parameter

For scenarios that don't require an independent server process, you can add the AUTO_SERVER=TRUE parameter to the connection string. This parameter allows H2 database to automatically start an embedded TCP server, enabling multiple concurrent connections.

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

In Spring Boot configuration:

spring.datasource.url=jdbc:h2:~/dbname;AUTO_SERVER=TRUE

It's also recommended to add DB_CLOSE_ON_EXIT=FALSE parameter to prevent automatic database connection closure when the program exits:

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

Solution 3: Manually Terminating Locking Processes

If the database is already locked and cannot be closed through normal means, you can manually terminate the process holding the lock.

In Linux systems, first find the relevant Java processes:

ps aux | grep java | grep h2

The output might show something like:

user 12345  2.5  0.8 5123456 123456 pts/0  Sl   10:30   0:15 java -jar h2-2.1.214.jar

Then terminate the process using its PID:

kill -9 12345

Finally, remove the lock file:

rm -f ~/dbname.lock.db

In Windows systems, you can find and terminate the javaw.exe process through Task Manager.

Technical Principle Deep Analysis

H2 database's locking mechanism is implemented based on file system locks. When connecting to the database in embedded mode (non-TCP mode), H2 creates a .lock.db file in the database file directory. This lock file ensures that only one process can open the database file in write mode at any given time, thereby guaranteeing data consistency.

The TCP server mode works by abstracting database access into a client-server architecture. The server process holds exclusive lock on the database file, while clients communicate with the server via TCP protocol, avoiding direct file lock competition. This architecture not only solves concurrent access issues but also provides better network accessibility and security.

The AUTO_SERVER=TRUE parameter implements a hybrid mode. The first connection starts in embedded mode and creates the lock file, but simultaneously starts a TCP server. Subsequent connections automatically connect to this server via TCP protocol rather than directly accessing the database file. This design provides limited concurrent access capability while maintaining simplicity.

Best Practice Recommendations

1. Development Environment: Use AUTO_SERVER=TRUE parameter to facilitate simultaneous database access by IDE and applications.

2. Production Environment: Recommended to use independent TCP server mode for better concurrent performance and stability.

3. Testing Environment: Ensure proper database connection closure before and after tests to avoid lock file residue.

4. Connection Management: Use connection pooling technology to ensure proper connection release.

5. Monitoring and Logging: Enable H2's logging features to monitor database connection status and lock usage.

Common Issue Troubleshooting

If none of the above solutions work, consider the following troubleshooting steps:

1. Check database file permissions to ensure the application has read/write access.

2. Confirm no other applications (such as H2 Console, other Java processes) are using the database.

3. Check firewall settings to ensure TCP ports are accessible.

4. Verify H2 database version, as some versions may have known lock-related issues.

5. Consider using in-memory database mode for testing to eliminate file system issues.