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

I would like to describe an issue I got on JPA/MySQL; it may inspire your investigations...

• Global transaction begin
• transaction 1) a new row on table Address (autoincrement)
• transaction 2) a new row on table Entreprise with a foreign key on table Addres; the new Entreprise inserted is linked to the new Adress #ID.
• End of Global transaction

MYSQL dead-locks for this case with ResourceLocal / JPATransactionManager.

Actually, it seems that we cannot open several nested transactions. The global transaction seems to be merged with transactions 1) and 2). Transaction 2) ends in deadlock because data cannot be feeded with table A new #Id that is not ready.

However, we can see with the debugger the new adresse row#id between transaction 1 and 2.

Is it similar to your issue ? Do you guess some autoincrement - relation with your deadlock? These followings are possible solutions...

• Solution1 Change isolation level ? -> How ?!!I don't have the answer...And I'm not shure this will change anything.

• Solution2 Replace JPA Entities ID generation strategy (auto or identity) into a custom sequence table.

• Solution3

Check if you cannot use cascade strategy on ManyToOne relationships.

EntrepriseEntity{
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "id_entreprise")
private int id;

@ManyToOne(fetch=FetchType.LAZY,cascade=CascadeType.ALL)
@JoinColumn(name = "id_address")
private AddressEntity address;  

And then save both rows into a single merge() :

EntrepriseEntity e=new EntrepriseEntity();
e.setAddress(new AddressEntity());
e=entityManager.merge(e);

Returned instance with give you back both new #ids inserted, and magic : no longer deadlock...

Solution#3 is smarter, but needs deeper analysis and change some code...

Answer 2

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.