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 >
