Logging JDBC/Hibernate/JPA transaction isolation levels

Question

I'm working on a Flex/BlazeDS/Spring/JPA/Hibernate web application hooked up to a Microsoft SQL Server database. It seems to be locking the tables too aggresively. From my research, it looks like using the snapshot isolation policy is the best bet.

I've set things up as such:

  <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" lazy-init="true">
    <property name="persistenceUnitName" value="OrderManagerPersistenceUnit" />
    <property name="dataSource" ref="dataSource"/>
     <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
     </property>
    <property name="jpaProperties">
      <props>
        <prop key="hibernate.jdbc.batch_size">${db.main.hibernate.jdbc.batch_size}</prop>
        <prop key="hibernate.hbm2ddl.auto">${db.main.hbm2ddl.auto}</prop>
        <prop key="hibernate.search.default.indexBase">${db.main.search.default.indexBase}</prop>
        <prop key="hibernate.search.autoregister_listeners">${db.main.search.autoregister_listeners}</prop>
          <prop key="hibernate.show_sql">${db.main.show_sql}</prop>
          <prop key="hibernate.dialect">${db.main.dialect}</prop>
          <prop key="hibernate.connection.isolation">${db.main.isolation}</prop>
          <prop key="hibernate.ejb.naming_strategy">com.herffjones.zebra.db.ZebraNamingStrategy</prop>
      </props>
    </property>
  </bean>

However, I'm not convinced that it's actually using hibernate.connection.isolation. It looks like I have to set some properties on the JDBC datasource as well.

I'd like to verify whether or not it's currently using 4096 as the transaction isolation level for queries.

What packages and log levels can I add to my logback.xml file to clearly see the isolation level that a particular query is using?

Thanks!

Answer 1

You should set the transaction isolation level of hibernate as 2 (the java.sql.Connection constant for READ_COMMITTED.

Then execute the following in your SQL Server 2005 instance (with no active connections):

ALTER DATABASE [database_name] SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE [database_name] SET READ_COMMITTED_SNAPSHOT ON;

Test by executing this query:

SELECT [name], snapshot_isolation_state_desc, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases WHERE [name] = 'database_name';

Now a READ_COMMITTED will be interpreted as READ_COMMITTED_SNAPSHOT in SQL Server.