[jboss-user] [EJB 3.0] - SELECT FOR UPDATE in Optimistic transactions

[paoletto]

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)

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4103323#4103323

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4103323