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.
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?
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.
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.
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.
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.
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.
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.
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.
--
Matt Warnock <[email protected]>
RidgeCrest Herbals, Inc.
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.
On Fri, Aug 13, 2010 at 5:31 PM, Brett Palmer <[email protected]> wrote:
James,
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.
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.
Brett
On Thu, Aug 12, 2010 at 2:15 PM, James McGill <
[email protected]> wrote:
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.
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."
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?
--
James McGill
Phoenix AZ
