> -----Ursprüngliche Nachricht----- > Von: David Blevins [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 20. Februar 2008 00:03 > An: users@openejb.apache.org > Betreff: Re: Problems Testing - native SQL - SELECT FOR > UPDATE - Pessimistic Locking > > > 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. > > 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#Co ntainersandResources-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.
Well, like I have written above. I want that the operation completes successfully. I have already tried the transaction levels, but if concurrent transaction are not blocked they are tried in parallel and I get an exception. Retrying it is no possibility, because the queries to the database effect for consecutive calls the same row. So again, the transaction may fail. And again and again. For Optimistic locking I always got an OptimisticLockingException like expected. I don't know how the lock() method should be useful. I have to retrieve the object before and another transaction might have updated the row before I can get the lock. So, the data which is locked might be not more relevant anymore. If a retry now the query and again get the lock the same may happen. So the algorithm is not guaranteed to work correctly. WBR, Karsten > > -David > >
