Optimistic locking based on timestamps

[James McGill <ja...@ableengineering.com>]

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

Re: Optimistic locking based on timestamps

[David E Jones <de...@me.com>]

On Aug 14, 2010, at 2:02 PM, Matt Warnock wrote:

> Thank you David for the detailed and thoughtful explanation.  I think I
> see the issue now.
> 
> However, I still see it as largely a business process issue, not a
> programming issue.  You're right, you don't want to tell a customer that
> a product is available, then later tell them it isn't, because someone
> beat them to it.  But the real fact of life is that shopping carts are
> abandoned all the time, so you really shouldn't commit inventory to any
> order until the order is placed and approved.  There are lots of
> potential slips between order placement and shipping (when the product
> REALLY moves out of inventory)-- abandoned carts, declined credit, bad
> shipping address, no shipping service to that location, import/export
> restrictions, management fiat, whatever.  I exercised management fiat
> just yesterday. :)
> 
> Near-simultaneous inventory transactions *per se* shouldn't be a
> problem.  If I order 5 items, then when I commit, my code sets "qty =
> qty - 5" in a single sql statement and is further enclosed within a
> transaction that checks that the resulting (or beginning) qty is not
> less than zero or some other minimum number, or from mixed lot numbers,
> or whatever else your business logic may require.  Very busy sites won't
> collide unless they try to commit inventory to "pending" orders with an
> optimistic lock, which I think is a mistake.  
> 
> If inventory levels are high and goods are fungible, you don't need to
> worry about it.  Tens of seconds, or even days, isn't going to make a
> difference, and even if the item is serialized, you allocate the serial
> number when the order is filled.   
> 
> If inventory is low, or unique, then you have a scarcity problem, and
> you may want to warn the user of that fact, both to encourage the quick
> order, and to manage expectations, so that they are not disappointed--
> but locking the inventory pending an order that may or may not happen
> seems like a wrong approach to me.  If people are doing that, then I
> certainly understand why they are seeing concurrency issues on busy
> sites.
> 
> Another possible approach would be to enter "pending" orders in a
> separate table (or flagged as such in the same table), and only deduct
> from the actual inventory if and when the sale is finally approved.
> That way you could see whether inventory is scarce (orders pending equal
> or exceed some ratio of inventory on hand) and also impose a "first
> come, first served" approach on pending orders, while still imposing
> time limits on completing the order, and possibly even enabling a
> "waiting list".  Kind of like an appointment queue-- "we've had a
> cancellation, so we can take you today at 3pm."  
> 
> It just seems to me that optimistic locking is not the right solution to
> a scarce inventory problem. 

The scenario I was talking about was for inventory reservation when an order is placed, ie decrementing ATP (Available To Promise). The scenario could happen for QOH on shipping in a busy warehouse with many packers (or at least more than one packer). A really common scenario discussed to demonstrate this point is bank accounts and the possibility of getting an incorrect total because of multiple transactions happening at close enough to the same time.

There are many scenarios, you don't have to do something like reservations on add-to-cart in order for this problem to appear. All you need, in theory, is two users changing the same data at close enough to the same time. In practice it usually happens when there are many users as it increases the chances that two will have a conflict.

BTW, I guess I didn't explain it very well, because I did say the solution is not optimistic locking, but pessimistic locking and more specifically a lock on read (as opposed to the more common lock on write in the ReadCommitted transaction isolation).

> In my view, any database that relaxes the ACID requirements to achieve
> concurrency is barely worthy of the name.  ERP (especially financial
> accounting) requires ACID, period.  You can't run an enterprise on less.
> But it does mean you need to break your business processes down into
> small, atomic steps so that-- like using a hammer to drive a screw.
> isolation requirements do not become a concurrency nightmare.  And some
> longer steps (like internally consistent dumps of the entire database)
> may need to be timed carefully.

You might be surprised to find out that roughly 99.99999% (okay, maybe it's only 5 nines, or maybe only 3, but certainly at least two) of business transactions are run on databases that are not strictly ACID, or at least the I (isolation) part of it. That's just the reality of things. The remaining transactions usually use alternative techniques for isolation, such as SELECT FOR UPDATE, instead of making the whole database always use strictly isolated transactions. Databases running in RepeatableRead or Serializable modes are EXTREMELY rare, and many "enterprise" databases don't even support these isolation levels.

-David


> Thanks again for your thoughtful explanation.
> 
> -- 
> Matt Warnock <mw...@ridgecrestherbals.com>
> RidgeCrest Herbals, Inc.
> 
> On Sat, 2010-08-14 at 09:46 -0600, David E Jones wrote:
>> 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 <mw...@ridgecrestherbals.com>
>>> 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 <br...@gmail.com> 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 <
>>>>> james.mcgill@ableengineering.com> 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
>>>>>> 
>>>>> 
>>> 
> 

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

Thank you David for the detailed and thoughtful explanation.  I think I
see the issue now.

However, I still see it as largely a business process issue, not a
programming issue.  You're right, you don't want to tell a customer that
a product is available, then later tell them it isn't, because someone
beat them to it.  But the real fact of life is that shopping carts are
abandoned all the time, so you really shouldn't commit inventory to any
order until the order is placed and approved.  There are lots of
potential slips between order placement and shipping (when the product
REALLY moves out of inventory)-- abandoned carts, declined credit, bad
shipping address, no shipping service to that location, import/export
restrictions, management fiat, whatever.  I exercised management fiat
just yesterday. :)

