as commented repeatedly I agree with you I don't buy the "nested transactions" concept. 100%
And agree the commit() becomes maybe_commit(), in fact it becomes do_nothing(), because final decision of real commit becomes delayed until top level is closed. That was my initial pov about to remove the "commit" and "rollback" words from the API, but finally was agreed to have them there. It's just a matter of knowing what they mean.
About uses, well, I can see the point about, for example, making the creation of one course, as one transaction (it involves creating various records in various tables) and any failure in the process being nicely reverted sounds ok (normal 1-level transaction).
But problems arise when I'm trying to use that create_course() function in web services, or in restore or bulk creation of courses... where I also want to handle transactions. For example, I want to make the whole restore process to run in a transaction, it creates zillions of records and any failure is really an (almost invisible luckily) mess. But, with real 1-level transactions I'll be forbidden to use create_course(), while with the "delegated transactions" (uhm, perhaps whe should name them that way!) I'll be able to do so and restore will have the control.
In any case, note that we never will rely in transaction status (no matter if they are 1-level real ones or delegated/nested ones). Mainly because not all our DBs support them. It's ONLY one measure to reduce the mess in DB and not to eliminate it! Only to reduce! It's only one anti-mess-feature for DBs supporting them. And, as said, this principle is the same, no matter if we are using 1-level or n-level ones.
I think we need to be 300% careful before start using them and just then we'll discover real problems (if they exist). But, in the other side, all reports I've found from ADOdb, Mahara, OU... comment they are working ok (and I'd add: "if under control"). That's the key, preventing any use related to "external subsystems" will help, for sure.
Finally, 100% agree about your idea of "moodle gargle very loud and ugly complaints when nested transactions are used". Not because of complaints, but to let the developer know he's executing code that have transactions within it. So, perhaps I'd add one new debug parameter, enabled by default, call it "show nested transactions stacks", so, in DEBUG_DEVELOPER mode, it will be showing the whole transactions stack when the inner one is commit/rollback. Definitively that will help.
So, having the feature will lead us to be able to use it, surely as 1-level only in most places initially (islands), but with the possibility of allowing caller code to take the control in the future (delegated).
Uhm... the most I think the most I like the "delegated" concept.