Lately we had to solve concurrent management of database access between different threads of the same process or different processes.
The scenario is as follows: web application deployed on websphere, tasks scheduled by spring scheduler within the same web application and standalone external batches. All these threads must be able to work simultaneously on the same data without causing concurrent problems. Above all because the operations involve non-transactional services on Host invoked via CTG. In essence it means to adopt a pessimistic lock approach instead of optimistic lock.
The application stack is as follows:
- Java 7
- Spring 3.1
- JPA 2.0
- Hibernate 3.6.8
- Websphere 8.5.5
- Oracle 12g
Oracle has three types of Isolation Level: Read Uncommitted, Read Committed and Serialization. Missing Repeatable Read that is exactly what would solve our problem without application interventions. Serialization is too strong as an approach, because we do not want to lock the entire table and prevent any insert. We decide to set Read Committed (Oracle default level) but as expected at the first concurrent access we have an optimistic lock problem:
Caused by: javax.persistence.OptimisticLockException: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)
To solve this type of problems Oracle provides us with the select for update, which means using JPA to lock the instance obtained from the database.
For example, to retrieve objects through a query:
Query q = entityManager().createQuery("SELECT o FROM ObjectA o");
q.setLockMode(LockModeType.PESSIMISTIC_WRITE);
in this way if the object has no lock it will be immediately returned by the db to the calling thread, otherwise the thread will wait until the lock is released.
With this pessimistic lock-oriented approach, lock timeout problems can occur. In this case the exeception should be managed with try / catch to cancel the operation or try again to recover the object. In production it is also possible through an empirical approach to increase the timeout to reduce the frequency of the problem.
Finally, obviously, the retrieve of concurrent objects must be done before invoking non-transactional services such as CTG, to avoid the cancellation of the operation after committing the CTG itself.
Giancarlo Parma 