Near-simultaneous inventory transactions *per se* shouldn't be a
problem.  If I order 5 items, then when I commit, my code sets "qty =
qty - 5" in a single sql statement and is further enclosed within a
transaction that checks that the resulting (or beginning) qty is not
less than zero or some other minimum number, or from mixed lot numbers,
or whatever else your business logic may require.  Very busy sites won't
collide unless they try to commit inventory to "pending" orders with an
optimistic lock, which I think is a mistake.  

If inventory levels are high and goods are fungible, you don't need to
worry about it.  Tens of seconds, or even days, isn't going to make a
difference, and even if the item is serialized, you allocate the serial
number when the order is filled.   

If inventory is low, or unique, then you have a scarcity problem, and
you may want to warn the user of that fact, both to encourage the quick
order, and to manage expectations, so that they are not disappointed--
but locking the inventory pending an order that may or may not happen
seems like a wrong approach to me.  If people are doing that, then I
certainly understand why they are seeing concurrency issues on busy
sites.

Another possible approach would be to enter "pending" orders in a
separate table (or flagged as such in the same table), and only deduct
from the actual inventory if and when the sale is finally approved.
That way you could see whether inventory is scarce (orders pending equal
or exceed some ratio of inventory on hand) and also impose a "first
come, first served" approach on pending orders, while still imposing
time limits on completing the order, and possibly even enabling a
"waiting list".  Kind of like an appointment queue-- "we've had a
cancellation, so we can take you today at 3pm."  

It just seems to me that optimistic locking is not the right solution to
a scarce inventory problem. 

In my view, any database that relaxes the ACID requirements to achieve
concurrency is barely worthy of the name.  ERP (especially financial
accounting) requires ACID, period.  You can't run an enterprise on less.
But it does mean you need to break your business processes down into
small, atomic steps so that-- like using a hammer to drive a screw.
isolation requirements do not become a concurrency nightmare.  And some
longer steps (like internally consistent dumps of the entire database)
may need to be timed carefully.

Thanks again for your thoughtful explanation.

-- 
Matt Warnock <mw...@ridgecrestherbals.com>
RidgeCrest Herbals, Inc.

On Sat, 2010-08-14 at 09:46 -0600, David E Jones wrote:
> 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 <mw...@ridgecrestherbals.com>
> > 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 <br...@gmail.com> 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 <
> >>> james.mcgill@ableengineering.com> 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
> >>>> 
> >>> 
> > 

Re: Optimistic locking based on timestamps

[Jacques Le Roux <ja...@les7arts.com>]

Checking entityengine.xml I found that, by default, only HSQL uses isolation-level="ReadUncommitted" while all others use
isolation-level="ReadCommitted". Since its version 2.0, HSQL now supports ReadCommitted. Should we not switch it also to
ReadCommitted?

I remember liking much HSQL when I began on OFBiz. Then switching on something more robust (mostly Derby/Postgres for me) after some 
unpleasant surprises.
But I guess if you want something really quick and prepared/checked (demo) it may still be useful...

http://en.wikipedia.org/wiki/HSQLDB

Jacques

From: "David E Jones" <de...@me.com>
> 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 <mw...@ridgecrestherbals.com>
>> 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 <br...@gmail.com> 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 <
>>>> james.mcgill@ableengineering.com> 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
>>>>>
>>>>
>>
>
>

Re: Optimistic locking based on timestamps

[David E Jones <de...@me.com>]

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 <mw...@ridgecrestherbals.com>
> 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 <br...@gmail.com> 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 <
>>> james.mcgill@ableengineering.com> 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
>>>> 
>>> 
> 

Re: Optimistic locking based on timestamps

[David E Jones <de...@me.com>]

BJ,

I don't remember commenting about optimistic locking yet, only about "nesting" of transaction (or lack thereof).

Am I forgetting something?

-David


On Aug 14, 2010, at 2:36 AM, BJ Freeman wrote:

> Matt:
> read Davids responses.
> in short Optimistic locking as a database Function.
> Since ofbiz does it own managing of the data, the database itself does not have all the info to manage the locking effectively.
> 
> 
> Matt Warnock sent the following on 8/13/2010 11:45 PM:
>> 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.
>> 

Re: Optimistic locking based on timestamps

[BJ Freeman <bj...@free-man.net>]

Matt:
read Davids responses.
in short Optimistic locking as a database Function.
Since ofbiz does it own managing of the data, the database itself does 
not have all the info to manage the locking effectively.


Matt Warnock sent the following on 8/13/2010 11:45 PM:
> 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.
>

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

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 <mw...@ridgecrestherbals.com>
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 <br...@gmail.com> 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 <
> > james.mcgill@ableengineering.com> 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
> >>
> >

Re: Optimistic locking based on timestamps

[Mike Z <mz...@gmail.com>]

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 <br...@gmail.com> 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 <
> james.mcgill@ableengineering.com> 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
>>
>

Re: Optimistic locking based on timestamps

[Brett Palmer <br...@gmail.com>]

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 <
james.mcgill@ableengineering.com> 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
>

