panic: "database table is locked" when multiple writes occur concurrently
spiffytech opened this issue · 7 comments
I encountered the problem from Issue 39 and applied the resolution of adding "?cache=shared&mode=rwc" to my open string. Now, when multiple writers run concurrently, I encounter a different error:
panic: database table is locked: <table name>
Some research indicate SQLite has an API for waiting for the locking query to finish so the next query can be run. Please implement this, so that manually managing concurrent writes, or write retries, is unnecessary.
I'm getting some "database table is locked" errors as well when doing some concurrent reading & writing.
Using shared-cache mode does not seem to help. However, setting the database to use Write-Ahead Logging (requires sqlite >= 3.7.0, see https://www.sqlite.org/pragma.html#pragma_journal_mode) does seem to fix the problem for me.
@spiffytech What version of sqlite do you use?
$ sqlite3 --version
3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc
I think I got that from MacPorts.
I tried enabling WAL mode and continued to get the error.
I am using the following connection string file:locked.sqlitecache=shared&mode=rwc&_busy_timeout=50000000 and creating a new connection for every method call, and setting WAL to true to try and avoid contention but I'm getting constant table is locked errors. This is especially true if the table is :memory:. Is there an easy way for me to set FULL_MUTEX?
I think the first aspect to clear would be if go-sqlite3 itself is goroutine-safe, e.g. if a single instance can be called from multiple goroutines.
Although related, such aspect is separate from the internal SQLite3 locking mechanism (which of course is a nice feature to use) that can work across different processes.