5

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.

Community
  • 1
  • 1
Mickael
  • 4,458
  • 2
  • 28
  • 40
  • The connections are closed; you are setting up Hibernate to manually create connections, so it will be closed when that session.close() is done. The problem here may be that H2 (the database) itself keeps running since you boot it embedded. What do you mean by "redeployment" - does that mean the application is redeployed in a running tomcat instance, or do you stop and start Tomcat too? – Gimby Apr 28 '15 at 09:55
  • Thanks for your comment. I think you're right. I think H2 keeps running even when I close the session. And yes, when I say "redeployment", I meant redeploy the application on a running tomcat instance without restarting it. – Mickael Apr 30 '15 at 09:45

3 Answers3

7

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.

Mickael
  • 4,458
  • 2
  • 28
  • 40
  • 1
    If it works, it works. Quite possibly an alternative solution is to setup a servlet context listener and on context shutdown send a "SHUTDOWN" SQL command to the H2 database. – Gimby Apr 30 '15 at 12:08
0

H2 closes the Database when all connections are closed to it. Closing all connections from the connectionpool works for me.

Christian
  • 3,551
  • 1
  • 28
  • 24
-1

Closing the connection also worked for me, had multiple connections open without realizing.

Taranjit Kang
  • 2,510
  • 3
  • 20
  • 40