Re: Optimistic locking based on timestamps

[Jacques Le Roux <ja...@les7arts.com>]

+1

Morevoer MySql seems to sleep since it's Oracle, surprised?
On the other hand there is now http://askmonty.org/wiki/MariaDB

I also like Postgres for its licence (MIT/BSD style hence ASL2 compatilble) when MySql is GPL and still MariaDB, even if it's not a 
big deal regarding OFBiz...

Currently in Postgres http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Jacques

From: "Raj Saini" <ra...@gmail.com>
> PostgreSQL 9.0 is going to have built in streaming replication feature. It is in beta 4 which is final beta. You can find more 
> about streaming replication at http://www.depesz.com/index.php/2010/02/01/waiting-for-9-0-streaming-replication/
>
> Thanks,
>
> Raj
>
> On 13/08/10 12:21, Matt Warnock wrote:
>> Synchronization is the one area that postgres has traditionally trailed
>> mysql, but I know I have seen presentations on sync tools (but have not
>> used them) for postgres including:
>>
>> www.slony.info
>> http://eradman.com/posts/pgsql-sync.html
>>
>> There was another promising project whose name I can't now recall, and
>> Google isn't presently helping.  Perhaps after some sleep.
>>
> 

Re: Optimistic locking based on timestamps

[Raj Saini <ra...@gmail.com>]

PostgreSQL 9.0 is going to have built in streaming replication feature. 
It is in beta 4 which is final beta. You can find more about streaming 
replication at 
http://www.depesz.com/index.php/2010/02/01/waiting-for-9-0-streaming-replication/

Thanks,

Raj

On 13/08/10 12:21, Matt Warnock wrote:
> Synchronization is the one area that postgres has traditionally trailed
> mysql, but I know I have seen presentations on sync tools (but have not
> used them) for postgres including:
>
> www.slony.info
> http://eradman.com/posts/pgsql-sync.html
>
> There was another promising project whose name I can't now recall, and
> Google isn't presently helping.  Perhaps after some sleep.
>    

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

Synchronization is the one area that postgres has traditionally trailed
mysql, but I know I have seen presentations on sync tools (but have not
used them) for postgres including:

www.slony.info
http://eradman.com/posts/pgsql-sync.html

There was another promising project whose name I can't now recall, and
Google isn't presently helping.  Perhaps after some sleep.
-- 
Matt Warnock <mw...@ridgecrestherbals.com>
RidgeCrest Herbals, Inc.

On Fri, 2010-08-13 at 11:08 +0530, Raj Saini wrote:
> > Since you're strongly suggesting Postgres, do you have any advice for
> > migrating a live system between databases, hopefully from an Entity
> > perspective?
> >    
> May be export/import of the data in entity-engine-xml help the 
> migration. You do not need to deal with the DMBS specific queries and 
> OFBiz already handles the table creation using entity models.
> 
> Thanks,
> 
> Raj
> 

Re: Optimistic locking based on timestamps

[Jacques Le Roux <ja...@les7arts.com>]

You may try that, remember to Create/Remove All Foreign Keys
https://demo-trunk.ofbiz.apache.org/webtools/control/view/checkdb
Else you will get hung. Note that this is maybe not enough...

Jacques

From: "Raj Saini" <ra...@gmail.com>
>> Since you're strongly suggesting Postgres, do you have any advice for
>> migrating a live system between databases, hopefully from an Entity
>> perspective?
>>    
> May be export/import of the data in entity-engine-xml help the 
> migration. You do not need to deal with the DMBS specific queries and 
> OFBiz already handles the table creation using entity models.
> 
> Thanks,
> 
> Raj
> 
>

Re: Optimistic locking based on timestamps

[Raj Saini <ra...@gmail.com>]

> Since you're strongly suggesting Postgres, do you have any advice for
> migrating a live system between databases, hopefully from an Entity
> perspective?
>    
May be export/import of the data in entity-engine-xml help the 
migration. You do not need to deal with the DMBS specific queries and 
OFBiz already handles the table creation using entity models.

Thanks,

Raj

Re: Optimistic locking based on timestamps

[James McGill <ja...@ableengineering.com>]

On Thu, Aug 12, 2010 at 3:19 PM, David E Jones <de...@me.com> wrote:

>
> As Jacques mentioned chances are you'll still have a better experience with
> Postgres when it comes to concurrency issues, in the way they manage
> transactions and locking in addition to the timestamp resolution issue.
>

The idea of migrating a production system from MySQL to Postgres stops my
heart.   We depend on MySQL's live replication feature for one thing, and
the only issue we've had so far that makes us even consider the database, is
because the delegator is giving us a data integrity problem.

I'm happy to have the confirmation that the problem is not just because
we're doing it wrong.  We are working a solution into the delegator so that
the application can request locks, since it's a pretty unusual case where it
is actually a problem.  Unfortunately, the problem manifests with inventory
counts, which for us is a pretty big issue.

Reading ahead, I'm really interested in the nested transaction problem.

Since you're strongly suggesting Postgres, do you have any advice for
migrating a live system between databases, hopefully from an Entity
perspective?

-- 
James McGill
Phoenix AZ

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

