Register JDBC / Hibernate / JPA transaction isolation levels

Question

I am working on a Flex / BlazeDS / Spring / JPA / Hibernate web application connected to a Microsoft SQL Server database. Table locking seems to be too aggressive. From my research, it seems that using a snapshot isolation policy is the best bet.

I installed things 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 sure if it really uses hibernate.connection.isolation. It looks like I should set some properties in the JDBC data source.

I would like to check if it is currently using 4096 as the transaction isolation level for queries.

What packet and log levels can be added to the logback.xml file to clearly see the isolation level that a particular request uses?

Thanks!

Answer 1

You should set the transaction isolation level in sleep mode to 2 (constant java.sql.Connection for READ_COMMITTED.

Then, do the following on an instance of SQL Server 2005 (without active connections):

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

Check the execution of this request:

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

Now READ_COMMITTED will be interpreted as READ_COMMITTED_SNAPSHOT in SQL Server.

Answer 2

I would like to describe the problem I got in JPA / MySQL; it may inspire your research ...

• Start a global transaction
• transaction 1) a new row in the table Address (auto-increment)
• transaction 2) a new row on the Entreprise table with a foreign key on the Addres table; The new Entrepreneur Entered is associated with the new address # ID.
• End of global transaction

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

In fact, it seems that we cannot open several nested transactions. A global transaction seems to be combined with transactions 1) and 2). Transaction 2) ends in a dead end because the data cannot be loaded by the table. New #Id that is not ready.

However, we can see with the debugger a new line adresse # id between transactions 1 and 2.

Does this sound like your problem? Do you assume auto-increment is related to your dead end? These are the following possible solutions ...

• Solution1 Change isolation level? -> How? !! I have no answer ... And I'm not sure if this will change anything.

• Solution2 Replace the strategy for generating JPA object identifiers (auto or identifier) ​​in the user sequence table.

• Solution3

Check if you can use a cascading strategy for 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 lines in one merge ():

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

Returned instance returning new inserted #ids and magic: no longer deadlock ...

Solution # 3 is smarter, but requires more in-depth analysis and code changes ...

Answer 3

Whenever you supply a DataSource , Hibernate will ignore the hibernate.connection.isolation parameter.

Instead, you need to set the isolation level at the DataSource level. Most connection pools or the XA Java EE Application Server DataSources allow you to set the isolation level of a global transaction, so all connections originating from this DataSource inherit the same isolation level.