You may have noticed that I’ve been fairly quiet about the result codes that can be expected from a number of these API calls. Unfortunately, error handling in SQLite is a bit complex. At some point, it was recognized that the original error reporting mechanism was a bit too generic and somewhat difficult to use. To address these concerns, a newer “extended” set of error codes was added, but this new system had to be layered on top of the existing system without breaking backward compatibility. As a result, we have both the older and newer error codes, as well as specific API calls that will alter the meaning of some of the codes. This all makes for a somewhat complex situation.
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.
Table 7-2. SQLite standard return codes
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.
int sqlite3_extended_result_codes( sqlite3 *db, int onoff )
Turns extended result and error codes on or off for
this database connection. Database connections
returned by any version of sqlite3_open_xxx() will have extended
codes off by default. You can turn them on by
passing a nonzero value in the second parameter.
This function always returns SQLITE_OK—there is no
way to extract the current result code state.
int sqlite3_errcode( sqlite3 *db )
If a database operation returns a non-SQLITE_OK status, a
subsequent call to this function will return the
error code. By default, this will only return a
standard error code, but if extended result codes
have been enabled, it may also return one of the
extended codes.
int sqlite3_extended_errcode( sqlite3 *db )
Essentially the same as sqlite3_errcode(), except that extended
results are
always
returned.
const char* sqlite3_errmsg( sqlite3 *db )
const void* sqlite3_errmsg16( sqlite3 *db )
Returns a null-terminated, human-readable, English language error string that is encoded in UTF-8 or UTF-16. Any additional calls to the SQLite APIs using this database connection may result in these pointers becoming invalid, so you should either use the string before attempting any other operations, or you should make a private copy. It is also possible for these functions to return a NULL pointer, so check the result value before using it. Extended error codes are not used.
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:
| Statement prepared with original version | Statement prepared with v2 version |
|---|---|
Created with sqlite3_prepare() or sqlite3_prepare16(). | Created with sqlite3_prepare_v2() or sqlite3_prepare16_v2(). |
Most errors in sqlite3_step() return SQLITE_ERROR. | sqlite3_step() returns specific errors
directly. |
sqlite3_reset() or sqlite3_finalize() must
be called to get full error. Standard or extended
error codes may be returned. | No need to call anything additional. sqlite3_step() may
return a standard or extended error code. |
Schema changes will make any statement
function return SQLITE_SCHEMA. Application must
manually finalize and reprepare statement. | Schema changes will make the statement reprepare itself. |
| If application-provided SQL is no longer valid, the prepare will fail. | If internal SQL is no longer valid, any
statement function will return SQLITE_SCHEMA. This is a
statement-fatal error, and the only choice is to
finalize the statement. |
| Original SQL is not associated with statement. | Statement keeps a copy of SQL used to
prepare. SQL can be recovered with sqlite3_sql(). |
| Limited debugging. | sqlite3_trace() can be used. |
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.
A busy handler is a callback function that is called by the SQLite
library any time it is unable to acquire a lock, but has
determined it is safe to try and wait for it. The busy handler
can instruct SQLite to keep trying to acquire the lock, or to
give up and return an SQLITE_BUSY error.
SQLite includes an internal busy handler that uses a timer. If you set a timeout period, SQLite will keep trying to acquire the locks it requires until the timer expires.
int sqlite3_busy_timeout( sqlite3 *db, int millisec )
Sets the given database connection to use the internal timer-based busy handler. If the second parameter is greater than zero, the handler is set to use a timeout value provided in milliseconds (thousandths of a second). If the second parameter is zero or negative, any busy handler will be cleared.
If you want to write your own busy handler, you can set the callback function directly:
int sqlite3_busy_handler( sqlite3 *db, callback_func_ptr, void *udp )
Sets a busy handler for the given database. The second parameter is a function pointer to the busy handler, and the third parameter is a user-data pointer that is passed to the callback. Setting a NULL function pointer will remove the busy handler.
int user_defined_busy_handler_callback( void *udp, int incr )
This is not an SQLite
library call, but the format of a user-defined
busy handler. The first parameter is the user-data
pointer passed to sqlite3_busy_handler() when the
callback was set. The second parameter is a
counter that is incremented each time the busy
handler is called while waiting for a specific
lock.
A return value of zero
will cause SQLite to give up and return an
SQLITE_BUSY
error, while a nonzero return value will cause
SQLite to keep trying to acquire the lock. If the
lock is successfully acquired, command processing
will continue. If the lock is not acquired, the
busy handler will be called again.
Be aware that each database connection has only one busy handler. You cannot set an application busy handler and configure a busy timeout value at the same time. Any call to either of these functions will cancel out the other one.
Setting a busy handler will not fix every problem. There are some situations when waiting for a lock will cause the database connection to deadlock. The deadlock happens when a pair of database connections each have some set of locks and both need to acquire additional locks to finish their task. If each connection is attempting to access a lock currently held by the other connection, both connections will stall in a deadlock. This can happen if two database connections both attempt to write to the database at the same time. In this case, there is no point in both database connections waiting for the locks to be released, since the only way to proceed is if one of the connections gives up and releases all of its locks.
If SQLite detects a potential deadlock
situation, it will skip the busy handler and will have one of
the database connections return SQLITE_BUSY immediately. This is done to
encourage the applications to release their locks and break the
deadlock. Breaking the deadlock is the responsibility of the
application(s) involved—SQLite cannot handle this situation for
you.
When developing code for a system that
requires any degree of database concurrency, the easiest
approach is to use sqlite3_busy_timeout() to set a timeout value
that is reasonable for your application. Start with something
between 250 to 2,000 milliseconds and adjust from there. This
will help reduce the number of SQLITE_BUSY response codes, but it will not
eliminate them.
The only way to completely avoid SQLITE_BUSY is to ensure a database never has more than one database
connection. This can be done by setting PRAGMA locking_mode
to EXCLUSIVE.
If this is unacceptable, an application can use transactions to
make an SQLITE_BUSY return
code easier to deal with. If an application can successfully
start a transaction with
BEGIN EXCLUSIVE TRANSACTION,
this will eliminate the possibility of getting an
SQLITE_BUSY. The
BEGIN itself may return
an SQLITE_BUSY, but in this
case the application can simply reset the BEGIN statement with sqlite3_reset() and try again.
The disadvantage of BEGIN
EXCLUSIVE is that it can only be started when no
other connection is accessing the database, including any
read-only transactions. Once an exclusive transaction is
started, it also locks out all other connections from accessing
the database, including read-only transactions.
To allow more concurrency, an application can use
BEGIN IMMEDIATE TRANSACTION.
If an IMMEDIATE transaction
is successfully started, the application is very unlikely to
receive an SQLITE_BUSY until
the COMMIT statement is
executed. In all cases (including the COMMIT), if an SQLITE_BUSY is encountered, the application can
reset the statement, wait, and try again. As with BEGIN EXCLUSIVE, the BEGIN IMMEDIATE statement itself
can return SQLITE_BUSY, but
the application can simply reset the BEGIN statement and try again. A BEGIN IMMEDIATE transaction can
be started while other connections are reading from the
database. Once started, no new writers will be allowed, but
read-only connections can continue to access the database up
until the point that the immediate transaction is forced to
modify the database file. This is normally when the transaction
is committed. If all database connections use BEGIN IMMEDIATE for all
transactions that modify the database, then a deadlock is not
possible and all SQLITE_BUSY
errors (for both the IMMEDIATE writers and other readers) can be
handled with a retry.
Finally, if an application is able to successfully begin a
transaction of any kind (including the default,
DEFERRED), it should never
get an SQLITE_BUSY (or risk a
deadlock) unless it attempts to modify the database. The
BEGIN itself may return
an SQLITE_BUSY, but the
application can reset the BEGIN statement and try again.
Attempts to modify the database within a BEGIN DEFERRED transaction (or
within an autocommit) are the only situations when the database
may deadlock, and are the only situations when the response to
an SQLITE_BUSY needs to go
beyond simply waiting and trying again (or beyond letting the
busy handler deal with it). If an application performs
modifications within a deferred transaction, it needs to be
prepared to deal with a possible deadlock situation.
The rules to avoid deadlocks are fairly simple, although their application can cause significant complexity in code.
First, the easy ones. The functions sqlite3_prepare_xxx(), sqlite3_backup_step(), and sqlite3_blob_open() cannot cause
a deadlock. If an SQLITE_BUSY
code is returned from one of these functions at any time, simply
wait and call the function again.
If the function sqlite3_step(), sqlite3_reset(), or sqlite3_finalize() returns SQLITE_BUSY from within a
deferred transaction, the application must back off and try
again. For statements that are not part of an explicit
transaction, the prepared statement can simply be reset and
re-executed. For statements that are inside an explicit deferred
transaction, the whole transaction must be rolled back and
started over from the beginning. In most cases, this will happen on
the first attempt to modify the database. Just remember that the
whole reason for the rollback is that some other database
connection needs to modify the database. If an application has
done several read operations to prepare a write operation, it
would be best to reread that information in a new transaction to
confirm the data is still valid.
Whatever you do, don’t ignore SQLITE_BUSY errors. They can be
rare, but they can also be a source of great frustration if
handled improperly.
When a database connection needs to modify the database, a lock is placed that makes the database read-only. This allows other connections to continue to read the database, but prevents them from making modifications. The actual changes are held in the database page cache and not yet written to the database file. Writing the changes out would make the changes visible to the other database connections, breaking the isolation rule of transactions. Since the changes have not yet been committed, it is perfectly safe to have them cached in memory.
When all the necessary modifications have been made and the transaction is ready to commit, the writer further locks the database file so that new read-only transactions cannot get started. This allows the existing readers to finish up and release their own database locks. When all readers are finished, the writer should have exclusive access to the database and may finally flush the changes out of the page cache and into the database file.
This process allows read-only transactions to continue running while the write transaction is in progress. The readers need to be locked out only when the writer actually commits its transaction. However, a key assumption in this process is that the changes fit into the database page cache and do not need to be written until the transaction is committed. If the cache fills up with pages that contain pending changes, a writer has no option but to put an exclusive lock on the database and flush the cache prior to the commit stage. The transaction can still be rolled back at any point, but the writer must be given immediate access to the exclusive write lock in order to perform the cache flush.
If this lock is not immediately available, the writer is forced
to abort the entire transaction. The write transaction will be
rolled back and the extended result code SQLITE_IOERR_BLOCKED (standard
code
SQLITE_IOERR) will be
returned. Because the transaction is automatically rolled back,
there aren’t many options for the application, other than to
start the transaction over.
To avoid this situation, it is best to start large transactions
that modify many rows with an explicit BEGIN EXCLUSIVE. This call may fail with
SQLITE_BUSY, but the
application can simply retry the command until it succeeds. Once
an exclusive transaction has started, the write transaction will
have full access to the database, eliminating the chance of an
SQLITE_IOERR_BLOCKED,
even if the transaction spills out of the cache prior to the
commit. Increasing the size of the database cache can also help.