tomee-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <>
Subject Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Date Tue, 19 Feb 2008 14:53:07 GMT

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:


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

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


View raw message