h2 leaving lock file even though only create one connection and immediately disconnect

Question

My application is using an in memory h2 database with Hibernate, I was noticing that even after closing the application the h2 lock file remained. This occurred even if I started the application and then immediately without doing anything much closed the application.

Further investigation revealed that when I first start the application I make a test connection to check the database is okay as follows (exiting application if I can not connect)

try
{
  Session session = HibernateUtil.getSession();
  Transaction t = session.getTransaction();
  t.setTimeout(10);
  session.beginTransaction();
  t.commit();
  HibernateUtil.closeSession(session);
}
catch (Exception ex)
{
  MainWindow.logger.log(Level.SEVERE, "Problem accessing database needs recreating:" + ex.getMessage(), ex);
  System.exit(0);
}

removing this code solves the problem with the lock file, it now disappears. But I don't understand why it doesn't disappear anyway as I am closing the session. And the problem doesn't go away because if I do any real work in the application then once again I'm left with the problem of the lock file being left behind.

HibernateUtil methods are

public static Session getSession()
    {
        if (factory == null)
        {
            Configuration config =
                    HibernateUtil.getInitializedConfiguration();
            factory = config.buildSessionFactory();
        }
        Session hibernateSession =
                factory.openSession();
        return hibernateSession;
    }

 public static void closeSession(Session session)
    {
        if(session!=null)
        {
            session.close();
        }
    }

 public static Configuration getInitializedConfiguration()
    {
        Configuration config = new Configuration();

        config.setProperty(Environment.DRIVER,"org.h2.Driver");
        config.setProperty(Environment.URL,"jdbc:h2:"+Db.DBFOLDER+"/"+Db.DBNAME+";FILE_LOCK=SOCKET;MVCC=TRUE;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=50000");
        config.setProperty(Environment.DIALECT,"org.hibernate.dialect.H2Dialect");
        org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider");
        config.setProperty("hibernate.connection.username","jaikoz");
        config.setProperty("hibernate.connection.password","jaikoz");
        config.setProperty("hibernate.c3p0.numHelperThreads","10");
        config.setProperty("hibernate.c3p0.min_size","20");
        config.setProperty("hibernate.c3p0.max_size","100");
        config.setProperty("hibernate.c3p0.timeout","300");
        config.setProperty("hibernate.c3p0.maxStatementsPerConnection","50");
        config.setProperty("hibernate.c3p0.idle_test_period","3000");
        config.setProperty("hibernate.c3p0.acquireRetryAttempts","10");
        config.setProperty("hibernate.show_sql","false");
        addEntitiesToConfig(config);
        return config;
    }

My url connection is of the form

jdbc:h2:Database/Database.h2.db;FILE_LOCK=SOCKET;MVCC=TRUE;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=50000

I tried changing it to

jdbc:h2:Database/Database.h2.db;FILE_LOCK=SOCKET;MVCC=TRUE;DB_CLOSE_ON_EXIT=TRUE;CACHE_SIZE=50000

but that had no effect

The reason why this is a real problem for me is that I have a recreateDatabase() command that the users can use, and this physicially deletes the database files before they are recreated, but of course they cannot be deleted if the files are still being used, as they are as evidenced by the existence of the lock file. So the recreateDatabase() command doesnt work

score 3 · Accepted Answer · answered Oct 07 '13 at 15:36

You're using a connection pool. Closing a connection won't actually close it, it will just return the connection to the pool.

This means the first step would be to read the documentation for C3P0ConnectionProvider to find out how it talks to c3p0. You need to get access to the ComboPooledDataSource so you can call reset(). Setting min_size to 0 might also help.

That said, this is very dangerous unless you have full control over all the threads. If you don't, then some other thread might want to access the database just as you kill all connections. So if this is a web application, you need to install a filter which denies all requests as long as you reset the database.

Also note that you're not using an in-memory database; you're using an embedded file-based database. To create an in-memory database, use jdbc:h2:mem:.

Thanks, I think Ive worked it out I need to close the hibernate session factory using factory.close(), and yes Im using an embedded database I meant to say that. — Paul Taylor, Oct 07 '13 at 16:21

score 1 · Answer 2 · answered Nov 08 '13 at 21:24

1

Hi guys this errors from yor local internet if you add your database port number ending of connectionurl it will become ok for example jdbc:mysql://127.0.0.1:3307/hibernateExamples

answered Nov 08 '13 at 21:24

Vahap Gencdal

1,900
18
17

h2 leaving lock file even though only create one connection and immediately disconnect

2 Answers2