Hello!
I have this method executed concurrently in many threads (MDBs), which generates quite many OptimisticLockException.
I changed the datasource from HSQL to PostgreSQL and tried to add a
SELECT FOR UPDATE statement to act like a mutex
But why i keep getting OptimisticLockExceptions then?
did i do some mistake?
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public Process fetchProcess_real(int subsystem) {
String lockQuery = "SELECT * FROM BPS_SUBSYSTEM WHERE id = :id FOR UPDATE";
String fetchQuery =
"SELECT " +
" p " +
"FROM " +
" Process AS p " +
"WHERE " +
" p.state = 0 AND " +
" p.processQueue.id IN " +
" (SELECT " +
" pq.id " +
" FROM " +
" ProcessQueue AS pq " +
" WHERE " +
" (pq.subsystem.id = " +
" (SELECT " +
" s.id " +
" FROM " +
" Subsystem AS s " +
" WHERE " +
" (s.id = " + subsystem + ") AND " +
" (s.connected = true) " +
" ) " +
" ) AND " +
" (pq.connected = true) AND " +
" (pq.curActiveProcesses < pq.maxActiveProcesses) " +
" ) " +
"ORDER BY p.priority ASC, p.id ASC";
Query nquery = em.createNativeQuery(lockQuery,Subsystem.class);
nquery.setParameter("id",subsystem);
Subsystem subsys = (Subsystem) nquery.getSingleResult();
Query query = em.createQuery(fetchQuery);
query.setMaxResults(1);
List<Process> ProcessList = query.getResultList();
if ((ProcessList == null) || ProcessList.isEmpty()) return null;
Process p = ProcessList.get(0);
ProcessQueue pq = p.getProcessQueue();
p.use();
pq.incrementActive();
em.merge(p);
em.merge(pq);
return p;
}
(ps the method is called always with the same parameter)
