Change isolation level with Spring 4.0 & Hibernate 4.2.8

Question

we are developing an app with Spring 4.0.0, Hibernate 4.2.8 and Ms SQL Server 8, which uses a custom sequence backed with a DB table and mapped with a Hibernate VO (CustomSequence)

This sequence is accesed within a service call:

Main service starts its own transaction
Execute code, do some things, queries...
Calls sequence service for a sequence value (SequenceService)
SequenceService starts its own transaction (REQUIRES_NEW)
SequenceService finds object, returns value and saves next value
Main service gets value, sets in a business object and saves (at this point the sequence value is already commited by the inner new transaction)
Exits

Snippet of the service which manages custom sequence:

@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.SERIALIZABLE)
@Service("sequenceService")
public class SequenceService implements ISequenceService {

    @Autowired
    private ISequenceDao sequenceDao;

    private Integer getSequence() {

        CustomSequence sequenceOut = sequenceDao.find();

        final Integer nextVal = sequenceOut.getNextVal();
        sequenceOut.setNextVal(nextVal + 1);
        sequenceDao.save(sequenceOut);

        return nextVal;    
    }    
}

Our problem is serializable attribute is completely ignored so 2 concurrent threads access getSequence method and obtain the same value.

If we check isolation with TransactionSynchronizationManager the value seems correct for serializable (value=8):

...
Integer isolation = TransactionSynchronizationManager.getCurrentTransactionIsolationLevel();
...

Our spring xml file is this one:

<context:annotation-config />
<context:component-scan base-package="dev.app"/>
<tx:annotation-driven /> 

<bean name="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:comp/env/jdbc/appDatasource"/>
</bean>    

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" lazy-init="false" >
    <property name="dataSource"> <ref bean="dataSource" /></property>
    <property name="packagesToScan" value="dev.app.model"/>
    <property name="hibernateProperties">
       <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.format_sql">true</prop>
            <!-- Disable LOB creation as connection -->
            <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
        </props>
    </property>
 </bean>

I've checked database serializable capabilities with MS SQL Management Studio with those commands and then execute app code, and it worked (blocked code until studio did commit):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  

BEGIN TRAN  

UPDATE CUSTOM_SEQUENCE set NEXTVAL = 1000;
WAITFOR DELAY '00:1:00'

COMMIT

¿any clues for what's going on? I've read lot of info on internet but to no avail

lot of thanks in advance!

Angular University Angular University · Accepted Answer · 2014-03-07T10:38:02

According to the code of HibernateTransactionManager, this could be because something is setting the prepareConnection flag of the transaction manager to false:

/**
 * Set whether to prepare the underlying JDBC Connection of a transactional
 * Hibernate Session, that is, whether to apply a transaction-specific
 * isolation level and/or the transaction's read-only flag to the underlying
 * JDBC Connection.
 * <p>Default is "true". If you turn this flag off, the transaction manager
 * will not support per-transaction isolation levels anymore.  ...
 */
public void setPrepareConnection(boolean prepareConnection) {
    this.prepareConnection = prepareConnection;
}

Try and put a breakpoint to see if it's the case. Also this flag is always used together with isSameConnectionForEntireSession(session):

if (this.prepareConnection && isSameConnectionForEntireSession(session)) {
    ....
}

isSameConnectionForEntireSession says:

/**
 * Return whether the given Hibernate Session will always hold the same
 * JDBC Connection. This is used to check whether the transaction manager
 * can safely prepare and clean up the JDBC Connection used for a transaction.
 * <p>The default implementation checks the Session's connection release mode
 * to be "on_close".
 */
protected boolean isSameConnectionForEntireSession(Session session) ...

This means a custom isolation level can only be applied if: the flag to do so is enabled at the transaction manager AND there is the guarantee that the same database connection will always be used for the same hibernate session.

If it's not the case, then the transaction manager does not change the isolation settings, because if a session could use multiple sessions for different queries, the transaction manager would not know when the sessions are being sent back to the pool.

This basically means the transaction manager only changes the isolation settings of a database session if it has the guarantee that it can cleanup those same settings before the session is sent to the pool.

Change isolation level with Spring 4.0 & Hibernate 4.2.8

1 Answers