Hi, in my application I use the MySQL specific semantic of SELECT FOR UPDATE, i.e. it locks the selected rows and let all other transactions wait. Now, in my test case I wanted to use the Apache Derby database, because it also supports SELECT FOR UPDATE, but unfortutanely only for too simple statements and it does not work. Limit it also not supported as in MySQL. So I'm on the search for another embeddable database which supprots the MySQL semantics. But actually I would like to get rid of these native queries.
The problem is the following: I have a bean which must be fetched for some modifications. These modifications must be atomic (so a serializable isolation level would be necessary). The method which does this must be guaranteed to succeed and should not pain the user with an OptimisticLockingException, RollbackException and so on. I have to guarantee that the transaction is sucessful, I cannot retry the transaction in case of an exception because it is possible that I loose again and another transaction is again faster. So all transactions must be enqueued for execution like the SLECT FOR UPDATE seems to do it in MySQL. Without modifications the following happens: Transaction T1 starts Transaction T2 starts Transaction T2 ends Transaction T1 ends The updates from T2 are lost. Transaction T2 should wait until T1 has finished and commited the transaction on the table rows. The default isolation level should be REPEATABLE_READ, so I would expect, that T1 must fail in some way, it does not happen, but this is not the problem, what I want is, that T2 waits until T1 has finished. The only way I could reach this was a native SELECT FOR UPDATE query. I have also tried the following: 1.) SERIALIZATION I set in the xyz-ds.xml in JBoss the transaction level to SERIALIZATION. T1 could not complete and an exception was thrown. Well, OK, but this does not help, the database still tries to run the transaction in parallel. I do not want an exception, if an exception occurs i coul try to repeat now the transaction until it succeeds, but if again another transaction is fatser I have to do it over and over again. This is not correct. 2.) Optimistic Locking I added a property the each entity and annoted it with @Version. An OptimisticLockingException was thrown. Again I do not want an exception. How can I reach my goal with EJB with a non native approach? By the way does anybody know how the EntityManager.lock() method works? I have to passed an entity to the method, but after getting the entity another transaction can already have updated the entity and locking seems to be not making sense to me. Is the entity really locked now? As far as I have read, the database will again throw an OptimisticLockingException. WBR, Karsten