Mickaël B Mickaël B - 5 months ago 82
Java Question

Tomcat WebApp with embedded H2 : Database may be already in use: "Locked by another process"

I'm working on a Maven Spring Boot web application using Hibernate on embedded H2 database.
The application is deployed on Tomcat 8 application container using Maven goal tomcat7:redeploy from Maven Tomcat plugin (tomcat7-maven-plugin).

When I try to deploy this web application on Tomcat for the first time, I have no exception (after Tomcat restart).

But when I try to redeploy this web application on Tomcat, I have the following exception :


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; SQL statement:
null/14cfb969fb93251ff134953c65dd1f05db2ecd34c6b [90020-145]


hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<!-- Database connection settings -->
<property name="connection.driver_class">org.h2.Driver</property>
<property name="connection.url">jdbc:h2:file:d:/Profiles/mBaye/Developement/Run/spring-boot-web-seed-dev/db/springbootwebui;DB_CLOSE_DELAY=0;MVCC=TRUE</property>
<property name="connection.username">sa</property>
<property name="connection.password"/>

<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>

<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.H2Dialect</property>

<!-- Disable the second-level cache -->
<property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>

<!-- Drop and re-create the database schema on startup -->
<!-- <property name="hbm2ddl.auto">create</property> -->
<!-- Update the database schema on startup -->
<property name="hbm2ddl.auto">update</property>

<mapping resource="app/Greeting.hbm.xml"/>

</session-factory>

</hibernate-configuration>


GreetingController.java

@Controller
public class GreetingController {

private static Logger logger ;

// A SessionFactory is set up once for an application
private static final SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();

[...]

private Greeting saveGreeting(Greeting greeting) {

logger.info(new StringBuilder("greeting=").append(greeting.toString()).toString());

Session session = null;
Greeting ret = null;

try {
session = sessionFactory.openSession();
session.beginTransaction();
session.save( greeting );
session.getTransaction().commit();
// Return result
ret = greeting ;
} catch (Exception e) {
logger.log(Level.SEVERE, new StringBuilder("Failed to save ").append(greeting.toString()).toString(), e);
} finally {
session.close();
}

if (ret != null) {
logger.info(new StringBuilder("ret=").append(ret.toString()).toString());
} else {
logger.info(new StringBuilder("ret=null").toString());
}

return ret ;
}

[...]
}


I read on other subjects that the database connections are closed automatically when the VM exits correctly
(source: What is the proper way to close H2?)

I supposed, as the application is deployed on Tomcat, the database connections are hold by Tomcat.

I'd like to find a proper way to close all the database connections on Tomcat redeployment.

Thanks in advance.

Answer

I finally found a solution ! :)

I changed the settings of the H2 database connection URL to :

<property name="connection.url">jdbc:h2:file:d:/Profiles/mBaye/Developement/Run/spring-boot-web-seed-dev/db/springbootwebui;MVCC=TRUE;DB_CLOSE_ON_EXIT=TRUE;FILE_LOCK=NO</property>

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:file:d:/Profiles/mBaye/Developement/Run/spring-boot-web-seed-dev/db/springbootwebui;MVCC=TRUE;DB_CLOSE_ON_EXIT=TRUE;FILE_LOCK=NO</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"/>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.H2Dialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <!-- <property name="hbm2ddl.auto">create</property> -->
        <!-- Update the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>

        <mapping resource="app/Greeting.hbm.xml"/>

    </session-factory>

</hibernate-configuration>

I'm not sure this is the best solution but it works.