Our Database class has startAtomic, endAtomic, and doAtomicSection to run multiple queries that should be logically grouped even if the code is called while inside another transaction or atomic section. This works well most of the time.
But what should you do if something goes wrong in the middle of your atomic section? Roll back the whole transaction, even if the caller could recover from your failure? Don't roll back the transaction, which leaves your "atomic" section half done?
Ideally we'd roll back just the atomic section and then throw an error for the caller to deal with. Most of the time the caller will probably roll back, but for things like upsert or insertSelect it might want to deal with it better.
It turns out that SQL has a mechanism to deal with this sort of thing: savepoints. And it also turns out that all of our supported databases do, in fact, support them.
- https://dev.mysql.com/doc/refman/5.5/en/savepoint.html
- https://www.sqlite.org/lang_savepoint.html
- https://www.postgresql.org/docs/8.1/static/sql-savepoint.html
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql
- https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/savepoint_statement.htm#i36041
We don't want to expose savepoints directly in the public interface, but using them for atomic sections seems reasonable.