Why could database files of h2 be still locked for some time after hibernate closes connection?-CodePudding

While writing JUnit tests for my application, I encountered a problem, which seems like when I close EntityManager and EntityManagerFactory, it takes some time to finish, and I am not able to copy the database (H2) folder right away.

Let me explain in more detail:

This is content in my single JUnit test (I am just calling this static method on DatabaseManager):

DatabaseManager.createBackupNoAlert(new File(DatabaseManager.getConnectedDatabasePath()), new File(TESTDBBACKUPPATH));

Here both Files are not null, and database connection was successfully created using EntityManager and EntityManagerFactory.

createBackupNoAlert method:

public static void createBackupNoAlert(final File actualDb, final File destination)
{
    try
    {
        if (!destination.exists())
        {
            destination.mkdir();
        }
        DatabaseManager.disconnect();
        FileUtils.copyDirectoryToDirectory(actualDb, destination);
        String now = LocalDateTime.now().toString();
        now = now.substring(0, now.indexOf('.')).replaceAll(":", "_");
        File newFile = new File(destination.getAbsolutePath()   "\\"   now);
        boolean renamedToCurrentDateTime = new File(destination, actualDb.getName()).renameTo(newFile);
        if (!renamedToCurrentDateTime)
        {
            logger.warn("Could not rename backup directory to current datetime");
        }
        DatabaseManager.renewConnection();
    }
    catch (IOException e)
    {
        logger.error(ExceptionUtils.getStackTrace(e));
    }
}

For copying database folder, I am using FileUtils.copyDirectoryToDirectory() method from commons-io https://mvnrepository.com/artifact/commons-io/commons-io/2.11.0.

disconnect method:

public static void disconnect()
{
    if (entityManager != null && entityManager.isOpen())
    {
        entityManager.close();
    }
    if (entityManagerFactory != null && entityManagerFactory.isOpen())
    {
        entityManagerFactory.close();
    }
    logger.info("Disconnected from db");
}

Both .close() methods are called.

Exception occurs at FileUtils.copyDirectoryToDirectory(actualDb, destination); when this test is run:

java.nio.file.FileSystemException: testdb\testdb.mv.db -> backuptestdb\testdb\testdb.mv.db: The process cannot access the file because another process has locked a portion of the file

There are no more connections to the database after disconnect() finishes.

Proof of above statement and also what I tried and found out:

When I insert a breakpoint at FileUtils.copyDirectory... and run that test (DatabaseManager.createBackupNoAlert()) in Debug mode, wait a bit, continue, directory is copied, everything is fine and test finishes OK. What I am thinking about is that Hibernate runs EntityManager.close() and EntityManagerFactory.close() on another thread, which does not finish before I call that copy operation, but it is not, after I looked at implementation.

Also, when I insert Thread.sleep(100) between DatabaseManager.disconnect() and FileUtils.copyDirectoryToDirectory(), test finishes OK!

Did someone else encounter this? Is there any fix that would allow me to copy database folder right after connection to database is closed?

Hibernate properties I used in connection:

hibernateProps.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
hibernateProps.put("hibernate.connection.pool_size", "1");
hibernateProps.put("hibernate.connection.provider_class", "org.hibernate.connection.C3P0ConnectionProvider");
hibernateProps.put("hibernate.c3p0.min_size", "1");
hibernateProps.put("hibernate.c3p0.max_size", "1");
hibernateProps.put("hibernate.c3p0.acquireRetryAttempts", "3");
hibernateProps.put("hibernate.c3p0.acquireRetryDelay", "400");
hibernateProps.put("hibernate.connection.isolation", String.valueOf(Connection.TRANSACTION_SERIALIZABLE));
hibernateProps.put("javax.persistence.schema-generation.database.action", "create");

CodePudding user response：

This issue was not caused by h2 database file locking https://www.h2database.com/html/features.html#database_file_locking . Tried to use FILE_LOCK=SOCKET and also FILE_LOCK=NO, but it did not help. Looks like using Hikari connection provider instead of C3P0 fixed my issue. To use it instead i had to define it in hibernate properties:

hibernateProps.put("hibernate.connection.provider_class","org.hibernate.hikaricp.internal.HikariCPConnectionProvider");

and include dependency in pom.xml (Maven project):

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-hikaricp</artifactId>
    <version>5.6.1.Final</version>
</dependency>

https://github.com/brettwooldridge/HikariCP

https://github.com/brettwooldridge/HikariCP/wiki/Hibernate4

Note: "As of Hibernate 4.3.6 there is an official ConnectionProvider class from Hibernate, which should be used instead of the HikariCP implementation. The class is called org.hibernate.hikaricp.internal.HikariCPConnectionProvider"