tomee-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Karsten Ohme <widerst...@t-online.de>
Subject Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Date Thu, 21 Feb 2008 23:34:53 GMT
Dain Sundstrom schrieb:
> 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.

Thanks, a lot. This looks great. At least it should lead me into the 
right direction for further searches.

Thanks,
Karsten
> 
> -dain
> 
> On Feb 19, 2008, at 6:53 AM, <Karsten.Ohme@t-systems.com> 
> <Karsten.Ohme@t-systems.com> 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
> 


Mime
View raw message