Here is some data on Postgres replication:
http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
It was Bucardo I was looking at a year ago or so, it looked promising.
-- 
Matt Warnock <mw...@ridgecrestherbals.com>
RidgeCrest Herbals, Inc.

On Fri, 2010-08-13 at 10:43 -0600, Brett Palmer wrote:
> David,
> 
> I like postgres for the reason you have mentioned.
> 
> The number one reason we use MySQL in some of our deployments is because its
> replication solution is very good.  You can replicate your data from the
> database very quickly.  We do this for database backups in production as
> well as creating reporting servers.
> 
> I have not been able to find a good replication solution for postgres.  Its
> been a while since I looked but the last time I investigated replication I
> couldn't find any.
> 
> 
> Brett
> 
> 
> 
> 
> 
> On Thu, Aug 12, 2010 at 4:19 PM, David E Jones <de...@me.com> wrote:
> 
> >
> > On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> >
> > > On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> > > jacques.le.roux@les7arts.com> wrote:
> > >
> > >> This is why I prefer to use PostGres but that's another story and of
> > course
> > >> the same problem could occur at the ms level, 1000 time less though...
> > >>
> > >> Jacques
> > >>
> > >
> > >
> > > I was hoping you would post to tell me I was wrong, and  point out the
> > > locking semantics in the delegator that the application can use.
> > >
> > > My current plan is to extend the delegator and minilang so that "findOne"
> > > and <entity-one> can have a "for update" parameter, so that at least the
> > > application can decide to do a select for update, to introduce some
> > locking
> > > to avoid concurrency bugs.
> > >
> > > Right now, it's fairly common to for us to issue inventory items until
> > the
> > > quantity goes below zero, because there's no way to regulate concurrency
> > > between two threads that want to issue.   There are many parts of the
> > system
> > > where this might not be such a problem, but on InventoryItem it's a
> > > potential nightmare.
> > >
> > > What do you think about my idea of giving the delegator a "select for
> > > update"  option?
> >
> > Adding a for-update option is a good idea, and is something I have
> > incorporated into the Moqui design.
> >
> > As Jacques mentioned chances are you'll still have a better experience with
> > Postgres when it comes to concurrency issues, in the way they manage
> > transactions and locking in addition to the timestamp resolution issue.
> >
> > I honestly don't know why so many people like MySQL compared to Postgres,
> > but I know that many people do.  Maybe it's the greater marketing budget of
> > corporate-driven MySQL versus the more community-driven Postgres. It's also
> > a shame that when SAP DB was scavenged for useful things to go into MySQL
> > that it wasn't done the other way around, ie take useful things from MySQL
> > and put them into SAP DB. Of course, I haven't looked into the state of
> > either code base before this was done, but I do know which organization
> > acquired the other and that probably drove the direction for the software
> > (it's bad marketing to come out and say you're tossing most of your main
> > software stack to go forward with another).
> >
> > I could certainly be wrong, and if any MySQL fans out there want to help me
> > understand why maybe it will even make it through my shield of bias.
> >
> > -David
> >
> >

Re: Optimistic locking based on timestamps

[Brett Palmer <br...@gmail.com>]

David,

I like postgres for the reason you have mentioned.

The number one reason we use MySQL in some of our deployments is because its
replication solution is very good.  You can replicate your data from the
database very quickly.  We do this for database backups in production as
well as creating reporting servers.

I have not been able to find a good replication solution for postgres.  Its
been a while since I looked but the last time I investigated replication I
couldn't find any.


Brett





On Thu, Aug 12, 2010 at 4:19 PM, David E Jones <de...@me.com> wrote:

>
> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>
> > On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> > jacques.le.roux@les7arts.com> wrote:
> >
> >> This is why I prefer to use PostGres but that's another story and of
> course
> >> the same problem could occur at the ms level, 1000 time less though...
> >>
> >> Jacques
> >>
> >
> >
> > I was hoping you would post to tell me I was wrong, and  point out the
> > locking semantics in the delegator that the application can use.
> >
> > My current plan is to extend the delegator and minilang so that "findOne"
> > and <entity-one> can have a "for update" parameter, so that at least the
> > application can decide to do a select for update, to introduce some
> locking
> > to avoid concurrency bugs.
> >
> > Right now, it's fairly common to for us to issue inventory items until
> the
> > quantity goes below zero, because there's no way to regulate concurrency
> > between two threads that want to issue.   There are many parts of the
> system
> > where this might not be such a problem, but on InventoryItem it's a
> > potential nightmare.
> >
> > What do you think about my idea of giving the delegator a "select for
> > update"  option?
>
> Adding a for-update option is a good idea, and is something I have
> incorporated into the Moqui design.
>
> As Jacques mentioned chances are you'll still have a better experience with
> Postgres when it comes to concurrency issues, in the way they manage
> transactions and locking in addition to the timestamp resolution issue.
>
> I honestly don't know why so many people like MySQL compared to Postgres,
> but I know that many people do.  Maybe it's the greater marketing budget of
> corporate-driven MySQL versus the more community-driven Postgres. It's also
> a shame that when SAP DB was scavenged for useful things to go into MySQL
> that it wasn't done the other way around, ie take useful things from MySQL
> and put them into SAP DB. Of course, I haven't looked into the state of
> either code base before this was done, but I do know which organization
> acquired the other and that probably drove the direction for the software
> (it's bad marketing to come out and say you're tossing most of your main
> software stack to go forward with another).
>
> I could certainly be wrong, and if any MySQL fans out there want to help me
> understand why maybe it will even make it through my shield of bias.
>
> -David
>
>

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

