Golang mattn sqlite3 driver db locks

https://groups.google.com/g/golang-nuts/c/y7WzUsMtqDA
https://go.dev/play/p/tLjXXcHq5H

package main

import (
    "fmt"
    "math/rand"
    "sync"
    "time"
)

func main() {
    launchSomeFakeQueries()
}

var mutexes = map[string]*sync.Mutex{}
var globalMapGuard sync.Mutex

//
// Protect the global map.
// Any accesses to the global map MUST lock the global mutex and then MUST return very quickly
//

func getMutex(dbpath string) *sync.Mutex {
    globalMapGuard.Lock()
    defer globalMapGuard.Unlock()

    if mutex, exists := mutexes[dbpath]; exists {
        return mutex
    }
    var newMutex sync.Mutex
    mutexes[dbpath] = &newMutex
    return &newMutex
}

//
// Execute some (potentially long) query on a specific DB
//

func executeGuardedQuery(dbpath string, q func(string)) {
    mutex := getMutex(dbpath)
    mutex.Lock()
    defer mutex.Unlock()

    q(dbpath)
}

func launchSomeFakeQueries() {
    var wg sync.WaitGroup
    for i := 0; i < 25; i++ {
        dbpath := fmt.Sprintf("file%d.sqlite", rand.Intn(5))
        duration := time.Duration(rand.Intn(100)) * time.Millisecond
        wg.Add(1)
        go executeGuardedQuery(dbpath, func(dbpath string) {
            fmt.Println("Starting some work on", dbpath)
            time.Sleep(duration)
            fmt.Println("Finished some work on", dbpath)
            wg.Done()
        })
    }
    wg.Wait()
}

https://sqlite.org/wal.html#concurrency

https://stackoverflow.com/questions/36431452/precautions-to-avoid-sqlite-file-database-locks-when-concurrent-goroutines-try-t

https://www.reddit.com/r/golang/comments/1988ch8/database_is_locked_when_writing_sqlite3_interface/


https://github.com/mattn/go-sqlite3/issues/209

https://gist.github.com/davyzhang/1392344f993eac471c90

package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    os.Remove("/tmp/testlock.db")
    os.Remove("/tmp/testlock-shm.db")
    os.Remove("/tmp/testlock-wal.db")

    var db *sql.DB
    if len(os.Args) != 2 { //no param
        printUsage()
        return
    }
    switch os.Args[1] {
    case "1":
        db = openDB()
        initDB(db)
    case "0":
        dbLoc := openDB()
        initDB(dbLoc)
        dbLoc.Close()
    default:
        printUsage()
        return
    }

    ch := make(chan bool)
    go writer(db)
    go reader(db)
    <-ch
}

func printUsage() {
    fmt.Printf("usage: dbtest mode\n 0:multiple connection; 1:single connection\n")
}

func initDB(db *sql.DB) {
    sSql := `
        CREATE TABLE counters (
            id      INTEGER,
            intro         VARCHAR (255)
        )`
    db.Exec(sSql)
}

func openDB() *sql.DB {
    db, err := sql.Open("sqlite3", "file:/tmp/testlock.db?cache=shared&mode=rwc&_busy_timeout=9999999")
    if err != nil {
        log.Printf("open error %s", err)
        return nil
    }
    db.Exec("PRAGMA journal_mode=WAL")
    return db
}

func writer(db *sql.DB) {
    var dbLoc *sql.DB
    if db == nil {
        dbLoc = openDB()
    } else {
        dbLoc = db
    }
    sSql := `
        INSERT INTO counters (
            id,intro
        ) VALUES(
            ?,?
        )`
    dbLoc.Exec(sSql, 1, "")
    i := 0
    sSql = `
    update counters set id = ?
    `
    for {
        _, err := dbLoc.Exec(sSql, i)
        i += 1
        if err != nil {
            log.Printf("db error in writer %s", err)
            os.Exit(1)
        }
    }
}

func reader(db *sql.DB) {
    var dbLoc *sql.DB
    if db == nil {
        dbLoc = openDB()
    } else {
        dbLoc = db
    }
    sSql := `
    select * from counters
    `
    for {
        _, err := dbLoc.Exec(sSql)
        if err != nil {
            log.Printf("db error in reader %s", err)
            os.Exit(1)
        }
    }
}