Spring Boot集成HSQLDB时数据库锁获取失败问题排查与解决方案咨询

Hey there, let's work through this HSQLDB lock issue you're facing—let's break down the root causes and fixes step by step.

First: Clarify HSQLDB Connection Modes #

Your JDBC URL jdbc:hsqldb:hsql:/localhost:9001/db/test uses server mode, which absolutely requires a running HSQLDB server instance. If you don't start the server first, your Spring Boot app will try to fall back to creating an embedded database, but the URL format will confuse the driver, leading to that lock file error.

There are two main paths to fix this: switch to embedded mode (great for testing, no separate server needed) or set up the HSQLDB server properly.

Fix Option 1: Switch to Embedded Mode (Recommended for Testing) #

Embedded mode lets HSQLDB run inside your Spring Boot app, either storing data in files or in memory. No external server required.

Update Your application.yml #

Change the datasource URL to one of these options:

• File-based embedded database (persists data between app restarts):

  spring:
    datasource:
      url: jdbc:hsqldb:file:./db/test;shutdown=true;hsqldb.tx=mvcc
      driverClassName: org.hsqldb.jdbcDriver
      username: root
      password: 
    jpa:
      show-sql: false
      hibernate:
        ddl-auto: create-drop
        use-new-id-generator-mappings: false
  

  • ;shutdown=true tells HSQLDB to cleanly shut down when your app stops
  • ;hsqldb.tx=mvcc enables multi-version concurrency control, which allows multiple connections (critical since your Hikari pool has maximumPoolSize=2)

• In-memory embedded database (data is lost on app restart, perfect for fast tests):

  url: jdbc:hsqldb:mem:testdb;hsqldb.tx=mvcc
  

Simplify Your Database Configuration #

Your current DatabaseConfiguration is redundant—Spring Boot can auto-configure HikariCP for you. You can simplify it to:

@Configuration
@EntityScan("com.x.project")
@EnableJpaRepositories("com.x.project")
public class DatabaseConfiguration {
    // Spring Boot will auto-configure the DataSource using your application.yml settings
    // If you need custom Hikari config, add @ConfigurationProperties for hikari:
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariConfig hikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource(hikariConfig());
    }
}

Then add Hikari settings directly to application.yml:

spring:
  datasource:
    hikari:
      pool-name: my db pool
      maximum-pool-size: 2

Fix Option 2: Use Server Mode (For Multi-App/Process Access) #

If you need multiple applications or processes to access the same HSQLDB instance, you'll need to set up the HSQLDB server:

1. Download HSQLDB Server: Grab the official HSQLDB distribution (match your JDBC driver version).
2. Start the Server: Run this command in your terminal (adjust paths as needed):

   java -cp hsqldb.jar org.hsqldb.server.Server --database.0 file:./db/test --dbname.0 test
   

   This starts a server hosting a database named test stored in ./db/test.
3. Fix Your JDBC URL: You had a typo—server mode URLs need two slashes after hsql::

   url: jdbc:hsqldb:hsql://localhost:9001/test
   

4. Start Your Spring Boot App: Make sure the HSQLDB server is running before launching your app.

Why You Got the Lock Error #

The error happened because:

• You used a server-mode URL but didn't start the server
• The driver tried to interpret the URL as an embedded database path, but the format was invalid, so it couldn't create the necessary lock file to prevent concurrent access
• Even if you'd fixed the URL, embedded mode without hsqldb.tx=mvcc doesn't support multiple connections (your Hikari pool has 2), which would also cause lock conflicts

内容的提问来源于stack exchange，提问作者Aksoy