sqlgogo-sqlite3

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


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]))
         }
     }
    

    }


Solution

  • 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]))
            }
        }
    
    }