How to fix "database is locked" when no concurrent threads are involved? golang, sqlite3

Question

I'm running a list of sql files. The list of files comes from the same sqlite3 db that I'm going to execute sql against, hence there's only one db connection. If I don't kill the loop over sql files it will return "database is locked" error for every file starting with the first. Things that didn't work:

1. No effect: Adding rows.Close() from Sqlite3 error: database is locked in golang

2. Removing sqlx and using database/sql didn't change anything.

3. No effect: Having Navicat open or closed.

4. Restarting my laptop, no effect.

5. No effect: Dumping database to a new file then running the process against the new file.

    package main
   
    import (
    "database/sql"
    "fmt"
    "io/ioutil"
    "os"
   
    _ "github.com/mattn/go-sqlite3"
    )
   
    func main() {
    dbPath := "/Users/darianhickman/Documents/wc_study/history.db"
   
    db, err := sql.Open("sqlite3", dbPath)
    if err != nil {
        println("db open failed.", err)
        os.Exit(-1)
    }
    defer db.Close()
    // qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
    qry := `select list_folder||'/'|| sql_file as script from run_list 
    where run_order > 0 
    ORDER BY run_order;`
   
    scripts, err := db.Query(qry)
   
    if err != nil {
        println("query failed ", qry)
        os.Exit(-1)
    }
    defer scripts.Close()
   
    var file string
    for scripts.Next() {
        err = scripts.Scan(&file)
        if err != nil {
            println(err)
        }
        println(file[len(file)-80:])
        sqlScript, err := ioutil.ReadFile(file)
   
        if err != nil {
            println("reading script file failed\n", file)
        }
        if _, err := db.Exec(string(sqlScript)); err != nil {
            fmt.Println(string(sqlScript[:80]))
            fmt.Println(err)
   
        } else {
            fmt.Println("Success ", string(sqlScript[:80]))
        }
    }
   

   }

Answer 1

As per the official documentation:

When you get a database is locked, please use the following options.

Add to DSN: cache=shared

Example:

  db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared")

Next, please set the database connections of the SQL package to 1:

  db.SetMaxOpenConns(1)

Answer 2

Per @kostix observation, I gave up on reading the list of scripts while executing the scripts and that worked.

package main

import (
    "fmt"
    "io/ioutil"

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

func main() {
    // dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db?cache=shared"
    dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db"
    db := sqlx.MustConnect("sqlite3", dbPath)
    defer db.Close()
    // db.SetMaxOpenConns(1)

    // qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
    qry := `select list_folder||'/'|| sql_file as script from run_list 
    where run_order > 0 
    ORDER BY run_order;`

    scripts, err := db.Query(qry)
    if err != nil {
        println("script qry failed ", qry)
    }
    defer scripts.Close()
    var files []string
    var file string

    // Giving up on executing sql scripts while reading the query of scripts.
    for scripts.Next() {
        err = scripts.Scan(&file)
        if err != nil {
            println(err)
        } else {
            files = append(files, file)
        }
    }

    for _, file := range files {

        println(file[len(file)-80:])
        sqlScript, err := ioutil.ReadFile(file)
        if err != nil {
            println("reading script file failed\n", file)
        }
        if _, err := db.Exec(string(sqlScript)); err != nil {
            fmt.Println(string(sqlScript[:80]))
            fmt.Println(err)

        } else {
            fmt.Println("Success ", string(sqlScript[:80]))
        }
    }

}