Karsten,
This is a much harder problem then it looked like at first glance.
The lock system in JPA seems to be designed to do exactly what you
want, but when I tried to use it to get a write lock, I get
OptimisticExceptions. Based on my reading of the spec, it is
technically legal but seems to be a copout on the spirit of the
locking system. It is all quite frustrating. I sent an email to the
OpenJPA list to get help, but it may take a bit to get a response.
In the mean time, you may want to take a look at the OpenJPA
documentation
http://openjpa.apache.org/docs/latest/manual/manual.html
It has lots of text concerning locking and how to acquire a write lock
using a query hint, and even mentions a pessimistic lock manager
implementation. Of course a lot of the options are OpenJPA specific
and won't help you with Hibernate.
-dain
On Feb 19, 2008, at 6:53 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]
> wrote:
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
