Entity engine SELECT for UPDATE feature

[Brett]

We are running into a concurrency problem with a service that needs to do a
read for update.  Currently, the service returns a record and then the
calling service updates the record.  We are seeing a problem when multiple
requests can update the same record.

In ofbiz is there a where to specify a SELECT for UPDATE read that will lock
the record until the transaction closes?


Thanks,


Brett

[Scott Gray-2]

Hi Brett

You need to make sure that the select to happens in the same  
transaction as the update, by default selects use CONCUR_READ_ONLY  
which means the row can only be updated by the transaction that  
selected it.  That lock ends when the transaction ends regardless of  
whether you still have a copy of the entity.

Regards
Scott

HotWax Media
http://www.hotwaxmedia.com

On 12/11/2009, at 10:27 AM, Brett Palmer wrote:

> We are running into a concurrency problem with a service that needs  
> to do a
> read for update.  Currently, the service returns a record and then the
> calling service updates the record.  We are seeing a problem when  
> multiple
> requests can update the same record.
>
> In ofbiz is there a where to specify a SELECT for UPDATE read that  
> will lock
> the record until the transaction closes?
>
>
> Thanks,
>
>
> Brett

[Brett]

Scott,

Thanks for the reply.  Do you know if this is supported on mysql 5.0 innodb?

We are doing the update in the same transaction but not seeing the
locking behavior.

Brett

On 11/11/09, Scott Gray <[hidden email]> wrote:

> Hi Brett
>
> You need to make sure that the select to happens in the same
> transaction as the update, by default selects use CONCUR_READ_ONLY
> which means the row can only be updated by the transaction that
> selected it.  That lock ends when the transaction ends regardless of
> whether you still have a copy of the entity.
>
> Regards
> Scott
>
> HotWax Media
> http://www.hotwaxmedia.com
>
> On 12/11/2009, at 10:27 AM, Brett Palmer wrote:
>
>> We are running into a concurrency problem with a service that needs
>> to do a
>> read for update.  Currently, the service returns a record and then the
>> calling service updates the record.  We are seeing a problem when
>> multiple
>> requests can update the same record.
>>
>> In ofbiz is there a where to specify a SELECT for UPDATE read that
>> will lock
>> the record until the transaction closes?
>>
>>
>> Thanks,
>>
>>
>> Brett
>
>

--
Sent from my mobile device

[Scott Gray-2]

I have to admit I only came to be aware of it because it was causing  
me problems while developing with derby so I can't say how the  
behavior differs across dbs.  I did a quick search on it but couldn't  
find anything of substance, sorry.

Regards
Scott

On 12/11/2009, at 11:29 AM, Brett Palmer wrote:

> Scott,
>
> Thanks for the reply.  Do you know if this is supported on mysql 5.0  
> innodb?
>
> We are doing the update in the same transaction but not seeing the
> locking behavior.
>
> Brett
>
> On 11/11/09, Scott Gray <[hidden email]> wrote:
>> Hi Brett
>>
>> You need to make sure that the select to happens in the same
>> transaction as the update, by default selects use CONCUR_READ_ONLY
>> which means the row can only be updated by the transaction that
>> selected it.  That lock ends when the transaction ends regardless of
>> whether you still have a copy of the entity.
>>
>> Regards
>> Scott
>>
>> HotWax Media
>> http://www.hotwaxmedia.com
>>
>> On 12/11/2009, at 10:27 AM, Brett Palmer wrote:
>>
>>> We are running into a concurrency problem with a service that needs
>>> to do a
>>> read for update.  Currently, the service returns a record and then  
>>> the
>>> calling service updates the record.  We are seeing a problem when
>>> multiple
>>> requests can update the same record.
>>>
>>> In ofbiz is there a where to specify a SELECT for UPDATE read that
>>> will lock
>>> the record until the transaction closes?
>>>
>>>
>>> Thanks,
>>>
>>>
>>> Brett
>>
>>
>
> --
> Sent from my mobile device