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 <[email protected]> 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 <[email protected]> > > 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 <[email protected]> > >>> 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 < > >>>>> [email protected]> 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 > >>> > >
