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
- Use my fork of go-sqlite3 due to outstanding bug https://github.com/mattn/go-sqlite3/issues/607 — committed to frioux/leatherman by deleted user 6 years ago
- fix: journal mode Journal Mode should only be enforced when the user set it. Fixes: #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- Fixes: #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- Fix test Fixes: #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- Fix: tests for JournalModeAuto Fixes #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- Fix test Fixes: #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- fix: journal mode Journal Mode should only be enforced when the user set it. Fixes: #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- fix: journal mode Journal Mode should only be enforced when the user set it. Fixes: #607 — committed to gjrtimmer/go-sqlite3 by gjrtimmer 6 years ago
- sqlite3: Set options in DSN PRAGMA locking_mode is not effective due to database/sql design (connection pooling). PRAGMA journal_mode=WAL seems to lock database indefinitely. Looks like go-sqlite3 i... — committed to foxcpp/go-imap-sql by foxcpp 5 years ago
- Fix Open() journal mode regression [why] see https://github.com/mattn/go-sqlite3/issues/607 SQLite default journal mode is DELETE, but forcing it on open causes "database is locked" if other connect... — committed to azavorotnii/go-sqlite3 by azavorotnii 5 years ago
- Fix Open() journal mode regression [why] see https://github.com/mattn/go-sqlite3/issues/607 SQLite default journal mode is DELETE, but forcing it on open causes "database is locked" if other connect... — committed to azavorotnii/go-sqlite3 by azavorotnii 5 years ago
- fix(sqlobjectstore): Note(geoah): Sqlite is a bit iffy when trying to write while something is reading, ie using rows.Next and results in db lock errors. For this reason a mutex for each table has be... — committed to nimona/go-nimona by geoah 2 years ago
- fix(sqlobjectstore): Note(geoah): Sqlite is a bit iffy when trying to write while something is reading, ie using rows.Next and results in db lock errors. For this reason a mutex for each table has be... — committed to nimona/go-nimona by geoah 2 years ago
- sqlite3: Set options in DSN PRAGMA locking_mode is not effective due to database/sql design (connection pooling). PRAGMA journal_mode=WAL seems to lock database indefinitely. Looks like go-sqlite3 i... — committed to AppleSeed95/sql-imap-v2 by AppleSeed95 5 years ago
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.