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?cache=shared&mode=rwc")
	if err != nil {
		log.Fatal("could not open sqlite3 database file", err)
	}
	defer db.Close()
	// db.SetMaxOpenConns(1)

	//setup(db)

	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()
	tx, err := db.Begin()
	if err != nil {
		fmt.Printf("begin. Exec error=%s", err)
		return
	}
	defer tx.Commit()

	result, err := tx.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("+")
}