How do I handle sqlite errors?
I have a long app, written in a mix of C and C ++, that stores data in sqlite.
While I am confident that the committed data will remain available (barring mechanical failure) and there will be no uncommitted data, it is not clear to me what I can do with this kind of mean state.
I am doing a large number of inserts into a transaction and then committing it. When an error occurs in a given statement, I can schedule it to try at some point in the future. It looks like some errors might explicitly undo my transaction (which would be undesirable if true).
The big problem is what happens when my team makes a mistake. I am currently going to keep repeating it until it works. I would expect that anything that could cause my commit to fail could also cause the rollback to fail.
What is the recommended error handling mechanism in such a situation?
a source to share
In COMMIT, if you see a SQLITE_BUSY error, you must retry COMMIT. It might work. Better yet, set up a busy handler callback to handle SQLITE_BUSY.
Another reason why a COMMIT might fail is a deferred foreign key violation. If this happens, you can fix the FK violation and then COMMIT. It's hard to see this drone app doing this.
Other mistakes you should probably just give up and ROLLBACK the deal.
If an I / O or OOM error occurs, the current transaction may be rolled back. This is because some I / O or OOM errors leave SQLite unsure of whether its internal data structures match what is actually on disk. If we went further the database could become corrupted.
You can check if a SQLite transaction has been rolled back using the sqlite3_get_autocommit () API.
If an I / O or OOM error occurs during COMMIT, the transaction may still have been committed. This can happen, for example, if the user pulls out the memory card from the camera in the same way as the transaction was committed. Typically, it is impossible to know if data is being transferred to persistent media without reading the db and checking at the application level.
a source to share
Inserting a null value in a null-valued column is unlikely to succeed, no matter how many times you try.
There is no single solution to your problem. For example, if you are violating database restrictions, you must change your details before trying again. If you have no free space on your disk / drives, please stay free and try again.
I think you need to read more about transactions and ACID properties.
a source to share
Databases are explicitly constructed so that data is always either transmitted or not allowed. When data is committed, it isn't randomly lost because it's in persistent storage (well, in a disk file, which is a good approximation). If you are doing manual transaction management (sounds like this) then the database is not collected by COMMIT or ROLLBACK until prompted. Errors in statements can be handled without losing anything already done.
If you are in a state where you can neither COMMIT nor ROLLBACK, you are in deep trouble. If this is due to insufficient memory or disk space (or quota), then you have created the application incorrectly, and you should fix this first.
You might also think in terms of limiting the duration of transactions so that it is less disastrous (in terms of recovery) when you have some glitches.
a source to share
While I disagree with earlier answers that SQLite can most definitely experience transient errors that will allow you to successfully COMMIT just by repeating a few times, they have a good point that this is not the case for all errors. Remember to implement retry restrictions and possibly also check the cause of the failure rather than blindly retrying forever.
a source to share