Then apparently the stuff that is supposed to separate the "unrelated"
transactions in the same database connection wasn't working in this
case, as the log file clearly indicated that MySQL wasn't seeing the
inner transaction in a separate connection, but saw a nested "begin"
statement within the same connection, causing the outer transaction to
abort.

As I say, it may have been some custom code.  Perhaps he just forgot to
set "require-new-transaction" on a custom service or such.  I do recall
that the inner transaction was in a separate service, or at least a
separately called code block, so the transactions were not nested within
a single code segment.  And you're right, that would not be a MySQL
issue in that case.  

-- 
Matt Warnock <mw...@ridgecrestherbals.com>
RidgeCrest Herbals, Inc.

On Thu, 2010-08-12 at 20:49 -0600, David E Jones wrote:
> There isn't really any such things as a "nested" transaction. That
>  implies some sort of hierarchy of transactions, ie you begin some sort
>  of sub-transaction that is part of another transaction. I've only ever
>  seen the concept in database theory, but in real practice most
>  databases don't support them, and I don't think JDBC supports them
>  either.
> 
> What OFBiz does in many places is pause an active transaction, begin an
>  independent transaction, and when that transaction is done then resume
>  the original transaction. The database doesn't even know there is any
>  relationship between the transactions, and in fact there is none
>  except for in the application the both transactions are being managed
>  by the same thread.
> 
> This happens in many places in OFBiz (search for any service with
>  require-new-transaction=true), and works fine on MySQL. This type of
>  code is easy to mess up, ie lock a resource in the paused transaction
>  and try to use it in the second transaction and you'll get a lock.
>  It's not technically a "dead lock" and the database has no way to
>  detect the dependency because it doesn't know the two transactions are
>  related, it just waits until a timeout is hit. However, that is
>  independent of the database, ie it would happen with any database. In
>  spite of other problems I've seen with MySQL, this is not something
>  I've seen any issues with.
> 
> -David
> 
> 
> On Aug 12, 2010, at 7:01 PM, Matt Warnock wrote:
> 
> > I'd have to look back at the archives, but I was emailing with someone
> > who posted a log showing that OFBiz entered one transaction, then
> > entered another at a lower level of code.  MySQL burped at that point
> > and rolled back the outside transaction.  I was not able to replicate
> > the problem under Derby or PostgreSQL, but it seemed odd to me that
> > OFBiz would nest one transaction inside another.  Still, the OFBiz trace
> > logs pretty clearly showed (at least so I thought) that was what had
> > happened.  I found the docs that said that MySQL would not do that, and
> > told him so.  I don't know OFBiz well enough yet to remember where the
> > code burped, and as I said, it never did it for me.
> > 
> > It may have been his custom code or something-- I certainly would hope
> > nested transactions are not "normal" in OFBiz, as support for them is
> > anything but universal.  I'm not even sure Postgres supports them in any
> > "standard" way. I looked for my email, can't find it, but perhaps
> > someone who knows how/where to search the message archives might be able
> > to turn it up.  I'm going by memory, which is a bit dodgy.
> > -- 
> > Matt Warnock <mw...@ridgecrestherbals.com>
> > RidgeCrest Herbals, Inc.
> > 
> > On Thu, 2010-08-12 at 18:26 -0600, David E Jones wrote:
> >> What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.
> >> 
> >> -David
> >> 
> >> 
> >> On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:
> >> 
> >>> We also determined a few months ago that some ofbiz code apparently
> >>> nests transactions, which MySQL does not support, at least as of 5.5,
> >>> which we were looking at then IIRC.
> >>> -- 
> >>> Matt Warnock <mw...@ridgecrestherbals.com>
> >>> RidgeCrest Herbals, Inc.
> >>> 
> >>> On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
> >>>> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> >>>> 
> >>>>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> >>>>> jacques.le.roux@les7arts.com> wrote:
> >>>>> 
> >>>>>> This is why I prefer to use PostGres but that's another story and of course
> >>>>>> the same problem could occur at the ms level, 1000 time less though...
> >>>>>> 
> >>>>>> Jacques
> >>>>>> 
> >>>>> 
> >>>>> 
> >>>>> I was hoping you would post to tell me I was wrong, and  point out the
> >>>>> locking semantics in the delegator that the application can use.
> >>>>> 
> >>>>> My current plan is to extend the delegator and minilang so that "findOne"
> >>>>> and <entity-one> can have a "for update" parameter, so that at least the
> >>>>> application can decide to do a select for update, to introduce some locking
> >>>>> to avoid concurrency bugs.
> >>>>> 
> >>>>> Right now, it's fairly common to for us to issue inventory items until the
> >>>>> quantity goes below zero, because there's no way to regulate concurrency
> >>>>> between two threads that want to issue.   There are many parts of the system
> >>>>> where this might not be such a problem, but on InventoryItem it's a
> >>>>> potential nightmare.
> >>>>> 
> >>>>> What do you think about my idea of giving the delegator a "select for
> >>>>> update"  option?
> >>>> 
> >>>> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
> >>>> 
> >>>> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
> >>>> 
> >>>> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another). 
> >>>> 
> >>>> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
> >>>> 
> >>>> -David
> >>> 
> > 

