This has been a very useful thread. I now know that I need to dump MySQL asap. I planned on running multiple ofbiz instances for ecommerce and had no idea that this may cause issues. Thanks for the input.
On Fri, Aug 13, 2010 at 5:31 PM, Brett Palmer <[email protected]> wrote: > James, > > We have run into this same problem on MySQL and ofbiz. We worked around the > problem by creating a custom method that got a direction connection from the > transaction manager. Then we wrote a custom SELECT for UPDATE on that > connection. We needed this functionality because we had multiple > application servers hitting the same database and ran into concurrency > problems without it. > > I would like to see the optimistic locking feature enhanced in ofbiz. Maybe > we could move away from timestamps and use an increasing unique ID as a > replacement. This is definitely a problem with MySQL. We may move away > from MySQL if we can find a good replication solution from Postgres. > > > Brett > > On Thu, Aug 12, 2010 at 2:15 PM, James McGill < > [email protected]> wrote: > >> We are having problems with the optimistic locking. With "enable-lock" >> set >> on an Entity, updates in GenericDAO use a timestamp to do locking. >> There are a number of issues with this. The biggest one is that it's not a >> synchronized operation, so there's potential for a race condition within >> customUpdate, which we are actually seeing in production. >> I added code to introduce the "FOR UPDATE" expression when reading the >> timestamp. This brings up another issue, that the timestamp field in MySQL >> has resolution only to the second. So even if you don't have contention on >> the optimistic lock SELECT, you still have to be lucky that your >> transactions are more than one second apart. >> >> I realize this is a fairly difficult problem to address, in general, and >> that "fixing" many concurrency issues leads to risks of deadlock. But we >> are seeing errors in data where the "last update wins." >> >> Has anyone else had concurrency problems when multiple threads are updating >> entities? Are there any locking provisions in the Delegator that would >> allow us to prevent this kind of problem? >> >> -- >> James McGill >> Phoenix AZ >> >
