I have an application that counts statistics,  each statistics is one row in a DB with name, count, version columns. I also got a Entity bean that is mapped to the statistics table.

The same statistics can be updated from different threads, this is why I wanted to use the OptimisticLocking mechanism and added the version column and annotaion to the code.

I have a session bean that invokes a DataLayer SessionBean that in turn select the proper statistics from the db using the entity manager and increment the value using the set method on the statistics entity bean. I'm calling flush on the entitymanager just after the set call and surronded this call with try/catch for optimistic locking exception.

my objective was to catch the optimistic lock exception at the datalayer level and just rerun the same command again (after refreshing the statistic entity bean)

Another thing that I did to play it safe is to declare this method in the datalayer as requires new transaction type so it will run in a separate transaction from the calling bean so if it is failling the other sql command in the parent trasaction will still be commited.

The problem is that I got the following Exception from the set code:

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [com.fc.entity.beans.Statistic#8]

And when I'm trying to rerun the same action I'm getting:

2010-01-07 10:27:41,002 ERROR [org.hibernate.util.JDBCExceptionReporter] (pool-19-thread-7) Transaction is not active: tx=TransactionImple < ac, BasicAction: 7f000001:d442:4b44fdc9:2c23 status: ActionStatus.ABORT_ONLY >; - nested throwable: (javax.resource.ResourceException: Transaction is not active: tx=TransactionImple < ac, BasicAction: 7f000001:d442:4b44fdc9:2c23 status: ActionStatus.ABORT_ONLY >)
2010-01-07 10:27:41,002 DEBUG [com.fc.ejbs.DataLayer] (pool-19-thread-7) incrementStatistic Can not create Statistic, got Exception org.hibernate.exception.GenericJDBCException: Cannot open connection

My questions are:

1. why I'm not getting Optimistic Locking Exception instead of the StaleObjectStateException?

2. Does my approach for optimisticLocking is correct - looing in the datalayer and refreah the object each time it fails, then flush the data after the set and surronded the flush with try/catch

3. Why is transaction closed after this exception, shouldnt I have a way to rerun the update from the same transaction? how can I recover from optimistic locking exception in another way?

4. Is it a right assumption that if I'm running the method from a requires new transaction type the caller method should not roll back? actually what I see is that the parent is also rolling back and I dont think that this is the correct behaviour.