Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

Karsten.Ohme
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
Reply | Threaded
Open this post in threaded view
|

Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

dblevins
Administrator

On Feb 19, 2008, at 6:53 AM, <[hidden email]> <[hidden email]
 > 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

Reply | Threaded
Open this post in threaded view
|

RE: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

Karsten.Ohme
In reply to this post by Karsten.Ohme

> -----Urspr√ľngliche Nachricht-----
> Von: David Blevins [mailto:[hidden email]]
> Gesendet: Mittwoch, 20. Februar 2008 00:03
> An: [hidden email]
> Betreff: Re: Problems Testing - native SQL - SELECT FOR
> UPDATE - Pessimistic Locking
>
>
> On Feb 19, 2008, at 6:53 AM, <[hidden email]>
> <[hidden email]  > 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
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

Dain Sundstrom
In reply to this post by Karsten.Ohme
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, <[hidden email]> <[hidden email]
 > 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

Reply | Threaded
Open this post in threaded view
|

Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

Karsten Ohme
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, <[hidden email]>
> <[hidden email]> 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
>

Reply | Threaded
Open this post in threaded view
|

Re: Problems Testing - native SQL - SELECT FOR UPDATE - Pessimistic Locking

Dain Sundstrom
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, <[hidden email]> <[hidden email]
>> > 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
>