go-sqlite3: `database is locked` regression

My tool that uses this consistently gets database is locked. I applied the suggested fix from the FAQ and it worked for an older vesion of go-sqlite3 but not for the current version. I bisected it down to e02bbc03819ea6dfc61212b7dbd772bc0809ae7a.

Let me know how I can assist with more details; the sqlite db I’m using is my firefox cookie jar, which is at /home/frew/.mozilla/firefox/qxp4lwy0.default/cookies.sqlite on my laptop. If you want to test it you can check out https://github.com/frioux/leatherman and run a command like this:

go build && echo "https://github.com/frioux/leatherman/commit/17ba99b79bfd741fd8aac2a4cd534aad087c83fd" | ./leatherman expand-url

About this issue

  • Original URL
  • State: open
  • Created 6 years ago
  • Reactions: 2
  • Comments: 44 (3 by maintainers)

Commits related to this issue

Most upvoted comments

I had the same issue after updating to current version. I found the solution that works for me so maybe it will help in your case.

I had sqlite file in WAL journal mode. But new driver couldn’t connect to it, until i changed my connection strings in all my projects, so it contains “&_journal_mode=WAL” in dsn.

Setting “?cache=shared&mode=ro&_busy_timeout=9999999” alone, and adding db.SetMaxOpenConns(1) was insufficient.

There is no issue using SQLite concurrently, provided that you understand certain interactions between database/sql and SQLite itself.

One of the ways that people get the “database is locked” errors is they attempt to modify the database in the middle of a rows.Next() loop. When you do that, database/sql tries to use a second database connection, since the first one is still “in use”. But under journal mode (the default), a second database connection cannot modify the database while the first connection has a read lock. So the second connection hits its busy timeout and returns that error. To resolve this, you need to make both the reads and writes part of the same transaction.

Another way that error can happen is if you attempt to read and then write within a single transaction (in either WAL mode or journal mode). If another connection modifies the database between the read and the write, you will get that error because SQLite can no longer guarantee the consistency of the data your transaction has been working with. To resolve this, you need to use IMMEDIATE transactions.

You should never use shared cache mode, as doing so just leads to further issues.