This would be a minor change, but I think would be a small quality of life improvement: add an alternate form of ROLLBACK that essentially does ROLLBACK TO SavePointName; RELEASE SavePointName;

I added some code that manages its own savepoint which also has no awareness of what's going on above it transaction-wise. I was temporarily confused when ROLLBACK TO still left me in a transaction when the function had started outside of one. Of course, had I read the docs more carefully, I would have known ROLLBACK TO leaves you in the savepoint. I propose adding ROLLBACK AND RELEASE (or some other phrasing, maybe ROLLBACK BEFORE, ROLLBACK PAST, or something?) which would exit that savepoint for two reasons: it removes one extra statement that needs to be done (by no means a big deal to have to put up with though), and since it would be in the railroad diagram it would likely help new and/or oblivious devs find the correct way to rollback a savepoint entirely.

It's not extremely helpful, but if it's simple enough, maybe it would be worth doing.

There are typically Three types of scenarios when using transactions:

• 1 - You start a transaction, it completes, you commit it.
• 2 - You start a transaction, it fails at some point, you roll it back.
• 3 - You start a transaction, checkpoint as you go with the option to roll back to a previous point in order to redo the last section, and then commit or rollback all if need be.

Note that 3 is only worth doing if:

• The process of building the transaction is processing intensive and takes a lot of time so that redoing everything is tangibly worse than redoing a section.
• You expect (or assign high likelihood to) failure of the transaction, or specific section(s) thereof.

You are asking, unless I am misunderstanding, for a way to roll back to a savepoint, and then also rollback the entire transaction? This is already possible by just doing one single ROLLBACK. No mess, no fuss.

If your suggestion is to roll back to a savepoint and then commit, well that's the worst transaction design in history, so I'm just waving that past as implausible.

If finally your suggestion is to roll back to another savepoint prior to the last one, that's also already possible by naming your savepoints and rolling back to a specific named one.

So I'm not 100% sure what you are suggesting and maybe hoping you just did not know about some of the above. To that end, see sqlite.org/lang_savepoint.html for the full detail.

"If finally your suggestion is to roll back to another savepoint prior to the last one" It was this one.

The reason why rolling back to a specific savepoint doesn't work is I want the function I wrote to clean up after itself automatically. The simplest way to me is to have it start its own savepoint and release it before its done (possibly with a rollback to it prior to the release), rather than having to save the most recent savepoint name and then provide it to the function whenever it's called. The changes to which SQL statements would be done is mostly trivial (1 statement versus 2), but it's more hand-holdy.

You're misunderstanding. If Process 2 decides to rollback, you end up keeping Savepoint B around. Now, after the "Wait, I'm dumb" moment I just had, this is only an issue in an edge case I walked into last night. If the code can happen both in an existing transaction or in one started by the savepoint it creates (depending on where that function happens to get called in the application logic), then it means it either works just fine or, in the latter case, it leaves an open transaction with no changes unless you release the savepoint or rollback entirely. I was kind of fixated on that last bit because I ran into it by mistake, and managed to get myself thinking it was a more general problem.