How can I set XACT_ABORT using JPA/Hibernate?

Question

I am using a hibernate entity that maps to a view in MS SQLServer. The view consists of data selected from a table on a linked server. I am able to successfully query this view using JPQL. However, when I try to update the view using entityManager.merge(myEntity), I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Unable to start a nested transaction for OLE DB provider "MSDAORA" for linked server "DBNAME". A nested transaction was required because the XACT_ABORT option was set to OFF. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)...

I need to set XACT_ABORT to ON so that SQLServer can rollback the entire transaction if an error occurs. I was able to work around the issue using a native query, but it's not really ideal.

    String queryString = """set xact_abort on; update table_name set column1 = :column1, column2 = :column2 where id = :id"""
    Query query = entityManager.createNativeQuery(queryString)
    query.setParameter("column1", column1)
    query.setParameter("column2", column2)
    query.setParameter("id", id)
    query.executeUpdate()

Is there any way I can configure hibernate/JPA/JPAVendorAdaptor to execute the "set xact_abort on" for every merge/persist so I don't have to use a native query?

Here is my hibernate configuration:

@Resource
DataSource dataSource

@Bean
JpaVendorAdapter vendorAdapter(){
    return new HibernateJpaVendorAdapter(database:Database.SQL_SERVER)
}

@Bean
AbstractEntityManagerFactoryBean entityManagerFactory(){
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean(dataSource:dataSource, jpaVendorAdapter:vendorAdapter())
    em.setPackagesToScan("com.mycompany.entity")
    return em
}

@Bean
PlatformTransactionManager transactionManager() {
    return new JpaTransactionManager(entityManagerFactory:entityManagerFactory().getObject())
}

Becca Gaspard Becca Gaspard · Accepted Answer · 2013-04-01T15:21:57

I ended up using a hibernate interceptor to append "set xact_abort on" for every insert and update statement.

Here's my interceptor:

class XactAbortInterceptor extends EmptyInterceptor{
   String onPrepareStatement(String sql) {
      if(sql.startsWith("insert") || sql.startsWith("update"))
         sql = "set xact_abort on; " + sql
      return sql
   }
}

Here's where I register the interceptor:

@Bean
AbstractEntityManagerFactoryBean entityManagerFactory(){
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean(dataSource:dataSource, jpaVendorAdapter:vendorAdapter())
    em.setPackagesToScan("com.mycompany.entity")
    em.setJpaPropertyMap(["hibernate.ejb.interceptor": XactAbortInterceptor.class.name])
    return em
}

Now all calls to entityManager.merge(myObject) or entityManager.persist(myObject) will set xact_abort to on.

Hopefully this helps someone else experiencing the same issue!

How can I set XACT_ABORT using JPA/Hibernate?

2 Answers