Bypass SQLite exclusive lock 🔐

TLDR

Chrome database with rollback journal: sqlite3 'file:Web Data?mode=ro&nolock=1'.

Firefox database with WAL: sqlite3 'file:places.sqlite?immutable=1'.

Context

There’s a number of ways SQLite can lock a database file, and if you’re encountering a “database is locked” error, according to the internet, you have two options:

1. If you control the software that created the lock, go and fix the problematic queries.
2. You’re fucked.

By “you’re fucked” I mean that your seemingly only option is to copy the whole database file and query the copy. If working off a one-time snapshot of the database work for you, awesome, problem solved:

$ echo .tables | sqlite3 places.sqlite
Error: database is locked
$ cp places.sqlite places-snapshot.sqlite
$ echo '.tables' | sqlite3 places-snapshot.sqlite
actual_table

But it seems that’s not a good enough solution for many people (including myself) and we’re desperately trying to perform read-only queries on a locked SQLite database.

This is especially useful for Firefox and Chrome SQLite files because both browsers have a bad tendency to keep them permanently locked, preventing us to access the database without closing the browser first.

In my case, I want to poll a specific table, and while technically the database is small enough that it’s not a problem to copy it over and over to query it periodically, I just don’t like this idea and I believe there must be a better way.

So let me tell you the better way.

The better way

SQLite allows passing a file: URI instead of a filename (e.g. file:places.sqlite instead of places.sqlite), which comes with the extra ability to pass query string parameters.

Some of those are aliases for flags you could otherwise set when opening the connexion, for example mode=ro is equivalent to setting SQLITE_OPEN_READONLY and cache=private the same as SQLITE_OPEN_PRIVATECACHE.

But we also have other parameters that have a deeper implementation that would otherwise be inaccessible to the SQLite user (no configuration flags for those). In particular, nolock and immutable.

Rollback journal databases

For SQLite databases using the default rollback journal, as Chrome does, we can get away with using nolock to bypass the lock. From the documentation:

The nolock parameter is a boolean query parameter which if set disables file locking in rollback journal modes. This is useful for accessing a database on a filesystem that does not support locking.

Caution: database corruption might result if two or more processes write to the same database and any one of those processes uses nolock=1.

$ echo .tables | sqlite3 'Web Data'
Error: database is locked
$ echo '.tables' | sqlite3 'file:Web Data?nolock=1'
actual_table

As long as you’re only reading data, you’ll be fine. You can enforce that by using:

sqlite3 'file:Web Data?mode=ro&nolock=1'

WAL databases

For WAL SQLite databases like used in Firefox, the nolock parameter has no effect, which leaves us with immutable. From its documentation:

The immutable parameter is a boolean query parameter that indicates that the database file is stored on read-only media. When immutable is set, SQLite assumes that the database file cannot be changed, and so the database is opened read-only and all locking and change detection is disabled.

Caution: setting the immutable property on a database file that does in fact change can result in incorrect query results and/or SQLITE_CORRUPT errors.

See also: SQLITE_IOCAP_IMMUTABLE.

Even though SQLITE_IOCAP_IMMUTABLE is not an option per se, but a particular characteristic of the IO device, we can force SQLite to treat the database as if was on an read-only device by setting immutable=1, which has the particularity of disabling all locking mechanisms, including that of respecting existing locks.

$ echo .tables | sqlite3 places.sqlite
Error: database is locked
$ echo '.tables' | sqlite3 'file:places.sqlite?immutable=1'
actual_table

This doesn’t require creating a copy of the file that you want to query despite it being locked by another active connection!

The catch is SQLite assumes the file never changes, so it ignores the WAL. You see the last checkpoint in the main file and not necessarily all the latest writes. If a checkpoint runs during the query, you can also get incorrect results and/or SQLITE_CORRUPT as mentioned earlier.

Because of that, I would recommend opening a new connection every time you want to query the database.

Applying it to a SQLite driver

It’s nice to be able to do that with the CLI, but how do we do that from a program that uses a SQLite driver? In my case I’m using sqlite3 with Node.js, but the method should be very similar in your language of choice.

Because the nolock and immutable options are only available in the URI filename format, we need to pass this kind of URI to our driver, e.g. file:places.sqlite?immutable=1 as opposed to places.sqlite.

The URI format is not enabled by default and you need to pass the SQLITE_OPEN_URI flag in order to enable it.

With sqlite3, this looks like this:

const sqlite3 = require('sqlite3')
const db = new sqlite3.Database('file:places.sqlite?immutable=1', sqlite3.OPEN_READONLY | sqlite3.OPEN_URI)

We need to precise OPEN_READONLY because OPEN_URI alone is not a valid mode, and by passing an explicit mode, we’re effectively overriding the default of SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX.

Wrapping up

I hope you enjoyed this trick! If you find a better way to do this (especially for WAL to be able to reflect underlying database updates), please let me know, I’d love to know about it!

And as usual, keep hacking. 😜

Want to leave a comment?

Join the discussion on X or send me an email! 💌
This post helped you? Buy me a coffee! 🍻