concurrency problems

[Alexandre Gomes-5]

Hello all,

I'm sending this mail for some help with concurrency issues. Does
anybody know which are the best practices in ofbiz for concurrency
problems such as dirty reads, unrepeatable reads, phantom reads and
lost updates.
Any help with be much appreciated,

Thank you all,
Alexandre Gomes

[Fabian Gorsler]

Hi Alexandre,

perhaps you should specify your problem. Is there actually a problem or
are you just asking to prevent a possible problem?

You can specify the isolation level for the DB connection in your
framework/entity/config/entityengine.xml per data source.

Best regards,
Fabian.

[Alexandre Gomes-5]

Thanks Fabian,

I mean something similar to this excerpt I found about Oracle Forms :

BEGIN OF QUOTE
"Oracle forms for example will issue a SELECT for UPDATE on any row you attempt
to update or delete from the GUI.  It does this since you read the row out at
time T1 and you delete it at time T2.  It wants to make sure that in the time
between T1 and T2 -- no one UPDATED that row.  It wants to make sure you do not
delete the row without having at least looked at the modified information (that
would what is commonly known as a "lost update").  It also does this to ensure
that when the time comes, the delete will proceed in a non-blocking fashion.
The select for update forms puts on the row ensures

o the row was not changed
o the row will NOT be changed
o the row is locked by you and you will be able to delete it.

So, in forms the logic is something akin to :

1) read the data out with select t.*, rowid from T
2) let the user look at the data
3) when the user decides to delete the "5'th" row in the result set, forms will
issue:

     select t.*, rowid from T
      where t.c1 = :block.c1
        and t.c2 = :block.c2
        and ....
        and t.rowid = :block.rowid
     FOR UPDATE NOWAIT;

if that returns ORA-54 (resource busy), you are notified someone else has that
row locked.

if that returns 0 rows, you are told that the data has been modified
-- you need
to requery

if that returns 1 row, you have the row locked and will be able to
delete it and
no one else can touch it."
END OF QUOTE


Do we have to customize the Form widget or write the SELECT FOR UPDATE
explicitly?
Or does the framework already contemplates this cases?

Thanks,
Alex

On 12/2/06, Fabian Gorsler <[hidden email]> wrote:

> Hi Alexandre,
>
> perhaps you should specify your problem. Is there actually a problem or
> are you just asking to prevent a possible problem?
>
> You can specify the isolation level for the DB connection in your
> framework/entity/config/entityengine.xml per data source.
>
> Best regards,
> Fabian.
>
>

[Fabian Gorsler]

On Sat, 2006-12-02 at 23:11 +0000, Alexandre Gomes wrote:
> Do we have to customize the Form widget or write the SELECT FOR UPDATE
> explicitly? Or does the framework already contemplates this cases?

AFAIK the Entity Engine just utilizes the concurrency features of your
used DBMS with your specified isolation level and does not do own checks
on the transactional state of rows.

Checks whether data in rows has been updated could be implemented quite
easy: For each entity the Entity Engine defines two additional fields
(lastUpdatedStamp and lastUpdatedTxStamp - there are even two more, but
they're not necessary in this case) and you could check with a service,
whether this two time stamps have changed since you've fetched data from
this row before you delete the rows.

I could imagine there is already a service for doing that, but I don't
know.

Perhaps someone else could correct me or add additional information to
this topic. I'm not sure, whether I'm writing the truth at the moment,
but I hope I could help you. ;)

Best regards,
Fabian.

[Alexandre Gomes-5]

Thanks once more Fabian,

I think even if you checked the timestamp before the update you
wouldn't be sure your update targeted your actual data or data altered
by another transaction that interleaved between your timestamp
checking and the update. That is why the quoted example does a row
lock.

Thank you once again for your time and see you tomorrow (I'm going to
sleep now!)
Alex

On 12/2/06, Fabian Gorsler <[hidden email]> wrote:

> On Sat, 2006-12-02 at 23:11 +0000, Alexandre Gomes wrote:
> > Do we have to customize the Form widget or write the SELECT FOR UPDATE
> > explicitly? Or does the framework already contemplates this cases?
>
> AFAIK the Entity Engine just utilizes the concurrency features of your
> used DBMS with your specified isolation level and does not do own checks
> on the transactional state of rows.
>
> Checks whether data in rows has been updated could be implemented quite
> easy: For each entity the Entity Engine defines two additional fields
> (lastUpdatedStamp and lastUpdatedTxStamp - there are even two more, but
> they're not necessary in this case) and you could check with a service,
> whether this two time stamps have changed since you've fetched data from
> this row before you delete the rows.
>
> I could imagine there is already a service for doing that, but I don't
> know.
>
> Perhaps someone else could correct me or add additional information to
> this topic. I'm not sure, whether I'm writing the truth at the moment,
> but I hope I could help you. ;)
>
> Best regards,
> Fabian.
>
>

