How should I deal with sqlite errors?

Question

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?

Answer 1

Inserting null value in a not nullable column is quite unlikely to succeed, no matter how many times you try.

There is no single solution to your problem. For instance if you are violating database constraints you should change your data before making another attempt. If you have no memory/disk space left - free some and retry.

I guess you need to learn more about transactions and ACID properties.

Answer 2

Databases are designed explicitly so that data is always either committed or uncommitted. When data is committed, it doesn't get lost arbitrarily because it is on permanent storage (well, in a disk file, which is a good approximation). If you're doing manual transaction management (sounds like it) then the database isn't going to either COMMIT or ROLLBACK until asked to. Errors in statements can be dealt with without losing anything already done.

If you're in a state where you can't either COMMIT or ROLLBACK, then you're in deep trouble. If it's because you're short of memory or disk space (or quota) then you have provisioned the application wrong and you should fix that first.

You also might want to think in terms of limiting the length of transactions so that it is less catastrophic (in terms of work to recover) when you have some failure.

Answer 3

Although I disagree with earlier answers in that SQLite most definitely can experience transient errors which will allow you to successfully COMMIT just by retrying a few times, they do have a good point about this not applying to all errors. Don't forget to implement retry limits and perhaps also check the cause of the failure instead of blindly retrying forever.

Answer 4

On COMMIT, if you see an SQLITE_BUSY error, you should reattempt the COMMIT. It might work. Better yet, install a busy-handler callback to handle SQLITE_BUSY.

Another reason a COMMIT might fail is a deferred foreign key violation. If this happens, you might fix the FK violation and then COMMIT. Hard to see an unmanned application doing this though.

Other errors you should probably just give up and ROLLBACK the transaction.

If an IO or OOM error occurs, the current transaction might be rolled back. This is because some IO or OOM errors leave SQLite unsure as to whether or not its internal data structures match what is actually on disk. If we were to proceed at this point the database might become corrupt.

You can test whether or not a transaction has been rolled back by SQLite using the sqlite3_get_autocommit() API.

If an IO or OOM error occurs during COMMIT, the transaction may still have been committed. This could happen, for example, if the user happens to rip the memory card out of a camera just as the transaction is committed. It is not generally possible to know if the data made it to the persistent media without reading the db and checking at the application level.