With great power comes great responsibility.
Combining that with spaghetti that does transaction magic at random places guarantees the sort of pain that makes cursing the entire human race seem like a pretty mild response.
Higher-level abstractions may prevent some footguns; e.g., an “atomic” decorator/annotation commits automatically after a successful call. They are somewhat easier to understand but come with their own limitations and caveats.
The problem isn't being able to commit. The problem is being able to commit and then not notice that you're no longer in the transaction. You could easily have `begin_transaction` return a `Transaction` object, having operations in the transaction happen on the object, and calling `commit` on it makes it throw an error if you try to use it again after. Maybe the reason that working with databases is "ridiculously hard" because the API isn't well-designed...
You told me "No" when I pointed out that a responsible API developer would not make an API like that. Now you're shifting to make some unrelated point about the database itself. I feel like you're implicitly assuming that the same people who write the database internals need to be the ones writing the code that provides a high-level client library that will be used by applications that need to interact with the database from the outside, and I don't agree with that premise at all. If anything, the fact that database internals are so complicated to get write is an argument in favor of having separate developers with different specialties, and not just slapping together an API without thinking about it because you're too busy working on the internals.
I do not know why that mentality exists in the industry, but I see it all the time... for the past 35 years.