title: Hibernate Multi-tenancy + Spring Data JPA Executing Update Query | javax.persistence.TransactionRequiredException: Executing an update/delete query
body: <p><strong><em>NOTE:</strong> Given the nature and complexity of the problem, I striving to strike a balance between describing the problem and providing the context in as much detail as possible, while remain succinct. Please indicate where ever any additional information or clarifications are required.</em></p>
  
  <h2>Problem Details</h2>
  
  <p>Currently, I have a simple SQL UPDATE (shown below) which throws TransactionRequiredException. It must be noted that all the finder queries execute successfully with appropriate resultset being returned to the service layer, whereas the update query fails with error.</p>
  
  <p><code>javax.persistence.TransactionRequiredException: Executing an update/delete query</code></p>
  
  <p>I have a simple SQL Query defined in the repository class as follows which is the one that fails:</p>
  
  <pre><code>public interface SlaQualifyingItemsRepository extends JpaRepository&lt;SlaQualifyingItemsEntity, RowId&gt;
  {
      ... ... ...
      ... ... ...
      @Modifying
      @Transactional(propagation=Propagation.REQUIRED, rollbackFor=RuntimeException.class)
      @Query("UPDATE SlaQualifyingItemsEntity sqie SET sqie.affectedCiId = :affectedCiId WHERE sqie.id.slice=:slice AND sqie.id.rowId IN (:rowIds)")
      public void updateInBulkAffectedService(Integer slice, List&lt;Long&gt; rowIds, Long affectedCiId);
      ... ... ...
      ... ... ...
  }
  </code></pre>
  
  <h2>Project Configuration:</h2>
  
  <p>The following are only the key component versions configured in the project's pom.xml.</p>
  
  <ul>
  <li>Spring Framework (4.3.7.RELEASE) </li>
  <li>Spring Data JPA (1.8.2.RELEASE)</li>
  <li>Hibernate (5.2.9.Final)</li>
  <li>MS SQL Server 2014</li>
  <li>jTDS SQL Server Driver (1.3.1)</li>
  <li>Tomcat JDBC Pool (9.0.0.M26)</li>
  </ul>
  
  <h2>Additional Information:</h2>
  
  <p>The following are some other pertinent information related to the project:</p>
  
  <ul>
  <li>We are using Hibernate's support for multi-tenancy implementation
  using DB per tenant approach i.e. Each tenant has its own physically
  isolated DB for its data</li>
  <li>Since this is a multi-module / multiple
  sub-application based project the multi-tenancy enablement related
  infrastructure classes are implemented as a separate library jar file
  used as a dependency in the main project to support re-use and ease maintenance. </li>
  <li>The initialization and
  bootstrapping of multi-tenancy infrastructure occurs in the actual
  consumer application</li>
  <li>The datasources (tenant DB) information i.e. db name, connection parameters, pool configurations, etc. is maintained in a separate database which we call as the platform database. The hibernate multi-tenancy implementation fetches information from the platform database to setup and cache the datasources and connection information during the application bootstrap.</li>
  <li>During CRUD operations, the application gets the tenant identifier to connect to the correct datasource against which the DAO operations must be carried out. Therefore, it must be noted that auto-detection of data source is turned off (during application bootstrap) when initializing the transaction manager using </li>
  </ul>
  
  <blockquote>
    <p>txManager.setAutodetectDataSource(false);</p>
  </blockquote>
  
  <h2>Troubleshooting &amp; Investigation:</h2>
  
  <p>Various different things that I have tried to troubleshoot this issue, however I consistently experience the same issue.</p>
  
  <ol>
  <li>Tried playing around with propagation attribute on the
  @Transactional; tried setting it to REQUIRED/REQUIRED_NEW </li>
  <li>Tried moving around the @Transactional annotation - annotating only the    Service class method, annotating service and repository class methods (with proper transaction propagation settings), annotating only the repository class method with no change in the results </li>
  <li>Tried using older versions of Spring Framework, Spring Data JPA, and
  Hibernate to no avail</li>
  <li>Based on my debugging, I have found that just before
  trying to execute the query Hibernate attempts to check whether a
  transaction is already in progress (in the code below). The specific
  point from where Spring JPA class delegates query execution to
  Hibernate is shown in the code below:</li>
  </ol>
  
  <h2>org.springframework.data.jpa.repository.query.JpaQueryExecution</h2>
  
  <pre><code>    @Override
      protected Object doExecute(AbstractJpaQuery query, Object[] values) {
  
          int result = query.createQuery(values).executeUpdate();
          ... ... ...
          ... ... ...
  </code></pre>
  
  <ol start="4">
  <li>Attempted to further debug the spring/hibernate layer, and found that it fails at the below point in org.hibernate.query.internal.AbstractProducedQuery. Most probably the issue might be happening because during evaluation of the transaction status because it finds that the Transaction Status is set as NOT_ACTIVE which means that the transaction has not yet been begun.</li>
  </ol>
  
  <h1>org.hibernate.query.internal.AbstractProducedQuery</h1>
  
  <pre><code>public int executeUpdate() throws HibernateException {
          if ( ! getProducer().isTransactionInProgress() ) {
              throw getProducer().getExceptionConverter().convert(
                      new TransactionRequiredException(
                              "Executing an update/delete query"
                      )
              );
          }
  </code></pre>
  
  <hr>
  
  <h2>Key Implementation Classes (including MTA Enablement Classes)</h2>
  
  <h1>DatabaseConfig.java</h1>
  
  <pre><code>public abstract class DatabaseConfig
  {
     private static final Logger logger = LogManager.getLogger(DatabaseConfig.class);
  
     @Autowired
     private Environment environment;
  
     @Autowired
     private MapDataSourceLookup dataSourceLookup;
  
     @Bean(name="platformEntityManagerFactory")
     @Qualifier("platformEntityManagerFactory")
     public EntityManagerFactory platformEntityManagerFactory()
     {
        logger.trace("Initializing CSM platform entity manager configurations.");
  
        // Exposes JpaVendorAdapter implementation for Hibernate's Persistence Provider and EntityManager interface
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setDatabasePlatform(environment.getProperty(org.hibernate.cfg.Environment.DIALECT));
        jpaVendorAdapter.setShowSql(Boolean.getBoolean(environment.getProperty(org.hibernate.cfg.Environment.SHOW_SQL)));
  
        // Set up a shared JPA EntityManagerFactory in a Spring application context; the EntityManagerFactory can then be passed to
        // JPA-based DAOs via dependency injection
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
  
        // Trigger Spring to scan for classes annotated with @Entity and @MappedSuperclass and automatically add those to the JPA
        // PersistenceUnit
        factory.setPackagesToScan("com.sa.csm.dao.mta.model");
  
        // Specify the JpaVendorAdapter implementation for the desired JPA provider
        factory.setJpaVendorAdapter(jpaVendorAdapter);
  
        // Sets up an embedded data source using Spring’s embedded database support.
        factory.setDataSource(platformDataSource());
        factory.setJpaProperties(jpaProperties());
  
        // Perform initialization after all the properties are set; a misconfiguration will result in an exception being thrown
        factory.afterPropertiesSet();
        return factory.getObject();
     }
  
     /**
      * &lt;p&gt;External configuration properties for a JPA EntityManagerFactory created by Spring.&lt;/p&gt;
      * 
      * @return A map containing JPA configuration properties
      */
     private Properties jpaProperties() 
     {
        // Configure Hibernate-based JPA Settings when initializing database sessions
        Properties jpaProperties = new Properties();
        jpaProperties.put(org.hibernate.cfg.Environment.STATEMENT_BATCH_SIZE,
              environment.getProperty(org.hibernate.cfg.Environment.STATEMENT_BATCH_SIZE));
        jpaProperties.put(org.hibernate.cfg.Environment.STATEMENT_FETCH_SIZE,
              environment.getProperty(org.hibernate.cfg.Environment.STATEMENT_FETCH_SIZE));
        jpaProperties.put(org.hibernate.cfg.Environment.SHOW_SQL,
              environment.getProperty(org.hibernate.cfg.Environment.SHOW_SQL));
        jpaProperties.put(org.hibernate.cfg.Environment.FORMAT_SQL,
              environment.getProperty(org.hibernate.cfg.Environment.FORMAT_SQL));
        jpaProperties.put(org.hibernate.cfg.Environment.MAX_FETCH_DEPTH,
              environment.getProperty(org.hibernate.cfg.Environment.MAX_FETCH_DEPTH));
        jpaProperties.put(org.hibernate.cfg.Environment.AUTOCOMMIT,
              environment.getProperty(org.hibernate.cfg.Environment.AUTOCOMMIT));
        jpaProperties.put(org.hibernate.cfg.Environment.USE_SECOND_LEVEL_CACHE,
              environment.getProperty(org.hibernate.cfg.Environment.USE_SECOND_LEVEL_CACHE));
        jpaProperties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "validate");
        return jpaProperties;
     }
  
     @Bean(name="platformDataSource")
     @Qualifier("platformDataSource")
     public DataSource platformDataSource()
     {
        logger.trace("Initializing CSM platform datasource configurations.");
        DataSource platformDataSource = new DataSource();
        platformDataSource.setDefaultAutoCommit(true);
        platformDataSource.setDefaultReadOnly(false);
        platformDataSource.setDefaultCatalog(environment.getProperty("mta.db.catalog"));
        platformDataSource.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        platformDataSource.setDriverClassName(environment.getProperty("mta.db.driver"));
        platformDataSource.setUrl(environment.getProperty("mta.db.url"));
        platformDataSource.setUsername(environment.getProperty("mta.db.user"));
        platformDataSource.setPassword(DAOUtils.decrypt(environment.getProperty("mta.db.password")));
  
        return platformDataSource;
     }
  
     @Bean(name="csmDataSources")
     @Qualifier("csmDataSources")
     public Map&lt;String, DataSource&gt; dataSourcesCsm()
     {
        logger.trace("Initializing CSM Platform Datasource.");
        return ((MultiTenantDataSourceLookup)dataSourceLookup).initializeDataSources(platformDataSource());
     }
  
     @Primary
     @Bean(name="platformTransactionManager")
     @Qualifier("platformTransactionManager")
     public PlatformTransactionManager transactionManager()
     {
        logger.trace("Initializing CSM platform transaction manager.");
  
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(platformEntityManagerFactory());
        return transactionManager;
     }
  
     @Bean(name="platformSessionFactory")
     @Qualifier("platformSessionFactory")
     public LocalSessionFactoryBean sessionFactory()
     {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(platformDataSource());
        sessionFactory.setPackagesToScan(new String[] { "com.sa.csm.dao.mta.model" });
        sessionFactory.setHibernateProperties(jpaProperties());
        return sessionFactory;
     }
  
     @Bean(name="hibernateExceptionTranslator")
     @Qualifier("hibernateExceptionTranslator")
     public HibernateExceptionTranslator hibernateExceptionTranslator()
     {
        logger.trace("Initializing hibernate exception translator.");
        return new HibernateExceptionTranslator();
     }
  
     @Bean(name="poolingStrategy")
     @Qualifier("poolingStrategy")
     public PoolingStrategy poolingStrategy()
     {
        return PoolingStrategy.getPoolingStrategyByCode(environment.getProperty("mta.db.pooling.strategy"));
     }
  }
  </code></pre>
  
  <h1>MultiTenantDatabaseConfig.java</h1>
  
  <pre><code>/**
   * &lt;p&gt;
   * This is the base class that implements configuration of multitenancy with multiple databases and API services using Spring JPA and
   * Hibernate. All the multi-tenant JPA related beans are instantiated from here. During the bootstrapping process, it configures the general
   * JPA infrastructure ((i.e., a DataSource connecting to a database as well as a JPA EntityManagerFactory) for the CSM tenant database
   * access.
   * &lt;/p&gt;
   * 
   * @author thada02
   *
   */
  public abstract class MultiTenantDatabaseConfig
  {
     private static final Logger logger = LogManager.getLogger(MultiTenantDatabaseConfig.class);
  
     @Inject
     private Environment environment;
  
     @Bean(name="entityManagerFactoryBean")
     @Qualifier("entityManagerFactoryBean")
     @DependsOn(value={"multiTenantConnectionProvider","currentTenantIdentifierResolver"})
     public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(
           MultiTenantConnectionProvider multitenantConnectionProvider,
           CurrentTenantIdentifierResolver currentTenantIdentifierResolver)
     {
        logger.trace("Initializing entity manager factory bean for enabling multi-tenancy configuration.");
        // Exposes JpaVendorAdapter implementation for Hibernate's Persistence Provider and EntityManager interface
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setDatabasePlatform(environment.getProperty(org.hibernate.cfg.Environment.DIALECT));
        jpaVendorAdapter.setShowSql(Boolean.getBoolean(environment.getProperty(org.hibernate.cfg.Environment.SHOW_SQL)));
  
        // Configure Hibernate-based JPA Settings when initializing database sessions
        Properties jpaProperties = this.jpaProperties();
        jpaProperties.put(org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER, multitenantConnectionProvider);
        jpaProperties.put(org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolver);
  
        // No dataSource is set to resulting entityManagerFactoryBean
        LocalContainerEntityManagerFactoryBean result = new LocalContainerEntityManagerFactoryBean();
  
        // Trigger Spring to scan for classes annotated with @Entity and @MappedSuperclass and automatically add those to the JPA
        // PersistenceUnit
        String[] packagesToScan = environment.getProperty("jpa.entity.packages.to.scan").split(",");
        result.setPackagesToScan(packagesToScan);
  
        // Specify the JpaVendorAdapter implementation for the desired JPA provider
        result.setJpaVendorAdapter(jpaVendorAdapter);
        result.setJpaProperties(jpaProperties);
  
        // Perform initialization after all the properties are set; a misconfiguration will result in an exception being thrown
        result.afterPropertiesSet();
        return result;
     }
  
     /**
      * &lt;p&gt;External configuration properties for a JPA EntityManagerFactory created by Spring.&lt;/p&gt;
      * 
      * @return A map containing JPA configuration properties
      */
     private Properties jpaProperties() 
     {
        // Configure Hibernate-based JPA Settings when initializing database sessions
        Properties jpaProperties = new Properties();
        jpaProperties.put(org.hibernate.cfg.Environment.DIALECT, environment.getProperty(org.hibernate.cfg.Environment.DIALECT));
        jpaProperties.put(org.hibernate.cfg.Environment.STATEMENT_BATCH_SIZE, environment.getProperty(org.hibernate.cfg.Environment.STATEMENT_BATCH_SIZE));
        jpaProperties.put(org.hibernate.cfg.Environment.STATEMENT_FETCH_SIZE, environment.getProperty(org.hibernate.cfg.Environment.STATEMENT_FETCH_SIZE));
        jpaProperties.put(org.hibernate.cfg.Environment.DEFAULT_SCHEMA, environment.getProperty(org.hibernate.cfg.Environment.DEFAULT_SCHEMA));
        jpaProperties.put(org.hibernate.cfg.Environment.SHOW_SQL, environment.getProperty(org.hibernate.cfg.Environment.SHOW_SQL));
        jpaProperties.put(org.hibernate.cfg.Environment.FORMAT_SQL, environment.getProperty(org.hibernate.cfg.Environment.FORMAT_SQL));
        jpaProperties.put(org.hibernate.cfg.Environment.MAX_FETCH_DEPTH, environment.getProperty(org.hibernate.cfg.Environment.MAX_FETCH_DEPTH));
        jpaProperties.put(org.hibernate.cfg.Environment.AUTOCOMMIT, environment.getProperty(org.hibernate.cfg.Environment.AUTOCOMMIT));
        jpaProperties.put(org.hibernate.cfg.Environment.USE_SECOND_LEVEL_CACHE, environment.getProperty(org.hibernate.cfg.Environment.USE_SECOND_LEVEL_CACHE));
        jpaProperties.put(org.hibernate.cfg.Environment.HBM2DDL_AUTO, "none");
        jpaProperties.put(org.hibernate.cfg.Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE.name());
        return jpaProperties;
     }
  
     /**
      * &lt;p&gt;
      * Obtain an instance of the JPA Entity Manager Factory for obtaining the hibernate's session factory.
      * &lt;/p&gt;
      * 
      * @param entityManagerFactoryBean  The entity manager factory bean
      * @return An entity manager factory
      */
     @Bean(name="entityManagerFactory")
     @Qualifier("entityManagerFactory")
     @Autowired
     public EntityManagerFactory entityManagerFactory(@Qualifier("entityManagerFactoryBean") LocalContainerEntityManagerFactoryBean entityManagerFactoryBean)
     {
        logger.trace("Initializing entity manager factory associated for multi-tenancy configuration.");
        return entityManagerFactoryBean.getObject();
     }
  
     /**
      * &lt;p&gt;
      * Initialize the hibernate 5 transaction manager using the using the application-managed entity manager factory.
      * &lt;/p&gt;
      * 
      * @param entityManagerFactory
      *           The entity manager
      * @return The transaction manager
      */
     @Bean(name="transactionManager")
     @Qualifier("transactionManager")
     public PlatformTransactionManager transactionManager(@Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory)
     {
        logger.trace("Initializing transaction manager for multi-tenancy configuration.");
        SessionFactory sessionFactory = entityManagerFactory.unwrap(SessionFactory.class);
        HibernateTransactionManager txManager = new HibernateTransactionManager();
        // The txManager bean needs to unwrap the EntityManagerFactory implementation, Hibernate's SessionFactory in this case, to set the
        // AutodetectDataSource attribute to false, this is a requirement for multitenancy to work
        txManager.setAutodetectDataSource(false);
        txManager.setSessionFactory(sessionFactory);
  
        return txManager;
     }
  
     /**
      * &lt;p&gt;
      * Returns an instance of &lt;code&gt;MultiTenantConnectionProvider&lt;/code&gt;
      * &lt;/p&gt;
      * 
      * @return An instance of MultiTenantConnectionProvider
      */
     @Bean(name="multiTenantConnectionProvider")
     @Qualifier("multiTenantConnectionProvider")
     public MultiTenantConnectionProvider multiTenantConnectionProvider()
     {
        return new MultiTenantConnectionProviderImpl();
     }
  
     /**
      * &lt;p&gt;
      * Returns an instance of &lt;code&gt;CurrentTenantIdentifierResolver&lt;/code&gt;
      * &lt;/p&gt;
      * 
      * @return An instance of CurrentTenantIdentifierResolver
      */
     @Bean(name="currentTenantIdentifierResolver")
     @Qualifier("currentTenantIdentifierResolver")
     public CurrentTenantIdentifierResolverImpl currentTenantIdentifierResolver()
     {
        return new CurrentTenantIdentifierResolverImpl();
     }
  }
  </code></pre>
  
  <h2>SlaQualifyingItemsDataAccessService.java</h2>
  
  <pre><code>@Service
  public class SlaQualifyingItemsDataAccessService
  {
     private static final Logger logger = LogManager.getLogger(SlaQualifyingItemsDataAccessService.class);
  
     @Autowired
     private SlaQualifyingItemsRepository slaQualifyingItemsRepository;
  
     ... ... ...
     ... ... ...
     /**
      * &lt;p&gt;Update Affected Service for the SLA Qualifying Items.&lt;/p&gt;
      * 
      * @param rowIds List of SLA Qualifying Item Row IDs to update
      * @param affectedCiId The affected service ID value to update 
      */
     public void updateAffectedCiInSlaQualifyingItems(List&lt;Long&gt; rowIds, Long affectedCiId)
     {
        logger.trace("Updating Affected Service in SLA Qualification Item Row IDs#: [{}] with Affected CI ID: {}",
              rowIds.stream().map(String::valueOf).collect(Collectors.joining(",")), affectedCiId);
        slaQualifyingItemsRepository.updateInBulkAffectedService(CurrentExecutionContext.getTenantIdAsInteger(), rowIds,
              affectedCiId);
     }
     ... ... ...
     ... ... ...
  </code></pre>
  
  <h2>SlaQualifyingItemsRepository.java</h2>
  
  <pre><code>public interface SlaQualifyingItemsRepository extends JpaRepository&lt;SlaQualifyingItemsEntity, RowId&gt;
  {
      ... ... ...
      ... ... ...
      @Modifying
      @Transactional(propagation=Propagation.REQUIRED, rollbackFor=RuntimeException.class)
      @Query("UPDATE SlaQualifyingItemsEntity sqie SET sqie.affectedCiId = :affectedCiId WHERE sqie.id.slice=:slice AND sqie.id.rowId IN (:rowIds)")
      public void updateInBulkAffectedService(Integer slice, List&lt;Long&gt; rowIds, Long affectedCiId);
      ... ... ...
      ... ... ...
  }
  </code></pre>