Javers SQL connections in use forever

Question

I have a problem with database connections not being 'released' by javers.

We are using Hibernate 5.0.6, Hikari as our connection pool and the MSSQL Server as our database. Javers is configured as follows (snippet):

JaversBuilder.javers().
    registerJaversRepository(SqlRepositoryBuilder.sqlRepository().
        withConnectionProvider(() -> ((SessionFactoryImpl) sessionFactory).getServiceRegistry().
            getService(org.hibernate.engine.jdbc.connections.spi.ConnectionProvider.class).getConnection()).
        withDialect(DialectName.MSSQL).
        build()).
    build();

Obtaining connections works fine this way. The connection pool opens database connections if no more are available. However, the connections obtained by javers are 'inUse' forever.

61366 [Hikari Housekeeping Timer (pool HikariPool-0)] DEBUG HikariPool  - Before cleanup pool stats HikariPool-0 (total=100, inUse=100, avail=0, waiting=1)
61366 [Hikari Housekeeping Timer (pool HikariPool-0)] DEBUG HikariPool  - After cleanup pool stats HikariPool-0 (total=100, inUse=100, avail=0, waiting=1)
61366 [HikariCP connection filler (pool HikariPool-0)] DEBUG HikariPool  - After fill pool stats HikariPool-0 (total=100, inUse=100, avail=0, waiting=1)

Do I have to manually close the connection? If I try this (just close every connection i gave to Javers after one second), the connection pool is cleared. However, this approach is not an option.

Is there something that i miss? If i have to manually close the connection, is it at least possible to receive a notice from Javers that it doesn't need the connection anymore?

Answer 1

You shouldn't close connections, in fact you shouldn't also open new connections for JaVers.

The main idea is that JaVers reuses connections and transactions opened and closed by Application.

Example scenario:

1. Application takes connection from a pool
2. Applications does some DML operations, e.g. inserts new Person#1
3. JaVers borrows connection from app and inserts audit records for Person#1
4. Application commits (or rollbacks) transaction and returns connection to the pool

If you are using JPA API, you can use JpaHibernateConnectionProvider bundled with JaVers:

package org.javers.spring.jpa;
... //

public class JpaHibernateConnectionProvider implements ConnectionProvider{

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Connection getConnection() {

        SessionImpl session =  (SessionImpl)entityManager.unwrap(Session.class);

        return session.connection();
    }
}

If you are using bare Hibernate, you can write similar code, which extracts connection from current Hibernate session (bounded to current thread).

Answer 2

Ok here is my solution. Collecting all connections obtained by Javers and closing them (close() does not close but releases them to the connection pool!) after Javers work is done:

A class to collect the connections:

public class ConnectionCleanup {

    private static final Logger LOG = LoggerFactory.getLogger(ConnectionCleanup.class);
    private static final ThreadLocal<List<Connection>> connections = new ThreadLocal<>();

    public static void add(final Connection connection) {
        List<Connection> listConnections = connections.get();
        if (listConnections == null) {
            listConnections = new ArrayList<>();
            connections.set(listConnections);
        }
        listConnections.add(connection);
    }

    public static void cleanup() {
        final List<Connection> listConnections = connections.get();
        if (listConnections == null) {
            return;
        }
        for (final Connection con : listConnections) {
            try {
                if (!con.isClosed()) {
                    con.close();
                }
            } catch (final Exception ex) {
                LOG.error("Caught an", ex);
            }
        }
        connections.set(null);
    }
}

Wrapping the SqlRepository to call cleanup after Javers work is done:

public class CleanupJaversSqlRepository implements JaversRepository {

    private final JaversSqlRepository repository;

    public CleanupJaversSqlRepository(final JaversSqlRepository repository) {
        this.repository = repository;
    }

    @Override
    public Optional<CdoSnapshot> getLatest(final GlobalId globalId) {
        final Optional<CdoSnapshot> latest = repository.getLatest(globalId);
        ConnectionCleanup.cleanup();
        return latest;
    }

    @Override
    public void persist(final Commit commit) {
        repository.persist(commit);
        ConnectionCleanup.cleanup();
    }

    @Override
    public CommitId getHeadId() {
        final CommitId commitId = repository.getHeadId();
        ConnectionCleanup.cleanup();
        return commitId;
    }

    @Override
    public void setJsonConverter(final JsonConverter jsonConverter) {
        repository.setJsonConverter(jsonConverter);
        ConnectionCleanup.cleanup();
    }

    @Override
    public void ensureSchema() {
        repository.ensureSchema();
        ConnectionCleanup.cleanup();
    }

    @Override
    public List<CdoSnapshot> getStateHistory(
            final GlobalId globalId,
            final QueryParams queryParams) {
        final List<CdoSnapshot> stateHistory = repository.getStateHistory(globalId, queryParams);
        ConnectionCleanup.cleanup();
        return stateHistory;
    }

    @Override
    public List<CdoSnapshot> getPropertyStateHistory(
            final GlobalId globalId,
            final String propertyName,
            final QueryParams queryParams) {
        final List<CdoSnapshot> stateHistory = repository.getPropertyStateHistory(
                globalId, propertyName, queryParams);
        ConnectionCleanup.cleanup();
        return stateHistory;
    }

    @Override
    public List<CdoSnapshot> getStateHistory(
            final ManagedType givenClass,
            final QueryParams queryParams) {
        final List<CdoSnapshot> stateHistory = repository.getStateHistory(givenClass, queryParams);
        ConnectionCleanup.cleanup();
        return stateHistory;
    }

    @Override
    public List<CdoSnapshot> getPropertyStateHistory(
            final ManagedType givenClass,
            final String propertyName,
            final QueryParams queryParams) {
        final List<CdoSnapshot> stateHistory = repository.getPropertyStateHistory(
                givenClass, propertyName, queryParams);
        ConnectionCleanup.cleanup();
        return stateHistory;
    }

    @Override
    public List<CdoSnapshot> getValueObjectStateHistory(
            final EntityType ownerEntity,
            final String path,
            final QueryParams queryParams) {
        final List<CdoSnapshot> stateHistory = repository.getValueObjectStateHistory(
                ownerEntity, path, queryParams);
        ConnectionCleanup.cleanup();
        return stateHistory;
    }
}

And finally, putting it all together:

JaversBuilder.javers().
                registerJaversRepository(new CleanupJaversSqlRepository(SqlRepositoryBuilder.sqlRepository().
                        withConnectionProvider(() -> {
                            final Connection connection = ((SessionFactoryImpl) sessionFactory).
                                    getServiceRegistry().getService(
                                            org.hibernate.engine.jdbc.connections.spi.ConnectionProvider.class).
                                    getConnection();
                            ConnectionCleanup.add(connection);
                            return connection;
                        }).
                        withDialect(DialectName.MSSQL).
                        build())).
                build();