I have a long running application written in a mix of C and C++ that stores data in sqlite.

While I am confident that committed data will remain available (barring mechanical failure) and uncommitted data will not be, it's not clear to me what I can do with this sort of middle state.

I do a large number of inserts in a transaction and then commit it. When an error occurs on a given statement, I can schedule it to be attempted at some point in the future. It sounds like some errors might implicitly rollback my transaction (which would be undesirable if true).

A larger problem is what happens when my commit itself fails. Currently, I'm just going to continue to retry it until it works. I would expect that whatever would cause my commit to fail may very well also cause a rollback to fail.

What is the recommended mechanism for error handling in such a situation?