tomee-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dain Sundstrom <>
Subject Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Date Thu, 21 Feb 2008 20:57:41 GMT

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  

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.


On Feb 19, 2008, at 6:53 AM, <> <

 > 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:
> I set in the xyz-ds.xml in JBoss the transaction level to  
> 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

View raw message