CODEWITHSUNDEEP
sqldatabasesqlitetransactionsrollback
matteof93
matteof93

Reputation: 53

SQLite C interface: what happens if transaction rollback fails?

I have written a lot of APIs for a project which exploits SQLite with the following approach:

sqlite3_exec(mydatabase, "BEGIN;", nullptr, nullptr, nullptr);
/* perform several INSERT, SELECT, UPDATE, DELETE queries on the database */
if(/* problem detected */){
    sqlite3_exec(mydatabase, "ROLLBACK;", nullptr, nullptr, nullptr);
} else {
    sqlite3_exec(mydatabase, "COMMIT;", nullptr, nullptr, nullptr);
}

My question is: what happens if the rollback statement fails? I suppose I could check the return value of the commit statement and rollback in case of error, but I do not know what to do if the rollback fails.

Moreover, I have written my code supposing that I can do something like this:

sqlite3_exec(mydatabase, "BEGIN;", nullptr, nullptr, nullptr);
/* INSERT data in the database */
/* UPDATE or SELECT records in the database where the record could be a record already stored in the db
or a record inserted with the INSERT statement above */
if(/* problem detected */){
    sqlite3_exec(mydatabase, "ROLLBACK;", nullptr, nullptr, nullptr);
} else {
    sqlite3_exec(mydatabase, "COMMIT;", nullptr, nullptr, nullptr);
}

So basically I assume that if I start a transaction, change something in the db and search for the modified row in the db, that change is immediately visible even if the transaction is still running and the commit has not been issued yet. Is this correct?

Thanks

Upvotes: 1

Views: 973

Answers (1)

C Perkins
C Perkins

Reputation: 3886

In context of the same connection (i.e. same process and same connection object), all changes to the database made while inside a transaction are visible to subsequent statements in the same transaction. The entire purpose of a transaction is to accumulate related changes and then to either commit them all at once or reject (i.e. roll back) all the changes together. If this were not the case, the entire purpose of a transaction would be nullified.

If a rollback fails, you should check the result/error codes and handle the case accordingly. Not all fails indicate a critical problem with the database.

A previously committed and/or rolled-back transaction cannot be rolled back again, so it would cause an error. This would indicate some type of logic/flow error, but not necessarily a problem with the database integrity. However, if one part of the code attempts a rollback but the transaction was already committed, the data may not be in valid state according to your specific requirements and business logic. Only you can decide how to handle this type of error. Fix a bug? Ignore the error?

An error code of SQLITE_BUSY means that another connection has the database temporarily locked and so it might also prevent a rollback (not certain of this). A simple delay and re-try could be sufficient to handle this problem.

Other errors could indicate something more serious, like a lost database connection or corrupted journal file, etc. How you hanlde this is completely dependent on your application and how critical the database is to the application. Should the error be escalated and terminate the entire program? Simply logged and ignored? Perhaps your code could further analyze the connection and/or database files and attempt a recovery?

Upvotes: 1

Related Questions