Re: Optimistic locking based on timestamps

[David E Jones <de...@me.com>]

There isn't really any such things as a "nested" transaction. That implies some sort of hierarchy of transactions, ie you begin some sort of sub-transaction that is part of another transaction. I've only ever seen the concept in database theory, but in real practice most databases don't support them, and I don't think JDBC supports them either.

What OFBiz does in many places is pause an active transaction, begin an independent transaction, and when that transaction is done then resume the original transaction. The database doesn't even know there is any relationship between the transactions, and in fact there is none except for in the application the both transactions are being managed by the same thread.

This happens in many places in OFBiz (search for any service with require-new-transaction=true), and works fine on MySQL. This type of code is easy to mess up, ie lock a resource in the paused transaction and try to use it in the second transaction and you'll get a lock. It's not technically a "dead lock" and the database has no way to detect the dependency because it doesn't know the two transactions are related, it just waits until a timeout is hit. However, that is independent of the database, ie it would happen with any database. In spite of other problems I've seen with MySQL, this is not something I've seen any issues with.

-David


On Aug 12, 2010, at 7:01 PM, Matt Warnock wrote:

> I'd have to look back at the archives, but I was emailing with someone
> who posted a log showing that OFBiz entered one transaction, then
> entered another at a lower level of code.  MySQL burped at that point
> and rolled back the outside transaction.  I was not able to replicate
> the problem under Derby or PostgreSQL, but it seemed odd to me that
> OFBiz would nest one transaction inside another.  Still, the OFBiz trace
> logs pretty clearly showed (at least so I thought) that was what had
> happened.  I found the docs that said that MySQL would not do that, and
> told him so.  I don't know OFBiz well enough yet to remember where the
> code burped, and as I said, it never did it for me.
> 
> It may have been his custom code or something-- I certainly would hope
> nested transactions are not "normal" in OFBiz, as support for them is
> anything but universal.  I'm not even sure Postgres supports them in any
> "standard" way. I looked for my email, can't find it, but perhaps
> someone who knows how/where to search the message archives might be able
> to turn it up.  I'm going by memory, which is a bit dodgy.
> -- 
> Matt Warnock <mw...@ridgecrestherbals.com>
> RidgeCrest Herbals, Inc.
> 
> On Thu, 2010-08-12 at 18:26 -0600, David E Jones wrote:
>> What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.
>> 
>> -David
>> 
>> 
>> On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:
>> 
>>> We also determined a few months ago that some ofbiz code apparently
>>> nests transactions, which MySQL does not support, at least as of 5.5,
>>> which we were looking at then IIRC.
>>> -- 
>>> Matt Warnock <mw...@ridgecrestherbals.com>
>>> RidgeCrest Herbals, Inc.
>>> 
>>> On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
>>>> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>>>> 
>>>>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
>>>>> jacques.le.roux@les7arts.com> wrote:
>>>>> 
>>>>>> This is why I prefer to use PostGres but that's another story and of course
>>>>>> the same problem could occur at the ms level, 1000 time less though...
>>>>>> 
>>>>>> Jacques
>>>>>> 
>>>>> 
>>>>> 
>>>>> I was hoping you would post to tell me I was wrong, and  point out the
>>>>> locking semantics in the delegator that the application can use.
>>>>> 
>>>>> My current plan is to extend the delegator and minilang so that "findOne"
>>>>> and <entity-one> can have a "for update" parameter, so that at least the
>>>>> application can decide to do a select for update, to introduce some locking
>>>>> to avoid concurrency bugs.
>>>>> 
>>>>> Right now, it's fairly common to for us to issue inventory items until the
>>>>> quantity goes below zero, because there's no way to regulate concurrency
>>>>> between two threads that want to issue.   There are many parts of the system
>>>>> where this might not be such a problem, but on InventoryItem it's a
>>>>> potential nightmare.
>>>>> 
>>>>> What do you think about my idea of giving the delegator a "select for
>>>>> update"  option?
>>>> 
>>>> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
>>>> 
>>>> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
>>>> 
>>>> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another). 
>>>> 
>>>> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
>>>> 
>>>> -David
>>> 
> 

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

I'd have to look back at the archives, but I was emailing with someone
who posted a log showing that OFBiz entered one transaction, then
entered another at a lower level of code.  MySQL burped at that point
and rolled back the outside transaction.  I was not able to replicate
the problem under Derby or PostgreSQL, but it seemed odd to me that
OFBiz would nest one transaction inside another.  Still, the OFBiz trace
logs pretty clearly showed (at least so I thought) that was what had
happened.  I found the docs that said that MySQL would not do that, and
told him so.  I don't know OFBiz well enough yet to remember where the
code burped, and as I said, it never did it for me.

It may have been his custom code or something-- I certainly would hope
nested transactions are not "normal" in OFBiz, as support for them is
anything but universal.  I'm not even sure Postgres supports them in any
"standard" way. I looked for my email, can't find it, but perhaps
someone who knows how/where to search the message archives might be able
to turn it up.  I'm going by memory, which is a bit dodgy.
-- 
Matt Warnock <mw...@ridgecrestherbals.com>
RidgeCrest Herbals, Inc.

