Table of Contents for

Using SQLite

Before we get into when things go wrong, let’s take a quick look at when things go right. Generally, any API call that simply needs to indicate, “that worked,” will return the constant SQLITE_OK. Not all non-SQLITE_OK return codes are errors, however. Recall that sqlite3_step() returns SQLITE_ROW or SQLITE_DONE to indicate specific return state.

Table 7-2 provides a quick overview of the standard error codes. At this point in the development life cycle, it is unlikely that additional standard error codes will be added. Additional extended error codes may be added at any time, however.

Many of these errors are fairly specific. For example, SQLITE_RANGE will only be returned by one of the sqlite3_bind_xxx() functions. Other codes, like SQLITE_ERROR, provide almost no information about what went wrong.

Of specific interest to the developer is SQLITE_MISUSE. This indicates an attempt to use a data structure or API call in an incorrect or otherwise invalid way. For example, trying to bind a new value to a prepared statement that is in the middle of an sqlite3_step() sequence would result in a misuse error. Occasionally, you’ll get an SQLITE_MISUSE that results from failing to properly deal with a previous error, but many times it is a good indication that there is some more basic conceptual misunderstanding about how the library is designed to work.

The extended codes were added later in the SQLite development cycle. They provide more specific details on the cause of an error. However, because they can change the value returned by a specific error condition, they are turned off by default. You need to explicitly enable them for the older API calls, indicating to the SQLite library that you’re aware of the extended error codes and willing to accept them.

All of the standard error codes fit into the least-significant byte of the integer value that is returned by most API calls. The extended codes are all based off one of the standard error codes, but provide additional information in the higher-order bytes. In this way, the extended codes can provide more specific details about the cause of the error. Currently, most of the extended error codes provide specific details for the SQLITE_IOERR result. You can find a full list of the extended error codes at http://sqlite.org/c3ref/c_ioerr_access.html.

The following APIs are used to enable the extended error codes and extract more information about any current error conditions.

It is acceptable to leave extended error codes off and intermix calls to sqlite3_errcode() and sqlite3_extended_errcode().

Because the error state is stored in the database connection, it is easy to end up with race conditionals in a threaded application. If you’re sharing a database connection across threads, it is best to wrap your core API call and error-checking code in a critical section. You can grab the database connection’s mutex lock with sqlite3_db_mutex(). See sqlite3_db_mutex() in Appendix G for more details.

Similarly, the error handling system can’t deal with multiple errors. If there is an error that goes unchecked, the next call to a core API function is likely to return SQLITE_MISUSE, indicating the attempt to use an invalid data structure. In this and similar situations where multiple errors have been encountered, the state of the error message can become inconsistent. You need to check and handle any errors after each API call.

In addition to the standard and extended codes, the newer _v2 versions of sqlite3_prepare_xxx() change the way prepared statement errors are processed. Although the newer and original versions of sqlite3_prepare_xxx() share the same parameters, the sqlite3_stmt returned by the _v2 versions is slightly different.

The most noticeable difference is in how errors from sqlite3_step() are handled. For statements prepared with the original version of sqlite3_prepare_xxx(), the majority of errors within sqlite3_step() will return the rather generic SQLITE_ERROR. To find out the specifics of the situation, you had to call sqlite3_reset() or sqlite3_finalize() to extract a more detailed error code. This would, of course, cause the statement to be reset or finalized, which limited your recovery options.

Things work a bit differently if the statement was prepared with the _v2 version. In that case, sqlite3_step() will return the specific error directly. The call sqlite3_step() may return a standard code or an extended code, depending if extended codes are enabled or not. This allows the developer to extract the error directly, and provides for more recovery options.

The other major difference is how database schema changes are handled. If any Data Definition Language command (such as DROP TABLE) is issued, there is a chance the prepared statement is no longer valid. For example, the prepared statement may refer to a table or index that is no longer there. The only way to resolve any possible problems is to reprepare the statement.

The _v2 versions of sqlite3_prepare_xxx() make a copy of the SQL statement used to prepare a statement. (This SQL can be extracted. See sqlite3_sql() in Appendix G for more details.) By keeping an internal copy of the SQL, a statement is able to reprepare itself if the database schema changes. This is done automatically any time SQLite detects the need to rebuild the statement.

The statements created with the original version of prepare didn’t save a copy of the SQL command, so they were unable to recover themselves. As a result, any time the schema changed, an API call involving any previously prepared statement would return SQLITE_SCHEMA. The program would then have to reprepare the statement using the original SQL and try again. If the schema change was significant enough that the SQL was no longer valid, sqlite3_prepare_xxx() would return an appropriate error when the program attempted to reprepare the SQL command.

