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;
 }