[David E Jones-2]

Fabian is right that we never explicitly lock a row, ie select for  
update. That depends totally on the transaction isolation. For most  
applications we recommend using a ReadCommitted or ReadUncommitted  
isolation level because RepeatableRead and Serializable generally  
have too much overhead and can result in table locks and such that  
increase the chance of dead locks and limit scalability.

If you have sensitive data you have two options:

1. create a special database/schema for them and use Serializable or  
RepeatableRead for those (safest)
2. use the optimistic locking feature in the Entity Engine

Option #2 basically shows the user an error and won't let them change  
the record if the data they are dealing with was updated between  
their read of the data (ie what is shown on the screen) and their  
write of the data (what they send to the Entity Engine). To use this  
just set the enable-lock attribute on the entity element in the  
entity def XML file to true. For more info on it just search around  
for "enable-lock".

-David


On Dec 2, 2006, at 4:46 PM, Alexandre Gomes wrote:

> Thanks once more Fabian,
>
> I think even if you checked the timestamp before the update you
> wouldn't be sure your update targeted your actual data or data altered
> by another transaction that interleaved between your timestamp
> checking and the update. That is why the quoted example does a row
> lock.
>
> Thank you once again for your time and see you tomorrow (I'm going to
> sleep now!)
> Alex
>
> On 12/2/06, Fabian Gorsler <[hidden email]> wrote:
>> On Sat, 2006-12-02 at 23:11 +0000, Alexandre Gomes wrote:
>> > Do we have to customize the Form widget or write the SELECT FOR  
>> UPDATE
>> > explicitly? Or does the framework already contemplates this cases?
>>
>> AFAIK the Entity Engine just utilizes the concurrency features of  
>> your
>> used DBMS with your specified isolation level and does not do own  
>> checks
>> on the transactional state of rows.
>>
>> Checks whether data in rows has been updated could be implemented  
>> quite
>> easy: For each entity the Entity Engine defines two additional fields
>> (lastUpdatedStamp and lastUpdatedTxStamp - there are even two  
>> more, but
>> they're not necessary in this case) and you could check with a  
>> service,
>> whether this two time stamps have changed since you've fetched  
>> data from
>> this row before you delete the rows.
>>
>> I could imagine there is already a service for doing that, but I  
>> don't
>> know.
>>
>> Perhaps someone else could correct me or add additional  
>> information to
>> this topic. I'm not sure, whether I'm writing the truth at the  
>> moment,
>> but I hope I could help you. ;)
>>
>> Best regards,
>> Fabian.
>>
>>

[Alexandre Gomes-5]

Thanks David,

Your comments were very helpful.
Is it possible for you to tell me, according to your experience, which
of the options you mentioned would be advisable for a retail industry
environment?

Thanks for your time,
Alexandre Gomes

On 12/3/06, David E Jones <[hidden email]> wrote:

>
> Fabian is right that we never explicitly lock a row, ie select for
> update. That depends totally on the transaction isolation. For most
> applications we recommend using a ReadCommitted or ReadUncommitted
> isolation level because RepeatableRead and Serializable generally
> have too much overhead and can result in table locks and such that
> increase the chance of dead locks and limit scalability.
>
> If you have sensitive data you have two options:
>
> 1. create a special database/schema for them and use Serializable or
> RepeatableRead for those (safest)
> 2. use the optimistic locking feature in the Entity Engine
>
> Option #2 basically shows the user an error and won't let them change
> the record if the data they are dealing with was updated between
> their read of the data (ie what is shown on the screen) and their
> write of the data (what they send to the Entity Engine). To use this
> just set the enable-lock attribute on the entity element in the
> entity def XML file to true. For more info on it just search around
> for "enable-lock".
>
> -David
>
>
> On Dec 2, 2006, at 4:46 PM, Alexandre Gomes wrote:
>
> > Thanks once more Fabian,
> >
> > I think even if you checked the timestamp before the update you
> > wouldn't be sure your update targeted your actual data or data altered
> > by another transaction that interleaved between your timestamp
> > checking and the update. That is why the quoted example does a row
> > lock.
> >
> > Thank you once again for your time and see you tomorrow (I'm going to
> > sleep now!)
> > Alex
> >
> > On 12/2/06, Fabian Gorsler <[hidden email]> wrote:
> >> On Sat, 2006-12-02 at 23:11 +0000, Alexandre Gomes wrote:
> >> > Do we have to customize the Form widget or write the SELECT FOR
> >> UPDATE
> >> > explicitly? Or does the framework already contemplates this cases?
> >>
> >> AFAIK the Entity Engine just utilizes the concurrency features of
> >> your
> >> used DBMS with your specified isolation level and does not do own
> >> checks
> >> on the transactional state of rows.
> >>
> >> Checks whether data in rows has been updated could be implemented
> >> quite
> >> easy: For each entity the Entity Engine defines two additional fields
> >> (lastUpdatedStamp and lastUpdatedTxStamp - there are even two
> >> more, but
> >> they're not necessary in this case) and you could check with a
> >> service,
> >> whether this two time stamps have changed since you've fetched
> >> data from
> >> this row before you delete the rows.
> >>
> >> I could imagine there is already a service for doing that, but I
> >> don't
> >> know.
> >>
> >> Perhaps someone else could correct me or add additional
> >> information to
> >> this topic. I'm not sure, whether I'm writing the truth at the
> >> moment,
> >> but I hope I could help you. ;)
> >>
> >> Best regards,
> >> Fabian.
> >>
> >>
>
>

[David E Jones-2]

On Dec 3, 2006, at 1:25 AM, Alexandre Gomes wrote:

> Thanks David,
>
> Your comments were very helpful.
> Is it possible for you to tell me, according to your experience, which
> of the options you mentioned would be advisable for a retail industry
> environment?

Hmmm... I don't know. A general system for retail involves a lot of  
different data elements. Is there a particular one or group of them  
that you have in mind? As a generality, nothing special is usually  
needed and transaction isolation like ReadCommitted is fine.

Is there a specific problem you're running into or worried about?

-David

> Thanks for your time,
> Alexandre Gomes
>
> On 12/3/06, David E Jones <[hidden email]> wrote:
>>
>> Fabian is right that we never explicitly lock a row, ie select for
>> update. That depends totally on the transaction isolation. For most
>> applications we recommend using a ReadCommitted or ReadUncommitted
>> isolation level because RepeatableRead and Serializable generally
>> have too much overhead and can result in table locks and such that
>> increase the chance of dead locks and limit scalability.
>>
>> If you have sensitive data you have two options:
>>
>> 1. create a special database/schema for them and use Serializable or
>> RepeatableRead for those (safest)
>> 2. use the optimistic locking feature in the Entity Engine
>>
>> Option #2 basically shows the user an error and won't let them change
>> the record if the data they are dealing with was updated between
>> their read of the data (ie what is shown on the screen) and their
>> write of the data (what they send to the Entity Engine). To use this
>> just set the enable-lock attribute on the entity element in the
>> entity def XML file to true. For more info on it just search around
>> for "enable-lock".
>>
>> -David
>>
>>
>> On Dec 2, 2006, at 4:46 PM, Alexandre Gomes wrote:
>>
>> > Thanks once more Fabian,
>> >
>> > I think even if you checked the timestamp before the update you
>> > wouldn't be sure your update targeted your actual data or data  
>> altered
>> > by another transaction that interleaved between your timestamp
>> > checking and the update. That is why the quoted example does a row
>> > lock.
>> >
>> > Thank you once again for your time and see you tomorrow (I'm  
>> going to
>> > sleep now!)
>> > Alex
>> >
>> > On 12/2/06, Fabian Gorsler <[hidden email]> wrote:
>> >> On Sat, 2006-12-02 at 23:11 +0000, Alexandre Gomes wrote:
>> >> > Do we have to customize the Form widget or write the SELECT FOR
>> >> UPDATE
>> >> > explicitly? Or does the framework already contemplates this  
>> cases?
>> >>
>> >> AFAIK the Entity Engine just utilizes the concurrency features of
>> >> your
>> >> used DBMS with your specified isolation level and does not do own
>> >> checks
>> >> on the transactional state of rows.
>> >>
>> >> Checks whether data in rows has been updated could be implemented
>> >> quite
>> >> easy: For each entity the Entity Engine defines two additional  
>> fields
>> >> (lastUpdatedStamp and lastUpdatedTxStamp - there are even two
>> >> more, but
>> >> they're not necessary in this case) and you could check with a
>> >> service,
>> >> whether this two time stamps have changed since you've fetched
>> >> data from
>> >> this row before you delete the rows.
>> >>
>> >> I could imagine there is already a service for doing that, but I
>> >> don't
>> >> know.
>> >>
>> >> Perhaps someone else could correct me or add additional
>> >> information to
>> >> this topic. I'm not sure, whether I'm writing the truth at the
>> >> moment,
>> >> but I hope I could help you. ;)
>> >>
>> >> Best regards,
>> >> Fabian.
>> >>
>> >>
>>
>>