Locking an SQLite database

Today I will return to the topic of SQLite once again. This time I will discuss why and how to lock an SQLite database for exclusive access to make sure that it's not modified by other processes.

Of course one of the goals of SQLite, like most database engines, is concurrency - i.e. the ability for multiple processes to read and write data to the same database. As we can read in the documentation, "in order to maximize concurrency, SQLite works to minimize the amount of time that EXCLUSIVE locks are held".

That makes perfect sense if we use SQLite with a web application, which should handle as many concurrent requests as possible. But there are some scenarios when we might want to keep the database locked for exclusive access. For example, an SQLite database is often used as a cache for various data. If we run two instances of a program and both use the same cache, it may sometimes lead to strange results. Sometimes it's just easier to prevent such situation than to try to handle it. As you can guess, that's the situation I came across in the WebIssues client.

One way to achieve this is to simply use one large transaction throughout the lifetime of the connection. The documentation advises such solution when the database is used as the application file format. However, sometimes we want all modifications to be committed as soon as possible, for example to prevent losing data on a crash, while still keeping an exclusive lock as long as the connection is open.

Fortunately, SQLite has a useful pragma statement called locking_mode. If we set it to EXCLUSIVE, the obtained locks are never released until the connection is closed. However, the name of this setting is slightly misleading, because it doesn't really obtain an exclusive lock, it just ensures that the lock is never released. So perhaps "persistent" locking mode would be a better name.

In order to lock the database, we need to perform the following sequence of commands:

PRAGMA locking_mode = EXCLUSIVE
BEGIN EXCLUSIVE
COMMIT

The BEGIN EXCLUSIVE command actually locks the database. It begins an empty transaction, which doesn't change anything, but ensures that the exclusive lock is acquired on the database. If the database is already locked by another process, this command will fail with the SQLITE_BUSY error.

Note that the standard Qt driver for SQLite sets the busy timeout to 5 seconds by default, so the application will freeze for a while before reporting an error. We can change it using the QSQLITE_BUSY_TIMEOUT connection option, or when using a custom driver, by simply changing the parameter of sqlite3_busy_timeout to a smaller value.