tomee-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Blevins <david.blev...@visi.com>
Subject Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Date Tue, 19 Feb 2008 23:03:27 GMT

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.

I'm not sure about the JBoss options, but as far as OpenEJB goes  
there's a property you can set on your Resources of type DataSource  
called "DefaultTransactionIsolation".  It allows you to set  
READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ or SERIALIZABLE.   
Note that not all options are supported by all databases/drivers, so  
it'd be best to start with the most pessimistic and work backwards to  
the more optimistic.

Here's a doc that http://openejb.apache.org/3.0/containers-and-resources.html#ContainersandResources-javax.sql.DataSource

If you can find a setting that works with your driver and database,  
you should have much better luck with the OptimisticLockingException  
and EntityManager.lock() functionality.

-David


Mime
View raw message