Bypass SQLite exclusive lock 🔐
“Error: database is locked” is not an acceptable answer
October 14, 2021
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 db.sqlite Error: database is locked cp db.sqlite db-snapshot.sqlite echo '.tables' | sqlite3 db-snapshot.sqlite actual_table
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.
Some of those are aliases for flags you could otherwise set when opening
the connexion, for example
is equivalent to setting
the same as
nolock only prevents this connection from locking the database
and doesn’t do anything about the fact a lock is already being held by
another connection, the
immutable is especially interesting for us.
From its documentation:
The immutable parameter is a boolean query parameter that indicates that the database file is stored on read-only media. When
immutableis 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_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
which has the particularity of disabling all locking mechanisms,
including that of respecting existing locks.
With this trick, we can rewrite the previous fix:
echo .tables | sqlite3 db.sqlite Error: database is locked echo '.tables' | sqlite3 'file:db.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 only caveat is because SQLite doesn’t expect that file to be updated, changes wont be reflected in that immutable connexion, so it’s still like you’re querying a snapshot, it’s just that you don’t have to physically copy the database in order to read it.
Also as mentioned earlier, if the underlying database is updated, this might result in errors when querying over the immutable connection. Because of that, I would recommending opening a new connection every time you want to query the database.
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.
immutable option is only available in the URI filename
format, we need to pass this kind of URI to our driver, e.g.
file:db.sqlite?immutable=1 as opposed to
The URI format is not enabled by default and you need to pass the
flag in order to enable it.
sqlite3, this looks like this:
const sqlite3 = require('sqlite3') const db = new sqlite3.Database('file:db.sqlite?immutable=1', sqlite3.OPEN_READONLY | sqlite3.OPEN_URI)
We need to precise
OPEN_URI alone is not a
valid mode, and by passing an explicit mode, we’re effectively
overriding the default
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX.
I hope you enjoyed this trick! If you find a better way to do this, or a way that allows to reflect underlying database updates without reloading the connection, please let me know, I’d love to know about it!
And as usual, keep hacking. 😜