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

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

• But I cannot reproduce the errors with another driver:
  https://gist.github.com/gwenn/c576db836fee0cb5747b/98774f0e63aa05feb06eafbaa36ddd9cd176bcdb
• The Russ Cox driver works:
  https://gist.github.com/gwenn/c576db836fee0cb5747b/3b2fe9dffc8f55b855cdaea04306773c7cbf78c4
  if a busy timeout is set:

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
 }

• The mxk driver works too:
  https://gist.github.com/gwenn/c576db836fee0cb5747b/fc233a881d1dbc12ed73e0fc4a856892923e3aca

Regards.

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

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

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