database is locked

[stevenh]

We have a little app which has multiple goroutines all running queries and it often fails with "database is locked" :(

we're running with:

db, err := sql.Open("sqlite3", "test.db?cache=shared&mode=rwc")

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

sqlite3 -version
3.10.2 2016-01-20 15:27:19 17efb4209f97fb4971656086b138599a91a75ff9

Is this a known issue?

[mattn]

did you try _busy_timeout ?

[stevenh]

I wasn't aware of this, so no I hadn't.

I would however has expected busy_timeout to only effect table locks but the error says "database is locked" is the error misleading?

[mrnugget]

Just chiming in here, because I keep running into the same problem. I created a gist that can pretty reliably demonstrate the "database is locked" error. In this program I open 1 goroutine that writes 5000 times to one table (users) and another goroutine that spawns 5000 goroutines that read from another table.

db, err := sql.Open("sqlite3", "database_file.sqlite?cache=shared&mode=rwc") doesn't fix the problem, and adding _busy_timeout makes the program really, really slow.

What helped was using db.SetMaxOpenConns(1). Which is the same as wrapping a mutex around every DB access.

I really want to understand what's going on here and what's possible with sqlite3 in Go and what's not. My guess is that the database is locked error isn't a problem in sqlite (which explicitly allows writing to and reading from different tables at the same time), but rather a combination of sqlite's thread-safety, Go's database/sql connection pooling and go-sqlite3 connection management.

[mattn]

Try to use transaction.

https://gist.github.com/392999fb7472906afec8

[mrnugget]

That still results in "database is locked" errors and I think it's even more than before.

[stevenh]

Yer, I think the issue is with multiple threads and this message is equivalent to MySQL's deadlock detected, at least in our case, just MySQL's message is much clearer as it doesn't have multiple meanings which seems to be the case with sqlite.

As none of the suggestions resolved the issue we've had to move away from sqlite unfortunately.

I'll leave this bug open for others to contribute to, as its clearly wider spread.

[mrnugget]

After some more reading and investigating, I think there is no proper solution where we can use multiple sql.DB connections in combination with sqlite3. Why?

• The thread-safety guarantees sqlite makes only cover the case where you have one sqlite3 *db in your program and access it from different threads. sqlite can then coordinate access to the database in its own functions.
• If we don't limit the amount of sql.DB connections (which is the database/sql default), we create multiple sqlite3 *db instances in our program. These act independently from each other. Now each connection has to do its own synchronization and they have to synchronise with the the other connections. We're now essentially in "multiple processes accessing the same file"-land. The sqlite FAQ describes this:

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business.

(Highlight by me)

The difference between sqlite and client/server databases (PostgreSQL, MySQL, ...) is that sqlite3 synchronizes access to the database file on the client-side, which is only possible if multiple threads/goroutines use the same client. If multiple clients (processes, goroutines with their own connection) use the same database file, the synchronization has to happen through the database file, in which case it's locked completely for every write action.

So I'd say that the only solution is to use db.SetMaxOpenConns(1) (or use the busy timeout).

If anything of what I just said is wrong, please feel free to point it out.

EDIT:

Basically everything I said above is wrong.

Apparently what I said about "thread-safety guarantees" only in "shared db instance across threads" is wrong. I found some sample code that shows usage of multiple db instances across multiple threads. Apparently that is the recommended way to go.

This sqlite wiki entry also shows how sqlite behaves when used in multiple threads with multiple database instances: a lot of SQLITE_BUSY/"database is locked" errors. The conclusion on the wiki page is as follows:

Make sure you're compiling SQLite with -DTHREADSAFE=1.
Make sure that each thread opens the database file and keeps its own sqlite structure.
Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle SQLITE_BUSY appropriately.

Using one connection across multiple goroutines by using db.SetMaxOpenConns(1) and letting database/sql synchronize access to the shared connection with its mutexes might cause problems, since connections in database/sql are stateful, which means that a db.Begin() in one goroutine effects other goroutines.

To update the conclusion at the top of the original version of this comment:

I think there is no proper solution where we can use multiple sql.DB connections in combination with sqlite3 in Go without handling "database is locked" by retrying or by incrementing the busy_timeout. It seems like this is expected behaviour when using sqlite in a multi-threaded context.

[sqweek]

Yer, I think the issue is with multiple threads and this message is equivalent to MySQL's deadlock detected, at least in our case, just MySQL's message is much clearer as it doesn't have multiple meanings which seems to be the case with sqlite.

sqlite's "database is locked" error doesn't have multiple meanings. It means "I tried to obtain a lock on the database but failed because a separate process/connection held a conflicting lock". It's certainly not a deadlock, and is 100% expected in code creating multiple connections to the same DB.

My guess is that the database is locked error isn't a problem in sqlite (which explicitly allows writing to and reading from different tables at the same time),

To be more specific, sqlite's locking protocol allows for many readers and exactly one writer at any given time. ie. if you attempt to start two write transactions concurrently, the second will encounter SQLITE_BUSY.

In rollback-journal mode, a read transaction will also encounter SQLITE_BUSY if it starts just as a write transaction enters its COMMIT phase - as hinted at in previous comments sqlite locks the entire database for the duration of COMMIT. This doesn't happen in WAL mode, because COMMIT doesn't need to update the main database file (just the write ahead journal).

Anyway I think you came to the same conclusion, that SQLITE_BUSY is unavoidable when using multiple connections to the same database. Shared cache mode helps by introducing finer locks (table-level instead of database-level), but still if you have two transactions trying to update the same table at once the second will get SQLITE_BUSY.

Setting the busy timeout is one way to cope with the situation - if your timeout is longer than the longest write transaction performed by your application that will mitigate many of the errors.

But even then, you can still easily get SQLITE_BUSY via a transaction along the lines of:

BEGIN;
SELECT MAX(x) FROM table1;
INSERT INTO table1 VALUES(x+1);
COMMIT;

If there is already a write transaction in progress on table1 when this INSERT statement runs, sqlite will immediately return SQLITE_BUSY, without waiting for the busy timeout.

In rollback-journal mode, this is because sqlite knows it has a read-lock (from the earlier SELECT), so waiting is just going to hold up the existing write transaction.

In WAL mode, this is because the earlier SELECT has the effect of pinning this transaction's view of the database to that point in time. sqlite knows the existing write transaction is going to update database potentially invalidating the SELECT results, and doesn't let you write to the database from this view of the past.

For this conflicting writer case, the proper course of action is for the application to ROLLBACK the transaction, and potentially try again once the other write transaction has completed.

I don't think there is any issue with go-sqlite3 here.

[Klowner]

fwiw, I stumbled across this ticket and subsequently found http://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang

After the addition rows.Close() calls to my code I don't seem to be experiencing the database is locked issue.

[jrots]

Still experiencing this issue atm..
So what is the best solution/ practices here to avoid these "locked issues" ?

• creating a writer connection with : db.SetMaxOpenConns(1) +
• a separate reader connection for selects without SetMaxOpenConns() ?
• avoid using tx.Begin() and Commit() as it will block other connections?

[sqweek]

@jrots sqlite allows multiple readers but only a single writer at any one time. If you have multiple connections to the same DB, you will inevitably run into database is locked. This is an unavoidable consequence of sqlite's locking model, and therefore something your application needs to deal with. Possible solutions include:

1. Only ever open a single connection to the DB (but note this can still result in multiple connections to the DB if a user runs multiple instances of your application at once)
2. Set the busy timeout to something reasonably high (which causes sqlite to retry lock acquisition for a certain amount of time before returning database is locked). In go-sqlite3 this is achieved by appending eg. ?_busy_timeout=5000 to the filename passed to sql.Open, where 5000 is the number of milliseconds to retry.

Also note that tx.Begin() and tx.Commit() are currently affected by issue #184 which can result in a connection getting stuck in an inconsistent state if SQLITE_BUSY is encountered while commit is in progress. But setting an appropriate busy timeout should avoid this situation also (unless you have excessively long lived transactions in which case you might want to rethink your design and/or ensure that you're closing all your transaction/result objects in a timely manner).

[jrots]

@sqweek Thx for the thorough explanation! Are you also using db.SetMaxOpenConns(1) when initialising the connection or just avoid having that setting?

update: ok reread your answer & you probably don't use that, just one initialisation and let it underlying create multiple connections without that setting