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#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
