Transactions not committed when using multi-tenancy

Hi,

We’re having issues with transactions not being committed/rolled back correctly when using container managed transactions using a multi-tenancy implementation with WildFly and Hibernate.

We’ve implement multi-tenancy using the database per tenant strategy, as per the guide below. The number of tenants is dynamic, and so datasources are not predefined, but are created as required and cached using JNDI. Each datasource is backed by a C3P0 connection pool.

http://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#multitenacy

Our issue is that, depending upon configuration, transactions are either not being committed, or are being auto-committed and therefore not rolled back when an exception is thrown.

Examples of our implementation are below. This is not our exact implementation, I’ve simplified it for brevity, but hopefully contains enough detail to demonstrate our use case.

import com.mchange.v2.c3p0.DataSources;
import com.mchange.v2.c3p0.DriverManagerDataSource;
import org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Properties;

public class DatabasePerTenantConnectionProvider extends AbstractMultiTenantConnectionProvider {

    private static final String JNDI_CP_BASE = "java:global/connectionprovider/";
    private static final Logger LOG = LoggerFactory.getLogger(MultiTenantConnectionProvider.class);

    private DatabaseConnectionRepository controlDbLookup = new DatabaseConnectionRepoImpl();


    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        return selectConnectionProvider("default");
    }

    @Override
    protected ConnectionProvider selectConnectionProvider(String tenantIdentifier) {

        ConnectionProvider provider = jndiLookupConnectionProvider(tenantIdentifier);
        if (provider == null) {
            DataSource ds = null;
            try {
                ds = createDataSource(tenantIdentifier);
            } catch (SQLException e) {
                LOG.error("Error selecting a Connection Provider for Tenant {}", tenantIdentifier);
                return null;
            }
            provider = new SingleTenantConnectionProvider(ds, tenantIdentifier);
            jndiBindConnectionProvider(tenantIdentifier, provider);
        }
        return provider;
    }

    private ConnectionProvider jndiLookupConnectionProvider(final String tenantIdentifier) throws ClassCastException {
        String jndiName = JNDI_CP_BASE + tenantIdentifier;
        try {
            Context ctx = new InitialContext();
            return (ConnectionProvider) ctx.lookup(jndiName);
        } catch (NamingException e) {
            LOG.debug("Naming Exception: {}", e.getMessage());
            return null;
        }
    }

    private void jndiBindConnectionProvider(final String dataSourceName, final ConnectionProvider provider) {
        String jndiName = JNDI_CP_BASE + dataSourceName;
        LOG.debug("Bind Connection Provider {}", jndiName);
        try {
            Context ctx = new InitialContext();
            ctx.rebind(jndiName, provider);
            LOG.info("Bound Connection Provider {}", jndiName);
        } catch (Exception e) {
            LOG.warn("Unable to store {} in JNDI: {}", jndiName, e.getMessage());
        }
    }

    private DataSource createDataSource(String tenantIdentifier) throws SQLException {
        DatabaseConnectionDetails dbConnDets = getDBConnectionDetails(tenantIdentifier);
        Properties jdbcProps = new Properties();
        jdbcProps.setProperty("user", dbConnDets.getUsername());
        jdbcProps.setProperty("password", dbConnDets.getPassword().getPassword().toString());

        DataSource unpooledDS = DataSources.unpooledDataSource(dbConnDets.getConnectionString(), jdbcProps);
        ((DriverManagerDataSource) unpooledDS).setDriverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        Properties poolProps = new Properties();
        [...]
        C3P0 configuration omitted, idle time, pool size, etc.
        [...]

        return DataSources.pooledDataSource(unpooledDS, poolProps);
    }

    /**
     * Obtains the tenant db connection url, user name, password, etc.
     */
    private DatabaseConnectionDetails getDBConnectionDetails(String tenantIdentifier) {
        return controlDbLookup.getConnectionDetailsForTenantEnvironment(tenantIdentifier);
    }
}
import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SingleTenantConnectionProvider implements ConnectionProvider {

    private static final Logger LOG = LoggerFactory.getLogger(SingleTenantConnectionProvider.class);

    private final DataSource dataSource;
    private final String tenantCode;

    public SingleTenantConnectionProvider(final DataSource wrappedDataSource, final String tenantCode) {
        this.dataSource = wrappedDataSource;
        this.tenantCode = tenantCode;
    }

    @Override
    public boolean isUnwrappableAs(@SuppressWarnings("rawtypes") final Class unwrapType) {
        // This class doesn't implement any of the optional unwrap operations.
        return false;
    }

    @Override
    public <T> T unwrap(final Class<T> unwrapType) {
        // This class doesn't implement any of the optional unwrap operations.
        return null;
    }

    @Override
    public Connection getConnection() throws SQLException {
        LOG.debug("Retrieve Connection for tenant " + tenantCode);
        Connection conn = dataSource.getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    @Override
    public void closeConnection(final Connection conn) throws SQLException {
        if (conn != null) {
            LOG.debug("Closing Connection for tenant " + tenantCode);
            conn.close();
        }
    }

    @Override
    public boolean supportsAggressiveRelease() {
        return true;
    }
}
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class HibernateExistingSessionTenantResolver implements CurrentTenantIdentifierResolver {

    private static final Logger LOG = LoggerFactory.getLogger(HibernateExistingSessionTenantResolver.class);

    @Override
    public String resolveCurrentTenantIdentifier() {
        String currentUserTenant = ThreadContext.getTenantEnvironmentCode();
        if (currentUserTenant == null) {
            LOG.warn("Current User Tenant is null - default to {}", MultiTenantConnectionProvider.DEFAULT_TENANT_CODE);
            currentUserTenant = MultiTenantConnectionProvider.DEFAULT_TENANT_CODE;
        }
        return currentUserTenant;
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        // Always perform the additional checks.
        return true;
    }
}
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1"
   xmlns="http://xmlns.jcp.org/xml/ns/persistence"
   xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
   <persistence-unit name=“core" transaction-type="JTA">
      <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
      
      <!-- Entities omitted —>

      <shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode>
      
      <properties>
         <property name="hibernate.multi_tenant_connection_provider"
            value="com.midlandhr.trent.core.multitenancy.MultiTenantConnectionProvider"/>
         <property name="hibernate.tenant_identifier_resolver"
            value="com.midlandhr.trent.core.multitenancy.HibernateExistingSessionTenantResolver"/>
         <property name="hibernate.multiTenancy" value="DATABASE"/>
         <property name="hibernate.connection.autocommit" value="false"/>
      </properties>
   </persistence-unit>
</persistence>

Originally we did not set auto-commit but found that auto-commit was enabled and therefore every statement was being committed even when an exception occurred which meant the transaction should be rolled back.

After setting autoCommit to false, we found that no transaction was ever committed and therefore we could not write anything to the database. As a workaround we implemented a TransactionInterceptor to explicitly commit at the end of a successful transaction.

public abstract class TransactionInterceptor extends EmptyInterceptor {

    @Override
    public final void beforeTransactionCompletion(final Transaction hibernateTx) {
        LOG.debug("beforeTransactionCompletion");
        Session session = getSession();
        Transaction sessionTx = session.getTransaction();
        if (sessionTx.getStatus() == TransactionStatus.ACTIVE) {

            // Flush any outstanding changes.
            session.flush();

            // Commit the JDBC Connection
            session.doWork(Connection::commit);
        } else {
            // This case should not force a rollback - the Hibernate transaction has already been dealt with.
            LOG.warn("Transaction is not active - do not commit");
        }
    }
}

With the following properties in the persistence.xml:

          <property name="hibernate.current_session_context_class" value="thread"/>
         <property name="hibernate.connection.autocommit" value="false"/>
        <property name="hibernate.connection.release_mode" value="after_transaction"/>-->
         <property name="hibernate.session_factory.session_scoped_interceptor" value=“ConcreteTransactionInterceptor"/>

This was working up to the point we upgrade from WildFly 12 to 16 with a corresponding upgrade of Hibernate from 5.1.10 to 5.3.9. Now we receive the following exception:

2019-05-10 10:26:47,545 ERROR [org.hibernate.internal.SessionImpl] (default task-2) f7daee13-42db-4c82-b895-0d1ab2b16913 HHH000088: Exception in interceptor beforeTransactionCompletion(): java.lang.IllegalStateException: A JTA EntityManager cannot use getTransaction()

Any ideas on how we can resolve this? Ideally, I’d like to remove the transaction interceptor workaround.

Regards,

Mark

For now, the following workaround to the workaround seems to be working:

public abstract class TransactionInterceptor extends EmptyInterceptor {   
  
   @Override   
   public final void beforeTransactionCompletion(final Transaction hibernateTx) {   
       LOG.debug("beforeTransactionCompletion");   
       EntityManager em = getEntityManager();   
       if (em.isJoinedToTransaction) {   
           // Flush any outstanding changes.   
           session.flush();   
           // Commit the JDBC Connection   
           session.doWork(Connection::commit);   
       } else {   
           // This case should not force a rollback - the Hibernate transaction has already been dealt with.   
           LOG.warn("Transaction is not active - do not commit");   
       }   
   }   
}   

Not entirely happy with it, would prefer to get rid of this altogether.

Regards,

Mark

1 Like