`database is locked` error when hiedb is run concurrently

[9999years]

When I run hiedb index .hiefiles concurrently, the second process errors because the first has already locked the database:

$ hiedb index .hiefiles >/dev/null 2>&1 &; hiedb index .hiefiles
hiedb: SQLite3 returned ErrorBusy while attempting to perform step: database is locked

Context

We're using ghciwatch for development in combination with static-ls for language intelligence. ghciwatch handles loading and compiling modules in ghci for rapid reloads, and static-ls leverages hiedb to provide language intelligence. (Our project is too big for haskell-language-server.)

We're using ghciwatch's lifecycle hooks to reindex the hiefiles on reloads:

ghciwatch \
  --after-reload-shell "async:hiedb -D .hiedb index .hiefiles" \
  --before-restart-shell "async:hiedb -D .hiedb index .hiefiles"

Because ghciwatch runs these commands asynchronously (so that it can keep reloading and responding to changes while the files are indexed), it's possible for multiple hiedb processes to run at once (especially on the first load when 7500+ modules are being indexed), which has been triggering this bug.

I have a few ideas for working around this in ghciwatch (only allowing one of each hook to run at once, killing old hook processes before launching new ones, etc.), but I think we should fix this in hiedb as well.

[wz1000]

I don't think it is possible for a sqlite database to be written to concurrently, even with the WAL and WAL2 modes. In particular, the COMMIT steps require exclusive access to the database.

https://sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

[9999years]

That's correct. I don't want concurrent writes to the database, I want hiedb to wait for the database lock to be released instead of exiting with an error.

Currently the behavior is this:

1. hiedb attempts to open the sqlite databse
2. hiedb sees the database is already locked and exits with an error

The behavior I want is this:

1. hiedb attempts to open the sqlite database
2. hiedb sees the database is already locked
3. hiedb outputs a warning or notice to the user that the database is locked
4. hiedb waits, periodically checking if the database can be opened, until the lock is released and hiedb can continue

[wz1000]

I'm not sure hiedb is the right place for this functionality, because different clients might expect different behaviour from hiedb depending on their own needs and usage pattern, like whether to retry, how many times to retry, the interval between retries etc. Instead of making this configurable in hiedb, a client can implement this functionality for their own use case.

For example, HLS implements retrying functionality here: https://github.com/haskell/haskell-language-server/blob/66cf40033cf6d1391622ee4350696656ea0c39d9/ghcide/session-loader/Development/IDE/Session.hs#L361