go-sqlite3 with journal_mode=WAL gives 'database is locked' error

Question

In go, I open a sqlite3 database using the mattn/go-sqlite3 module. I set the database journalling mode to WAL immediately after opening using a PRAGMA journal_mode=WAL.

However, if I try to open the database from a second process while the first is running, the second cannot open it and instead gets the "database is locked" error. This happens even if I did not perform any transactions.

The connection string I am using is:

"file:mydbfile.db?cache=shared&mode=rwc"

(I intend to answer my own question, since it took a long time to debug)

Answer 1

If you want to enable journal_mode=WAL, you should add it to the connection string:

"file:mydbfile.db?cache=shared&mode=rwc&_journal_mode=WAL"

As part of opening the database, go-sqlite3 will execute PRAGMA statements to set various defaults. One of these defaults is setting the journal_mode=DELETE. However, if another process has the database opened, the mode cannot be changed back to DELETE. Executing this statement fails with "database is locked" and so you will see the open operation fail with that error.

The complete list of connection string parameters is listed at https://github.com/mattn/go-sqlite3