Skip to content

Instantly share code, notes, and snippets.

@mrnugget
Created March 3, 2016 05:58
Show Gist options
  • Save mrnugget/0eda3b2b53a70fa4a894 to your computer and use it in GitHub Desktop.
Save mrnugget/0eda3b2b53a70fa4a894 to your computer and use it in GitHub Desktop.
Program that tests the concurrency issues with go-sqlite3. This will create two tables: `products` and `users`. One goroutine will repeatedly read from the `products` table in N fresh goroutines. At the same time ONE goroutine writes to the other table.
package main
import (
"database/sql"
"fmt"
"log"
"math/rand"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
const (
setupSql = `
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, user_name TEXT);
CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, product_name TEXT);
DELETE FROM products;
`
count = 5000
)
var r *rand.Rand
func init() {
r = rand.New(rand.NewSource(time.Now().UnixNano()))
}
func main() {
db, err := sql.Open("sqlite3", "database_file.sqlite")
if err != nil {
log.Fatal("could not open sqlite3 database file", err)
}
defer db.Close()
// db.SetMaxOpenConns(1)
done := make(chan struct{})
mu := &sync.Mutex{}
go func() {
// writes to users table
for i := 0; i < count; i++ {
write(db, mu, i, count)
randomSleep()
}
done <- struct{}{}
}()
go func() {
// reads from products table, each read in separate go routine
for i := 0; i < count; i++ {
go func(i, count int) {
read(db, mu, i, count)
done <- struct{}{}
}(i, count)
randomSleep()
}
}()
for i := 0; i < count+1; i++ {
<-done
}
}
func randomSleep() {
time.Sleep(time.Duration(r.Intn(5)) * time.Millisecond)
}
func setup(db *sql.DB) {
_, err := db.Exec(setupSql)
if err != nil {
log.Fatal(err)
}
for i := 0; i < 1000; i++ {
_, err := db.Exec(`INSERT INTO products (product_name) VALUES ("computer");`)
if err != nil {
log.Fatalf("filling up products table failed. Exec error=%s", err)
}
}
}
func read(db *sql.DB, mu *sync.Mutex, i, count int) {
// mu.Lock()
// defer mu.Unlock()
rows, err := db.Query(`SELECT * FROM products WHERE id = 5`)
if err != nil {
fmt.Printf("\nproducts select %d/%d. Query error=%s\n", i, count, err)
} else {
rows.Close()
}
fmt.Printf(".")
}
func write(db *sql.DB, mu *sync.Mutex, i, count int) {
// mu.Lock()
// defer mu.Unlock()
result, err := db.Exec(`INSERT INTO users (user_name) VALUES ("Bobby");`)
if err != nil {
fmt.Printf("user insert. Exec error=%s", err)
return
}
_, err = result.LastInsertId()
if err != nil {
fmt.Printf("user writer. LastInsertId error=%s", err)
}
fmt.Printf("+")
}
@mrnugget
Copy link
Author

mrnugget commented Mar 3, 2016

Without the Mutex and with unlimited database connections, this will pretty reliably result in output like this:

...
products select 4998/5000. Query error=database is locked
..
products select 2850/5000. Query error=database is locked
.
products select 2949/5000. Query error=database is locked
.
products select 3026/5000. Query error=database is locked
.++.+++++++.++.+.+++++++++.++++.+++++.++++++++.++++++++++.++.+++++.+.+++++.+++.++++++.++++++++++++++++++.++++++.+++++..+++++++++++++++++++++++++++.+++.++++++++++++.+++.++++.++++++++
products select 3330/5000. Query error=database is locked
.++++++.+++++++++++++
products select 3366/5000. Query error=database is locked
.++..++++++++++++.+++++
products select 3391/5000. Query error=database is locked
.++.+++++++++++++.
products select 3802/5000. Query error=database is locked
.
products select 3805/5000. Query error=database is locked
.
products select 3831/5000. Query error=database is locked
.+++++++++++++++.++
products select 3872/5000. Query error=database is locked
.+.+++++++++.++++++++.+++...++++++++++.++++++++++++++++.+++++++++.++++++++++++++++++++++++++++++++++++++++++++++++++++.+++++++.+.++++++++++++++++++++++++++++++..
products select 4490/5000. Query error=database is locked
.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
products select 4719/5000. Query error=database is locked
.++++++++++.+++++++++++++++
products select 4767/5000. Query error=database is locked
.++++++++.+++++++++++++++++++++++++++++++.++++++++++

With db.SetMaxOpenConns(1) enabled the database is locked errors disappear. With the Mutex enabled (mu.Lock() and defer mu.Unlock()) the errors disappear too.

Opening the database with this:

    db, err := sql.Open("sqlite3", "database_file.sqlite?cache=shared&mode=rwc")
    if err != nil {
        log.Fatal("could not open sqlite3 database file", err)
    }

doesn't help either.

@gwenn
Copy link

gwenn commented Mar 3, 2016

Hello,
I can reproduce the error with the mattn driver.

diff --git a/driver.go b/driver.go
index 8a6954e..6bfe6af 100644
--- a/driver.go
+++ b/driver.go
@@ -140,6 +140,7 @@ func (impl) Open(name string) (driver.Conn, error) {
        if db == nil {
                return nil, errors.New("sqlite succeeded without returning a database")
        }
+       C.sqlite3_busy_timeout(db, 1000) // TODO check return value
        return &conn{db: db}, nil
 }

Regards.

@mrnugget
Copy link
Author

mrnugget commented Mar 5, 2016

Thanks for the reply @gwenn!

I'm a bit irritated that the mxk driver works. It explicitly states in the documentation that reusing the same instance of *sql.DB is wrong: https://godoc.org/github.com/mxk/go-sqlite/sqlite3

@jeroiraz
Copy link

jeroiraz commented Jul 12, 2016

Hi @mrnugget, I've just changed mutex var to RWMutex. Besides it doesn't solve the issue, it might help as a workaround as concurrent reads are allowed. See fork: https://gist.github.com/jeronimoirazabal/cbe014eb22333cc3b6b56aa66379e933

@ifraixedes
Copy link

Without modifying anything rather than the driver to use the rsc.io/sqlite, I go many more locked messages rather than using the github.com/mattn/go-sqlite3 one.

With rsc.io/sqlite:

products select 0/5000. Query error=The database file is locked: database is locked
.
products select 1/5000. Query error=The database file is locked: database is locked
.
products select 2/5000. Query error=The database file is locked: database is locked
.++..+......+...++....+..+..+....+..+.+....+.+..
products select 37/5000. Query error=The database file is locked: database is locked
..
products select 38/5000. Query error=The database file is locked: database is locked
.+..+...+.+.+.+..+....+.+....+...++......+...+..+.+.+.
products select 75/5000. Query error=The database file is locked: database is locked
..
products select 77/5000. Query error=The database file is locked: database is locked
.+..+..+..+..+...+...+..+.+...+.+......+.+.+..
products select 108/5000. Query error=The database file is locked: database is locked
..+..+.+...+..+..+......++.++.++.+.+....+.+..+.+..+.+.+....+...
products select 150/5000. Query error=The database file is locked: database is locked

With github.com/mattn/go-sqlite3

..+....+..+.+...+....+...+.+..+.....+...+.+.+.+.......+..+.+.....+..+..+..+...+...+..+....+...+.....+..+..++.++..+.......+.+.+..+..+....+.+...+.+.+.....+...+....+.+.....+.+..+...+..+...+.+.+..+.+...+.+..+...+
.+.....+.+..+..+.+.....+.+...+...+...+...+...+..+..+...+..+...+..+....+...+..+....+.+..+....+...+.....+.+.....+..+....+....+..+...+..+..+...+..+..+...+...+..+.+...+...+...+...+...+.+.+....+...+...............
................................................................................................................................................................................................................
...........................................................................................................................................................................+.+....+...+..+.+......+...+.+....+..
...+..+...+...+.+...+.++...+.+...+.....+.+.....+..+...+..+..+.+.+.....++..+.++.+...+...+..+.+...+..++..+....+..+.+..+...++.+.+..+..+....+..+...+...+.+....+.+....+...+....+....++.+.+...+......+....+....+...+.+
..++..+.+..+.....+..+..+...+..+..+..+...+.+..+..+.+...+....+.+....+.....+....+.+......+.+....+....+....+..+....+...+...+.....+.+...+..+..+...+.....+.....+.+....+......+...+..++.......+.+....+...+.+.+....+..+.
+...+....+.+..+..+...+....+....+......+.+.+.+.+.+..+.+....+...+.+..+...+....+...+.+.+.+....+...+.+...+..+...+...+.....++.+...+..+.+.+.+...+..+.+.+..+...+..+....+..+..+........+...+.+.+....+.+.....+....+...+..
+.+...+..+....+...+...+..+...+.+..+..+......+.....+.+...+...+..+..++....+...++....+..+..+.+...+..+.+..+......+.+...+.......+..+.+.+..+..+..+.+...+..+....+....+..+.+..+...+..+..+.++....+..+..+....+.....+...+.+
..+.+..+..+.+...+....+.+..+.+...+..+..+..+.+....+..+....+..+.+.+..+.......+.+..+..+.+....+....+...+...+.+...+.+...+.....+..+..+...+....+...+.+.+..+....+..+.+.+...+..+.+..+.....+.+....+....+...+..+..+...+..+..
+....+..+.......................................................................................................................................................................................................
................................................................................................................................................................................................................
..........+...+.+...+..++..+..+....+..+..+..+......+.+.+...+.++..+..+.....+...++....+.+..+....+......+.....+..+..+.+...+.....+..+...+..++....+.....+....+....+.....+............................................
................................................................................................................................................................................................................
.......................................................................................................................................................+.+..+..+.+......+...+...+.+..+..+.+..+.+.++...+....+...+
..+.+..+..+..+.+....+.+...+....+......+.+.+..+...+..+.+...+...+.+....+.+..+.+.+.+.+..+.....+..+....++..+.....++...+...+..+.....+....+..+.+...+..+..+.+..+....+.+..+..+...+.....+..+....+..+.+.+.....+.+.+...+..+
...+..+...+...+..++...+.+.....+.+...+..+.+...+..+..+......+.+......+....+.+..+..+..+.+..+..+..+..+.+.+.+.+....+..+...+..+.+..+..+.+..+.+.+..+......+..+..+..+.+....+............................................
................................................................................................................................................................................................................
..................................................................................................................................................................................+..+...+...+...+...++....+.+..
+...++.+.++.....+....+.+...+.+...++......+...+....+....+..+.+..+.....+.+...+....+...+....+.....+..+..+..+..+....+.+...+...+.+.+.+..+..+.+..+.+.+.....+...+...+....+....+.....+..+..+
products select 1317/5000. Query error=database is locked
.
products select 1318/5000. Query error=database is locked
..
products select 3263/5000. Query error=database is locked
.+
products select 3264/5000. Query error=database is locked
.+..+..++.....+..+..+..+..+...+...+.+.+.+....++...+..+.+..+..+.

@nehbit
Copy link

nehbit commented Mar 16, 2018

This gist helped me debug SQLite issues in my program. Thank you.

@ArlenFuCN
Copy link

thanks

@demaggus83
Copy link

I can't reproduce the "database is locked" problem with this example code.

go1.12 windows/amd64
gcc.exe (x86_64-win32-seh-rev0, Built by MinGW-W64 project) 8.1.0
go-sqlite v1.10.0

@Canadadry
Copy link

Same here, I cant reproduce the error. Does that mean something on mattn/go-sqlite3 has fix the lock db ?

go version go1.16.3 darwin/arm64
mattn/go-sqlite3 v1.14.7

@zicla
Copy link

zicla commented Mar 18, 2022

Thanks, this gist help me a lot.

@thoriqadillah
Copy link

@Canadadry @demaggus83 you may need spawn multiple goroutine for write/read like this for example

        for i := 0; i < 50; i++ {
		go func() {
			// writes to users table
			for i := 0; i < count; i++ {
				write(db, mu, i, count)
				randomSleep()
			}

			done <- struct{}{}
		}()
	}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment