Mailing-List: contact user-help@ofbiz.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ofbiz.apache.org
Received-SPF: pass (nike.apache.org: domain of dejc@me.com designates
 17.148.16.102 as permitted sender)
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Subject: Re: Optimistic locking based on timestamps
From: David E Jones <dejc@me.com>
In-reply-to: <1281768335.2197.2577.camel@matt5>
Date: Sat, 14 Aug 2010 09:46:35 -0600
Content-transfer-encoding: quoted-printable
Message-id: <F1EC6E9E-56ED-47A9-869D-60D3D26AF950@me.com>
References: <AANLkTimEWtjSyuGi3gJXaA48DAOr+iC7ks_QN0hoZNGs@mail.gmail.com>
 <AANLkTin3uiTAsRVYPX9nvbFWEOoq6EU2s7rVWOsu46wJ@mail.gmail.com>
 <AANLkTik5Vs0JzQzpsmVRj38okQmwSbX65MUCpJWD_xWi@mail.gmail.com>
 <1281768335.2197.2577.camel@matt5>
To: user@ofbiz.apache.org


Timestamp-based optimistic locking is a feature of the Entity Engine, =
but it is not used very much in OFBiz. In fact, I'm not sure if it's =
used at all. The way it came into this discussion was, I suppose, as a =
possible solution to the synchronization problems people were having =
with race conditions.

As you mentioned here, which is correct, optimistic locking is really =
only helpful if two people are possibly editing the same data at the =
same time and you want to notify a user if another user has changed the =
data they are working on between the time they got the data from the =
database, and the time they saved their changes to the database. With =
most manual editing, as you mentioned, the reading and writing are done =
in two separate transactions, so that is a case where a SELECT FOR =
UPDATE would not help. As you said, in order for that to be helpful in =
the common case where optimistic locks are used the transaction would =
have to live for many minutes and lock resources for that entire time =
(ie a pessimistic lock).

There are certainly cases where optimistic locks might be useful, and =
they would be things mostly done manually like editing product =
information or any content that lives in the database. Two people could =
accidentally be working on the same product or content at the same time, =
and without optimistic locking the person who saved second would wipe =
out the changes of the person who saved first, but neither would know it =
unless they manually review the data at a later time. If pessimistic =
locking were used in these scenarios it would be like those REALLY =
annoying old source repositories where if you check out a file it is =
"locked" and no one else can change it until you check it back in and =
release the lock (ie they didn't bother to implement any sort of =
merging). With the Entity Engine optimistic lock it won't try to do any =
merging, the purpose is to notify the user that someone else changed the =
data they were working on between the time they read the data to edit =
and the time they tried to save it (the separate read and write =
transactions).

For many race conditions that cause bigger problems the scenario is very =
different. In your example of order data that is likely to be very low =
conflict, but there are many data structures that tend to be higher =
conflict, like inventory data. In order for there to be conflict in =
inventory data all it takes is for two customers to order the same =
product at roughly the same time (ie within the span of the time it =
takes the order transaction to execute, which can be tens of seconds =
sometimes). For a popular item on a busy site this isn't just possible, =
it's really likely. In this case optimistic locking wouldn't be that =
helpful, ie you don't want the behavior where the system essentially =
says "someone else is ordering that product right now, please try again =
later". What you would want is for the database to lock certain records =
so that the second user waits until the first user makes any changes. =
And, what you want them to wait on is being able to READ the data, not =
waiting to WRITE it. The common scenario is that two different threads =
read the current inventory value, then both are working on things =
including decrementing the inventory value, then both write it. In the =
end the result will be wrong because they both started with the same =
value and subtracted from it, and basically whoever writes first will =
have their value ignored and the total at the end will just be the =
original value minus whatever the second thread to write subtracted.

That is a case where pessimistic locking is necessary, and a case where =
things aren't as simple as they may seem.

To understand some of the complexity check out the concept of =
"transaction isolation". The big trick is that for performance and =
concurrency reasons databases do NOT totally isolate transactions and =
update conflicts can easily occur:

http://en.wikipedia.org/wiki/Isolation_(database_systems)

Many databases don't even support the more strict transaction isolation =
levels, and even if they do they are not commonly used except for =
special purposes. With things like SERIALIZABLE the problem is that you =
end up locking, in many cases, entire tables and not just rows within =
those tables and you have HUGE concurrency and deadlock problems that =
result.

The most common level you'll see used is READ COMMITTED, and sometimes =
READ UNCOMMITTED if the database doesn't support READ COMMITTED. You can =
see these settings in the entityengine.xml file.

That is where SELECT FOR UPDATE is useful. You don't want to use the =
SERIALIZABLE transaction isolation, but you want this certain record =
locked even though it hasn't been changed so that other transactions =
don't read the incorrect value.

-David


On Aug 14, 2010, at 12:45 AM, Matt Warnock wrote:

> I'm still a bit confused.  I think I understand the issues, but not =
why
> so many people are apparently having trouble with them.  Or maybe I
> misunderstand them completely.
>=20
> Optimistic locking (as I understand it) is used primarily when editing
> an existing record by hand, since record creation and programmed =
updates
> can just use transactions, which are better for most operations =
anyway.
> Most common business cases I can imagine would not usually involve 2
> people editing (not just viewing) the same record at the same time.
> What business scenario causes these apparently common collisions?
>=20
> Most high-volume business uses don't edit other people's records.  If =
I
> enter an e-commerce order for example, I create the header record,
> several line item records, perhaps some other stuff.  Eventually I
> commit the whole order at once, when it is assigned an order number =
and
> becomes part of the main database, which can all be done in a single
> transaction. =20
>=20
> Others may be entering similar orders, but they are creating different
> header records with different associated line items.  These records
> should all be accumulated into memory-only or temporary tables (I =
would
> assume) until they are committed to the database, and optimistic =
locking
> should never really enter into it, as these records are private to the
> user and current session (like an e-commerce shopping cart) until they
> are committed.  If they are abandoned before they commit, they should
> never leave a trace in the main database, as I see it.  Any code that
> updates the record (to total it, apply taxes, figure shipping, or
> whatever) can work in-memory, or in a single transaction on the
> temporary records, until the whole thing is committed.
>=20
> If I then go back and edit an order, it is usually one I just recently
> entered, and in most cases, no one else should be using it.  When I do
> that, the optimistic lock code should read the record data and note =
the
> time that the record was last modified (or the data itself). I then =
edit
> that data on-screen, and when I commit, it first checks to see that =
the
> data was not modified in the meantime.  In most cases, it wasn't
> modified, and the new data is written, again within the scope of a
> single transaction.
>=20
> If the last-modified date (or the original data) has changed, then a
> collision has occurred, and the system should cancel my commit, =
because
> I was editing data which has changed while I was editing it, and is =
now
> stale.  In most cases, any manual edit takes much more than a second, =
so
> the chance of a time granularity collision on an actual record edit
> seems miniscule. If there is a collision, the system re-reads the
> recently updated data, tells me about the collision, probably discards
> the previous edits, and I can then edit again if necessary.=20
>=20
> It's a poor substitute for an update transaction, but you don't want =
to
> lock a database up for several minutes while a user edits a record by
> hand, and most transactions will timeout long before the user finishes
> the edit.
>=20
> Programmatic data updates like Mike Z describes are much more common,
> but they can usually be managed in a single transaction too.  I don't
> need a lock to calculate a total, enter a timestamp, or similar =
updates,
> as these can all be done inside an ACID transaction, thereby protected
> from other threads, users, application servers, or whatever.  We can
> even suspend one transaction to run an unrelated one, then resume the
> first, as David suggested earlier in this thread.
>=20
> Can you give me an example of the kind of update that leads to the =
kind
> of concurrency issues you describe?  Is OFBiz using optimistic locks
> where transactions are really required?  Or what about James' =
inventory
> count scenario prevents using a transaction instead of an optimistic
> lock?  What am I missing?  Just want to know where the big bear traps
> might be.  Thanks in advance.
>=20
> --=20
> Matt Warnock <mwarnock@ridgecrestherbals.com>
> RidgeCrest Herbals, Inc.
>=20
>=20
> On Fri, 2010-08-13 at 19:52 -0700, Mike Z wrote:
>> This has been a very useful thread.  I now know that I need to dump
>> MySQL asap.   I planned on running multiple ofbiz instances for
>> ecommerce and had no idea that this may cause issues.  Thanks for the
>> input.
>>=20
>> On Fri, Aug 13, 2010 at 5:31 PM, Brett Palmer =
<brettgpalmer@gmail.com> wrote:
>>> James,
>>>=20
>>> We have run into this same problem on MySQL and ofbiz.  We worked =
around the
>>> problem by creating a custom method that got a direction connection =
from the
>>> transaction manager.  Then we wrote a custom SELECT for UPDATE on =
that
>>> connection.  We needed this functionality because we had multiple
>>> application servers hitting the same database and ran into =
concurrency
>>> problems without it.
>>>=20
>>> I would like to see the optimistic locking feature enhanced in =
ofbiz.  Maybe
>>> we could move away from timestamps and use an increasing unique ID =
as a
>>> replacement.  This is definitely a problem with MySQL.  We may move =
away
>>> from MySQL if we can find a good replication solution from Postgres.
>>>=20
>>>=20
>>> Brett
>>>=20
>>> On Thu, Aug 12, 2010 at 2:15 PM, James McGill <
>>> james.mcgill@ableengineering.com> wrote:
>>>=20
>>>> We are having problems with the optimistic locking.   With =
"enable-lock"
>>>> set
>>>> on an Entity, updates in GenericDAO use a timestamp to do locking.
>>>> There are a number of issues with this.  The biggest one is that =
it's not a
>>>> synchronized operation, so there's potential for a race condition =
within
>>>> customUpdate, which we are actually seeing in production.
>>>> I added code to introduce the "FOR UPDATE" expression when reading =
the
>>>> timestamp.  This brings up another issue, that the timestamp field =
in MySQL
>>>> has resolution only to the second.  So even if you don't have =
contention on
>>>> the optimistic lock SELECT, you still have to be lucky that your
>>>> transactions are more than one second apart.
>>>>=20
>>>> I realize this is a fairly difficult problem to address, in =
general, and
>>>> that "fixing" many concurrency issues leads to risks of deadlock.  =
But we
>>>> are seeing errors in data where the "last update wins."
>>>>=20
>>>> Has anyone else had concurrency problems when multiple threads are =
updating
>>>> entities?  Are there any locking provisions in the Delegator that =
would
>>>> allow us to prevent this kind of problem?
>>>>=20
>>>> --
>>>> James McGill
>>>> Phoenix AZ
>>>>=20
>>>=20
>=20