On Thu, 2010-08-12 at 18:26 -0600, David E Jones wrote:
> What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.
> 
> -David
> 
> 
> On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:
> 
> > We also determined a few months ago that some ofbiz code apparently
> > nests transactions, which MySQL does not support, at least as of 5.5,
> > which we were looking at then IIRC.
> > -- 
> > Matt Warnock <mw...@ridgecrestherbals.com>
> > RidgeCrest Herbals, Inc.
> > 
> > On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
> >> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> >> 
> >>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> >>> jacques.le.roux@les7arts.com> wrote:
> >>> 
> >>>> This is why I prefer to use PostGres but that's another story and of course
> >>>> the same problem could occur at the ms level, 1000 time less though...
> >>>> 
> >>>> Jacques
> >>>> 
> >>> 
> >>> 
> >>> I was hoping you would post to tell me I was wrong, and  point out the
> >>> locking semantics in the delegator that the application can use.
> >>> 
> >>> My current plan is to extend the delegator and minilang so that "findOne"
> >>> and <entity-one> can have a "for update" parameter, so that at least the
> >>> application can decide to do a select for update, to introduce some locking
> >>> to avoid concurrency bugs.
> >>> 
> >>> Right now, it's fairly common to for us to issue inventory items until the
> >>> quantity goes below zero, because there's no way to regulate concurrency
> >>> between two threads that want to issue.   There are many parts of the system
> >>> where this might not be such a problem, but on InventoryItem it's a
> >>> potential nightmare.
> >>> 
> >>> What do you think about my idea of giving the delegator a "select for
> >>> update"  option?
> >> 
> >> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
> >> 
> >> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
> >> 
> >> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another). 
> >> 
> >> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
> >> 
> >> -David
> > 

Re: Optimistic locking based on timestamps

[David E Jones <de...@me.com>]

What do you mean by "nests transactions"? I'm not aware of OFBiz doing anything like that, and in the few contracts I've worked on where clients used MySQL it's not something I've run into.

-David


On Aug 12, 2010, at 4:33 PM, Matt Warnock wrote:

> We also determined a few months ago that some ofbiz code apparently
> nests transactions, which MySQL does not support, at least as of 5.5,
> which we were looking at then IIRC.
> -- 
> Matt Warnock <mw...@ridgecrestherbals.com>
> RidgeCrest Herbals, Inc.
> 
> On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
>> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
>> 
>>> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
>>> jacques.le.roux@les7arts.com> wrote:
>>> 
>>>> This is why I prefer to use PostGres but that's another story and of course
>>>> the same problem could occur at the ms level, 1000 time less though...
>>>> 
>>>> Jacques
>>>> 
>>> 
>>> 
>>> I was hoping you would post to tell me I was wrong, and  point out the
>>> locking semantics in the delegator that the application can use.
>>> 
>>> My current plan is to extend the delegator and minilang so that "findOne"
>>> and <entity-one> can have a "for update" parameter, so that at least the
>>> application can decide to do a select for update, to introduce some locking
>>> to avoid concurrency bugs.
>>> 
>>> Right now, it's fairly common to for us to issue inventory items until the
>>> quantity goes below zero, because there's no way to regulate concurrency
>>> between two threads that want to issue.   There are many parts of the system
>>> where this might not be such a problem, but on InventoryItem it's a
>>> potential nightmare.
>>> 
>>> What do you think about my idea of giving the delegator a "select for
>>> update"  option?
>> 
>> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
>> 
>> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
>> 
>> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another). 
>> 
>> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
>> 
>> -David
> 

Re: Optimistic locking based on timestamps

[Matt Warnock <mw...@ridgecrestherbals.com>]

We also determined a few months ago that some ofbiz code apparently
nests transactions, which MySQL does not support, at least as of 5.5,
which we were looking at then IIRC.
-- 
Matt Warnock <mw...@ridgecrestherbals.com>
RidgeCrest Herbals, Inc.

On Thu, 2010-08-12 at 16:19 -0600, David E Jones wrote:
> On Aug 12, 2010, at 3:21 PM, James McGill wrote:
> 
> > On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> > jacques.le.roux@les7arts.com> wrote:
> > 
> >> This is why I prefer to use PostGres but that's another story and of course
> >> the same problem could occur at the ms level, 1000 time less though...
> >> 
> >> Jacques
> >> 
> > 
> > 
> > I was hoping you would post to tell me I was wrong, and  point out the
> > locking semantics in the delegator that the application can use.
> > 
> > My current plan is to extend the delegator and minilang so that "findOne"
> > and <entity-one> can have a "for update" parameter, so that at least the
> > application can decide to do a select for update, to introduce some locking
> > to avoid concurrency bugs.
> > 
> > Right now, it's fairly common to for us to issue inventory items until the
> > quantity goes below zero, because there's no way to regulate concurrency
> > between two threads that want to issue.   There are many parts of the system
> > where this might not be such a problem, but on InventoryItem it's a
> > potential nightmare.
> > 
> > What do you think about my idea of giving the delegator a "select for
> > update"  option?
> 
> Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.
> 
> As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.
> 
> I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another). 
> 
> I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.
> 
> -David

