You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomee.apache.org by Ka...@t-systems.com on 2008/02/19 15:53:07 UTC
Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
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
Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Posted by David Blevins <da...@visi.com>.
On Feb 19, 2008, at 6:53 AM, <Ka...@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
Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Posted by Dain Sundstrom <da...@iq80.com>.
The OpenJPA guys said that you need to add the property:
openjpa.LockManager=pessimistic
to your persistence unit. This will cause OpenJPA to use select for
update.
-dain
On Feb 21, 2008, at 3:34 PM, Karsten Ohme wrote:
> 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, <Ka...@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
>
Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic
Locking
Posted by Karsten Ohme <wi...@t-online.de>.
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, <Ka...@t-systems.com>
> <Ka...@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
>
Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking
Posted by Dain Sundstrom <da...@iq80.com>.
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.
-dain
On Feb 19, 2008, at 6:53 AM, <Ka...@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