Statements created with the _v2 version of prepare can still return SQLITE_SCHEMA. If a schema change is detected and the statement is unable to automatically reprepare itself, it will still return SQLITE_SCHEMA. However, under the _v2 prepare, this is now considered a fatal error, as there is no way to recover the statement.

Here is a side-by-side comparison of the major differences between the original and _v2 version of prepare:

Because the _v2 error handling is a lot simpler, and because of the ability to automatically recover from schema changes, it is strongly recommended that all new development use the _v2 versions of sqlite3_prepare_xxx().

Transactions and checkpoints add a unique twist to the error recovery process. Normally, SQLite operates in autocommit mode. In this mode, SQLite automatically wraps each SQL command in its own transaction. In terms of the API, that’s the time from when sqlite3_step() is first called until SQLITE_DONE is returned by sqlite3_step() (or when sqlite3_reset() or sqlite3_finalize() is called).

If each statement is wrapped up in its own transaction, error recovery is reasonably straightforward. Any time SQLite finds itself in an error state, it can simply roll back the current transaction, effectively canceling the current SQL command and putting the database back into the state it was in prior to the command starting.

Once a BEGIN TRANSACTION command is executed, SQLite is no longer in autocommit mode. A transaction is opened and held open until the END TRANSACTION or COMMIT TRANSACTION command is given. This allows multiple commands to be wrapped up in the same transaction. While this is useful to group together a series of discrete commands into an atomic change, it also limits the options SQLite has for error recovery.

When an error is encountered during an explicit transaction, SQLite attempts to save the work and undo just the current statement. Unfortunately, this is not always possible. If things go seriously wrong, SQLite will sometimes have no choice but to roll back the current transaction.

The errors most likely to result in a rollback are SQLITE_FULL (database or disk full), SQLITE_IOERR (disk I/O error or locked file), SQLITE_BUSY (database locked), SQLITE_NOMEM (out of memory), and SQLITE_INTERRUPT (interrupt requested by application). If you’re processing an explicit transaction and receive one of these errors, you need to deal with the possibility that the transaction was rolled back.

To figure out which action was taken by SQLite, you can use the sqlite3_get_autocommit() function.

If SQLite was forced to do a full rollback, the database will once again be in autocommit mode. If the database is not in autocommit mode, it must still be in a transaction, indicating that a rollback was not required.

Although there are situations when it is possible to recover and continue a transaction, it is considered a best practice to always issue a ROLLBACK if one of these errors is encountered. In situations when SQLite was already forced to roll back the transaction and has returned to autocommit mode, the ROLLBACK will do nothing but return an error that can be safely ignored.

SQLite employs a number of different locks to protect the database from race conditions. These locks allow multiple database connections (possibly from different processes) to access the same database file simultaneously without fear of corruption. The locking system is used for both autocommit transactions (single statements) as well as explicit transactions.

The locking system involves several different tiers of locks that are used to reduce contention and avoid deadlocking. The details are somewhat complex, but the system allows multiple connections to read a database file in parallel, but any write operation requires full, exclusive access to the entire database file. If you want the full details, see http://www.sqlite.org/lockingv3.html.

Most of the time the locking system works reasonably well, allowing applications to easily and safely share the database file. If coded properly, most write operations only last a fraction of a second. The library is able to get in, make the required modifications, verify them, and then get out, quickly releasing any locks and making the database available to other connections.

However, if more than one connection is trying to access the same database at the same time, sooner or later they’ll bump into each other. Normally, if an operation requires a lock that the database connection is unable to acquire, SQLite will return the error SQLITE_BUSY or, in some more extreme cases, SQLITE_IOERR (extended code SQLITE_IOERR_BLOCKED). The functions sqlite3_prepare_xxx(), sqlite3_step(), sqlite3_reset(), and sqlite3_finalize() can all return SQLITE_BUSY. The functions sqlite3_backup_step() and sqlite3_blob_open() can also return SQLITE_BUSY, as these functions use sqlite3_prepare_xxx() and sqlite3_step() internally. Finally, sqlite3_close() may return SQLITE_BUSY if there are unfinalized statements associated with the database connection, but that’s not related to locking.

Gaining access to a needed lock is often a simple matter of waiting until the current holder finishes up and releases the lock. In most cases, this is not a particularly long period of time. The waiting can either be done by the application, which can respond to an SQLITE_BUSY by simply trying to reprocess the statement and trying again, or it can be done with a busy handler.