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 >>> >