Re: Optimistic locking based on timestamps

[David E Jones <de...@me.com>]

On Aug 12, 2010, at 3:21 PM, James McGill wrote:

> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> jacques.le.roux@les7arts.com> wrote:
> 
>> This is why I prefer to use PostGres but that's another story and of course
>> the same problem could occur at the ms level, 1000 time less though...
>> 
>> Jacques
>> 
> 
> 
> I was hoping you would post to tell me I was wrong, and  point out the
> locking semantics in the delegator that the application can use.
> 
> My current plan is to extend the delegator and minilang so that "findOne"
> and <entity-one> can have a "for update" parameter, so that at least the
> application can decide to do a select for update, to introduce some locking
> to avoid concurrency bugs.
> 
> Right now, it's fairly common to for us to issue inventory items until the
> quantity goes below zero, because there's no way to regulate concurrency
> between two threads that want to issue.   There are many parts of the system
> where this might not be such a problem, but on InventoryItem it's a
> potential nightmare.
> 
> What do you think about my idea of giving the delegator a "select for
> update"  option?

Adding a for-update option is a good idea, and is something I have incorporated into the Moqui design.

As Jacques mentioned chances are you'll still have a better experience with Postgres when it comes to concurrency issues, in the way they manage transactions and locking in addition to the timestamp resolution issue.

I honestly don't know why so many people like MySQL compared to Postgres, but I know that many people do.  Maybe it's the greater marketing budget of corporate-driven MySQL versus the more community-driven Postgres. It's also a shame that when SAP DB was scavenged for useful things to go into MySQL that it wasn't done the other way around, ie take useful things from MySQL and put them into SAP DB. Of course, I haven't looked into the state of either code base before this was done, but I do know which organization acquired the other and that probably drove the direction for the software (it's bad marketing to come out and say you're tossing most of your main software stack to go forward with another). 

I could certainly be wrong, and if any MySQL fans out there want to help me understand why maybe it will even make it through my shield of bias.

-David

Re: Optimistic locking based on timestamps

[Jacques Le Roux <ja...@les7arts.com>]

From: "James McGill" <ja...@ableengineering.com>
> On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
> jacques.le.roux@les7arts.com> wrote:
> 
>> This is why I prefer to use PostGres but that's another story and of course
>> the same problem could occur at the ms level, 1000 time less though...
>>
>> Jacques
>>
> 
> 
> I was hoping you would post to tell me I was wrong, and  point out the
> locking semantics in the delegator that the application can use.

Sorry I have no time to do that tonight, hopefully someone will jump in though...

Jacques

> My current plan is to extend the delegator and minilang so that "findOne"
> and <entity-one> can have a "for update" parameter, so that at least the
> application can decide to do a select for update, to introduce some locking
> to avoid concurrency bugs.
> 
> Right now, it's fairly common to for us to issue inventory items until the
> quantity goes below zero, because there's no way to regulate concurrency
> between two threads that want to issue.   There are many parts of the system
> where this might not be such a problem, but on InventoryItem it's a
> potential nightmare.
> 
> What do you think about my idea of giving the delegator a "select for
> update"  option?
> 
> 
> -- 
> James McGill
> Phoenix AZ
>

Re: Optimistic locking based on timestamps

[James McGill <ja...@ableengineering.com>]

On Thu, Aug 12, 2010 at 1:52 PM, Jacques Le Roux <
jacques.le.roux@les7arts.com> wrote:

> This is why I prefer to use PostGres but that's another story and of course
> the same problem could occur at the ms level, 1000 time less though...
>
> Jacques
>


I was hoping you would post to tell me I was wrong, and  point out the
locking semantics in the delegator that the application can use.

My current plan is to extend the delegator and minilang so that "findOne"
and <entity-one> can have a "for update" parameter, so that at least the
application can decide to do a select for update, to introduce some locking
to avoid concurrency bugs.

Right now, it's fairly common to for us to issue inventory items until the
quantity goes below zero, because there's no way to regulate concurrency
between two threads that want to issue.   There are many parts of the system
where this might not be such a problem, but on InventoryItem it's a
potential nightmare.

What do you think about my idea of giving the delegator a "select for
update"  option?


-- 
James McGill
Phoenix AZ

Re: Optimistic locking based on timestamps

[Jacques Le Roux <ja...@les7arts.com>]

This is why I prefer to use PostGres but that's another story and of course the same problem could occur at the ms level, 1000 time 
less though...

Jacques

From: "James McGill" <ja...@ableengineering.com>
> 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
> 

Re: Optimistic locking based on timestamps

[Adrian Crum <ad...@yahoo.com>]

I recently committed some code that provides a workaround for the sub-second resolution issue:

http://ci.apache.org/projects/ofbiz/site/javadocs/org/ofbiz/entity/jdbc/JdbcValueHandler.TimestampJdbcValueHandler.html

-Adrian


--- On Thu, 8/12/10, James McGill <ja...@ableengineering.com> wrote:

> From: James McGill <ja...@ableengineering.com>
> Subject: Optimistic locking based on timestamps
> To: user@ofbiz.apache.org
> Date: Thursday, August 12, 2010, 1:15 PM
> 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
>