Help for sql error "database is locked"

[pantaluna]

Hi,

sqlite3.Error{Code:5, ExtendedCode:5, err:"database is locked"}

I have written an HTTP API server in Go that uses SQLite3 and your great package to implement data persistence.

The daemon works fine on the dev machine but once it is deployed on a test server and put under a slightly heavier load (100 API requests / second via HTTP) then I noticed that the API server throws SQL error . The sql errors occur randomly and in less then 0.2% of the transactional statements.

Some stats of a stress test:

Date            #Completed      #Errors     Ratio%
21:00           384215          628         0.163%
22:00           525816          744         0.141%
23:00           687652          812         0.118%

I have narrowed the problem down to the SQLite3 database (and maybe how I use it) when doing Insert, Update and Delete (Select works fine if no program is doing transactions and is blazingly fast).

The sql error starts popping up as soon as the execution of an sql statement has to wait more than 5 seconds.

=> So I have made a small test program to make it easier for you to reproduce it.
https://gist.github.com/pantaluna/89ffe94ec30fb28d9fb8

# Script
go get
go run databaselocked.go --addtestdata
go run databaselocked.go --updates
go run databaselocked.go --updates > updates.log 2>&1
grep "ERROR" updates.log

I noticed that the 5 second timeout is hardcoded in the go-sqlite3 package. I do not see increasing the timeout setting in the go-sqlite3 package as a real solution, nor throttling down the number of active sql connections to 1 (that is possible in database/sql).

I think the whole database is locked when an sql client executes a (simple) transactional statement; even if the Updates occur on different tables, or on the same table but different rows (the latter is implemented in the test program).

Can you please advice me on how to use the go-sqlite3 package correctly, and avoid those random errors?

Thank you.

[mattn]

Could you try to open the database like below?

gDb, err = sql.Open("sqlite3", "file:databaselocked.sqlite?cache=shared&mode=rwc")

[pantaluna]

I have made the code change in the test program <databaselocked.go> and it works now, thank you :)

But after making the same code changes in the API Server, it still does not work... The original sql error "database is locked" is gone but a new error pops up.

The new sql error occurs randomly, +-6% of the SQL Update sqlStmnt.Exec() statements

sqlite3.Error{Code:6, ExtendedCode:262, err:"database table is locked: counters"} 

I have made an attempt to write another test programme that reflects what the api server is doing.
The original test program launched a number of goroutines. Each goroutine executed an SQL Update (autocommit mode).

The new test program launches the same number of goroutines. Each goroutine executes an SQL Select and an SQL Update statement (autocommit mode).

=> This is the new test program <databaselocked2.go> to make it easier for you to reproduce it.
https://gist.github.com/pantaluna/a2723b9f3e6b367875e6

# Filename: databaselocked2.go

# Script
go get
go run databaselocked2.go --addtestdata
go run databaselocked2.go --updates
go run databaselocked2.go --updates > updates.log 2>&1
grep --context=2 --ignore-case  "sqlite3.Error" updates.log

Thanks for your help.

[pantaluna]

FYI The error also occurs (randomly) when putting the database in write-ahead-mode and still with at least +-50 requests / second):

    // SQLITE3 SYSTEM CONFIG
    sSql = "PRAGMA journal_mode=WAL"
    sqlResult, err = gDb.Exec(sSql)

sqlite3.Error{Code:6, ExtendedCode:262, err:"database table is locked: counters"}: "UPDATE counters SET code=:code,description=:description,value=:value,created_at=:created_at,updated_at=:updated_at WHERE id=:id"

[mattn]

You should separate sqlite connection per goroutines.

[pantaluna]

You should separate sqlite connection per goroutines.
hi @mattn mattn,
the database/sql package documentation clearly states that you must do an sql.Open() only once in an programme, so not for every goroutine (or http handler func).

// The returned DB is safe for concurrent use by multiple goroutines
// and maintains its own pool of idle connections. Thus, the Open
// function should be called just once. It is rarely necessary to
// close a DB.
func Open(driverName, dataSourceName string) (*DB, error) {

[pantaluna]

I have done more tests. This time I have implemented the same programme for the SQLite and the MySQL database, and I have used the config setting db.SetMaxOpenConns(i).

These tests were run on an Ubuntu Server v13.10 (running the tests in Windows7 gives other results).

Remember that the errors only pop up when doing things in parallel and for more than 100 operations per seconds (which represents a website with medium traffic in my case).

The results are interesting.

Variation 1. database/sql db.SetMaxOpenConns(i) from is not called and therefore the database/sql package imposes no limit on the nbr of connections (same as before).

Result SQLite (same as before): sqlite3.Error{Code:6, ExtendedCode:262, err:"database table is locked: counters"}
SQLite does not seem to have a limit on the number of simultaneous connections.

Result MySQL: &mysql.MySQLError{Number:0x410, Message:"Too many connections"}
This result opened my eyes. The MySQL Server restricts the number of simultaneous connections by design (it is setup as 100 in the Mysql config). So this error is as expected and it avoids using excessive system resources.

Variation 2. db.SetMaxOpenConns(i) is set to db.SetMaxOpenConns(75). This will throttle the number of active connections. I have chosen deliberately 75 which is a value < 100 (100 = the setting in the MySQL server config).

Result SQLite (same as before): sqlite3.Error{Code:6, ExtendedCode:262, err:"database table is locked: counters"}
This is very unfortunate. SQLite does not seem to respect the db.SetMaxOpenConns(i) setting because the same error comes up as before. I know the SQLIte DBMS does not implement it, but the sqlite-driver could do that.

Result MySQL: it works perfectly without errors :)

Global Conclusions so far:

1. MySQL works fine when configured correctly on the client and the server.
2. SQLite (server or driver) does not have a limit on the number of simultaneous connections. It does not respect the db.SetMaxOpenConns(i) setting (or the package database/sql does not do that correctly). => And I think this situation is causing the problem I describe in this github issue. I think some data structures in the driver get overwritten if the nbr of parallel connections gets too high.

I do not know how to solve this for SQLite.Please help ...

[mattn]

Could you please show me the last your code?

[pantaluna]

This is the latest test programme for SQLite and MySQL with instructions included. Thank you.

https://gist.github.com/pantaluna/7ad0988269119507539b

[metacoin]

I am using write-ahead logging as well as shared cache and I am still getting this error:

database table is locked: table_name

Has this issue been resolved? Should I implement a priority queue to avoid concurrency issues? Thanks!

[mattn]

Try latest commit

[mattn]

Should be done.

[wuranbo]

Looks it is not fixed.
I got he exactly same error.
When I set "cache=shared&mode=rwc", same error described upon... even using different connection for different go routine not solved it too.

[terran42]

Old thread but this might help someone:

Running the gist in @pantaluna 's comment ( https://gist.github.com/pantaluna/7ad0988269119507539b ) still produces the same error (database table is locked: counters) on my machine, but setting gDbMaxOpenConns to 1 works fine: no errors and no obvious slowing down.

A related issued: applikatoni/applikatoni#35

[mattn]

See #274 (comment)