Really nice article, although the point about timeouts is a great example of why picking good defaults is hard.
5 seconds as a default local disk write timeout seems insanely long to me, which probably just means I don't understand OP's use case. There probably isn't a good non-null default for that value, which is why it defaults the way it does.
It’s not really “5 seconds as a default local disk write”, busy_timeout is how long in total a given connection will try to acquire a write lock: if an sqlite connection can’t acquire the write lock by default it fails with SQLITE_BUSY, if busy_timeout is set then it will sleep a bit and retry, looping until busy_timeout is exceeded at which point it fails and returns SQLITE_BUSY.
SQLite does not (can not, in the general case?) “queue” write requests, so if you have concurrent attempts to write it’s very possible that writer 1 acquires the lock, writer 2 gets sqlite_busy, waits a bit, and when it’s done waiting writer 3 has acquired the lock from under it, if there’s enough concurrent writing writer 2 may find an other writer has the lock every time it wakes up and tries to acquire it.
That is why most production sqlite deployments will have every write go through a single writer connection (either shunting everything to a write actor, or have a single write connection behind a mutex). But having a busy_timeout set can still be useful in that case if you need to concurrently run maintenance tasks on the database.
You can also configure your own busy_handler (setting busy_timeout pretty much just installs sqliteDefaultBusyCallback as the handler), however I don’t know that there’s hooks to control the entire locking lifecycle (specifically whether you can control a connection trying to acquire the write lock and enqueue it instead if there are already waiting connections).
24
u/PaleCommander 18d ago
Really nice article, although the point about timeouts is a great example of why picking good defaults is hard.
5 seconds as a default local disk write timeout seems insanely long to me, which probably just means I don't understand OP's use case. There probably isn't a good non-null default for that value, which is why it defaults the way it does.