Best way to retry a transaction with Seam & Hibernate

Question

I've got a Seam web application working with Seam & Hibernate (JDBC to SQLServer).

It's working well, but under heavy load (stress test with JMeter), I have some LockAcquisitionException or OptimisticLockException.

The LockAquisitionException is caused by a SQLServerException "Transaction (Process ID 64) was deadlock on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction".

I've then written a Seam Interceptor to rerun such transactions for LockAquisitionException :

@AroundInvoke
public Object aroundInvoke(final InvocationContext invocationContext) throws Exception {
    if (instanceThreadLocal.get() == null && isMethodInterceptable(invocationContext)) {
        try {
            instanceThreadLocal.set(this);

            int i = 0;
            PersistenceException exception = null;
            do {
                try {
                    return invocationContext.proceed();
                } catch (final PersistenceException e) {
                    final Throwable cause = e.getCause();
                    if (!(cause instanceof LockAcquisitionException)) {
                        throw e;
                    }
                    exception = e;
                    i++;
                    if (i < MAX_RETRIES_LOCK_ACQUISITION) {
                        log.info("Swallowing a LockAcquisitionException - #0/#1", i, MAX_RETRIES_LOCK_ACQUISITION);
                        try {
                            if (Transaction.instance().isRolledBackOrMarkedRollback()) {
                                Transaction.instance().rollback();
                            }
                            Transaction.instance().begin();
                        } catch (final Exception e2) {
                            throw new IllegalStateException("Exception while rollback the current transaction, and begining a new one.", e2);
                        }
                        Thread.sleep(1000);
                    } else {
                        log.info("Can't swallow any more LockAcquisitionException (#0/#1), will throw it.", i, MAX_RETRIES_LOCK_ACQUISITION);
                        throw e;
                    }
                }
            } while (i < MAX_RETRIES_LOCK_ACQUISITION);

            throw exception;

        } finally {
            instanceThreadLocal.remove();
        }
    }
    return invocationContext.proceed();
}

First question : do you think this interceptor will correctly do the job ?

By googling around and saw that Alfresco (with a forum talk here), Bonita and Orchestra have some methods to rerun such transactions too, and they are catching much more Exceptions, like StaleObjectStateException for instance (the cause of my OptimisticLockException).

My 2nd question follows : for the StaleObjectStateException ("Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)"), normaly you can't just rerun the transaction, as it's a problem of synchronisation with the database and @Version fields isn't it ? Why Alfresco for instance tries to rerun such Transactions caused by such Exceptions ?

EDIT : For LockAcquisitionException caused by SQLServerException, I've looked at some some resources on the web, and even if I should double check my code, it seems that it can happend anyway ... here are the links :

• An article on the subject (with a comment which says it can happend by running out of resources also)
• Another article with sublinks :
  • Microsoft talking about that on support.microsoft.com
  • A way to profile transactions
  • And some advice to reduce such problems

Even Microsoft says "Although deadlocks can be minimized, they cannot be completely avoided. That is why the front-end application should be designed to handle deadlocks."

Answer 1

Actually I finally found how to dodge the famous "Transaction (Process ID 64) was deadlock on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction".

So I will not really answer my question but I will explain what I saw and how I manage to do that.

At first, I thought that I had a "lock escalation problem" which would transform my row locks into page locks and produce my deadlocks (my JMeter test runs on a scenario which does delete / update while selecting rows, but the deletes and updates don't concern necessarily the same rows as the selects).

So I read Lock Escalation in SQL2005 and How to resolve blocking problems that are caused by lock escalation in SQL Server (by MS) and finally Diagnose SQL Server performance issues using sp_lock.

But before trying to detect if I was in a lock escalation situation, I fall on that page : http://community.jboss.org/message/95300. It talks about "transaction isolation" and that SQLServer has a special one which is called "snapshot isolation".

I then found Using Snapshot Isolation with SQL Server and Hibernate and read Using Snapshot Isolation (by MS).

So I first enabled the "snapshot isolation mode" on my database :

ALTER DATABASE [MY_DATABASE]
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE [MY_DATABASE]
SET READ_COMMITTED_SNAPSHOT ON

Then I had to define transaction isolation for JDBC driver to 4096 ... and by reading the book "Hibernate in Action" on paragraph "5.1.6 Setting an isolation level", it reads :

Note that Hibernate never changes the isolation level of connections obtained from a datasource provided by the application server in a managed environment. You may change the default isolation using the configuration of your application server.

So I read Configuring JDBC DataSources (for JBoss 4) and finally edited my database-ds.xml file to add this :

<local-tx-datasource>
    <jndi-name>myDatasource</jndi-name>
    <connection-url>jdbc:sqlserver://BDDSERVER\SQL2008;databaseName=DATABASE</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <user-name>user</user-name>
    <password>password</password>
    <min-pool-size>2</min-pool-size>
    <max-pool-size>400</max-pool-size>
    <blocking-timeout-millis>60000</blocking-timeout-millis>
    <background-validation>true</background-validation>
    <background-validation-minutes>2</background-validation-minutes>
    <idle-timeout-minutes>15</idle-timeout-minutes>
    <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
    <prefill>true</prefill>
    <prepared-statement-cache-size>75</prepared-statement-cache-size>
    <transaction-isolation>4096</transaction-isolation>
</local-tx-datasource>

The most important part is of course <transaction-isolation>4096</transaction-isolation>.

And then, I got no more deadlock problem anymore ! ... so my question is now more or less useless for me ... but perhaps someone could have a real answer !