1 Reply Latest reply on Nov 9, 2007 12:52 PM by i o

    SELECT FOR UPDATE in Optimistic transactions

    i o Newbie

      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)