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.

go-sqlite3_database_is_locked.go

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("+")
}

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
.+..+..++.....+..+..+..+..+...+...+.+.+.+....++...+..+.+..+..+.

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

thanks

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

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

Thanks, this gist help me a lot.

@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{}{}
		}()
	}