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