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

huangapple
go评论125阅读模式
英文:

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

问题

我正在运行一系列的 SQL 文件。文件列表来自同一个 sqlite3 数据库，我将对该数据库执行 SQL，因此只有一个数据库连接。如果我不终止对 SQL 文件的循环，它将从第一个文件开始返回"database is locked"错误。

以下是没有起作用的方法：

  1. 没有效果：从 https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang 添加 rows.Close()。
  2. 移除 sqlx 并使用 database/sql 没有改变任何事情。
  3. 没有效果：无论 Navicat 是打开还是关闭。
  4. 重启我的笔记本电脑，没有效果。
  5. 没有效果：将数据库导出到一个新文件，然后对新文件运行该过程。
  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"fmt"
  6. 	"io/ioutil"
  7. 	"os"
  9. 	_ "github.com/mattn/go-sqlite3"
  10. )
  12. func main() {
  13. 	dbPath := "/Users/darianhickman/Documents/wc_study/history.db"
  15. 	db, err := sql.Open("sqlite3", dbPath)
  16. 	if err != nil {
  17. 		println("db open failed.", err)
  18. 		os.Exit(-1)
  19. 	}
  20. 	defer db.Close()
  21. 	// qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
  22. 	qry := `select list_folder||'/'|| sql_file as script from run_list 
  23. 	where run_order > 0 
  24. 	ORDER BY run_order;`
  26. 	scripts, err := db.Query(qry)
  28. 	if err != nil {
  29. 		println("query failed ", qry)
  30. 		os.Exit(-1)
  31. 	}
  32. 	defer scripts.Close()
  34. 	var file string
  35. 	for scripts.Next() {
  36. 		err = scripts.Scan(&file)
  37. 		if err != nil {
  38. 			println(err)
  39. 		}
  40. 		println(file[len(file)-80:])
  41. 		sqlScript, err := ioutil.ReadFile(file)
  43. 		if err != nil {
  44. 			println("reading script file failed\n", file)
  45. 		}
  46. 		if _, err := db.Exec(string(sqlScript)); err != nil {
  47. 			fmt.Println(string(sqlScript[:80]))
  48. 			fmt.Println(err)
  50. 		} else {
  51. 			fmt.Println("Success ", string(sqlScript[:80]))
  52. 		}
  53. 	}
  55. }

希望对你有帮助！

英文:

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 https://stackoverflow.com/questions/32479071/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.

    1.  package main
    3.  import (
    4.  "database/sql"
    5.  "fmt"
    6.  "io/ioutil"
    7.  "os"
    9.  _ "github.com/mattn/go-sqlite3"
    10.  )
    12.  func main() {
    13.  dbPath := "/Users/darianhickman/Documents/wc_study/history.db"
    15.  db, err := sql.Open("sqlite3", dbPath)
    16.  if err != nil {
    17.  	println("db open failed.", err)
    18.  	os.Exit(-1)
    19.  }
    20.  defer db.Close()
    21.  // qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
    22.  qry := `select list_folder||'/'|| sql_file as script from run_list 
    23.  where run_order > 0 
    24.  ORDER BY run_order;`
    26.  scripts, err := db.Query(qry)
    28.  if err != nil {
    29.  	println("query failed ", qry)
    30.  	os.Exit(-1)
    31.  }
    32.  defer scripts.Close()
    34.  var file string
    35.  for scripts.Next() {
    36.  	err = scripts.Scan(&file)
    37.  	if err != nil {
    38.  		println(err)
    39.  	}
    40.  	println(file[len(file)-80:])
    41.  	sqlScript, err := ioutil.ReadFile(file)
    43.  	if err != nil {
    44.  		println("reading script file failed\n", file)
    45.  	}
    46.  	if _, err := db.Exec(string(sqlScript)); err != nil {
    47.  		fmt.Println(string(sqlScript[:80]))
    48.  		fmt.Println(err)
    50.  	} else {
    51.  		fmt.Println("Success ", string(sqlScript[:80]))
    52.  	}
    53.  }

    }

答案1

得分: 2

根据官方文档：

  1. 当你遇到数据库被锁定的情况时，请使用以下选项。
  3. 在 DSN 中添加：cache=shared
  5. 示例：
  7.   db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared")
  9. 接下来，请将 SQL 包的数据库连接数设置为 1：
  11.   db.SetMaxOpenConns(1)
英文:

As per the official documentation:

  1. When you get a database is locked, please use the following options.
  3. Add to DSN: cache=shared
  5. Example:
  7.   db, err := sql.Open("sqlite3", "file:locked.sqlite?cache=shared")
  9. Next, please set the database connections of the SQL package to 1:
  11.   db.SetMaxOpenConns(1)

答案2

得分: 0

根据@kostix的观察，我放弃了在执行脚本时读取脚本列表，这样做起作用了。

  1. package main
  3. import (
  4. 	"fmt"
  5. 	"io/ioutil"
  7. 	"github.com/jmoiron/sqlx"
  8. 	_ "github.com/mattn/go-sqlite3"
  9. )
  11. func main() {
  12. 	// dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db?cache=shared"
  13. 	dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db"
  14. 	db := sqlx.MustConnect("sqlite3", dbPath)
  15. 	defer db.Close()
  16. 	// db.SetMaxOpenConns(1)
  18. 	// qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
  19. 	qry := `select list_folder||'/'|| sql_file as script from run_list 
  20. 	where run_order > 0 
  21. 	ORDER BY run_order;`
  23. 	scripts, err := db.Query(qry)
  24. 	if err != nil {
  25. 		println("script qry failed ", qry)
  26. 	}
  27. 	defer scripts.Close()
  28. 	var files []string
  29. 	var file string
  31. 	// 在读取脚本查询时放弃执行SQL脚本。
  32. 	for scripts.Next() {
  33. 		err = scripts.Scan(&file)
  34. 		if err != nil {
  35. 			println(err)
  36. 		} else {
  37. 			files = append(files, file)
  38. 		}
  39. 	}
  41. 	for _, file := range files {
  43. 		println(file[len(file)-80:])
  44. 		sqlScript, err := ioutil.ReadFile(file)
  45. 		if err != nil {
  46. 			println("reading script file failed\n", file)
  47. 		}
  48. 		if _, err := db.Exec(string(sqlScript)); err != nil {
  49. 			fmt.Println(string(sqlScript[:80]))
  50. 			fmt.Println(err)
  52. 		} else {
  53. 			fmt.Println("Success ", string(sqlScript[:80]))
  54. 		}
  55. 	}
  57. }
英文:

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

  1. package main
  2. import (
  3. "fmt"
  4. "io/ioutil"
  5. "github.com/jmoiron/sqlx"
  6. _ "github.com/mattn/go-sqlite3"
  7. )
  8. func main() {
  9. // dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db?cache=shared"
  10. dbPath := "file:/Users/darianhickman/Documents/wc_study/history.db"
  11. db := sqlx.MustConnect("sqlite3", dbPath)
  12. defer db.Close()
  13. // db.SetMaxOpenConns(1)
  14. // qry := `select list_folder||'/'|| sql_file as script from run_list where 'order' > 0 ORDER BY 'order'; `
  15. qry := `select list_folder||'/'|| sql_file as script from run_list 
  16. where run_order > 0 
  17. ORDER BY run_order;`
  18. scripts, err := db.Query(qry)
  19. if err != nil {
  20. println("script qry failed ", qry)
  21. }
  22. defer scripts.Close()
  23. var files []string
  24. var file string
  25. // Giving up on executing sql scripts while reading the query of scripts.
  26. for scripts.Next() {
  27. err = scripts.Scan(&file)
  28. if err != nil {
  29. println(err)
  30. } else {
  31. files = append(files, file)
  32. }
  33. }
  34. for _, file := range files {
  35. println(file[len(file)-80:])
  36. sqlScript, err := ioutil.ReadFile(file)
  37. if err != nil {
  38. println("reading script file failed\n", file)
  39. }
  40. if _, err := db.Exec(string(sqlScript)); err != nil {
  41. fmt.Println(string(sqlScript[:80]))
  42. fmt.Println(err)
  43. } else {
  44. fmt.Println("Success ", string(sqlScript[:80]))
  45. }
  46. }
  47. }

huangapple
  • 本文由 发表于 2022年7月9日 01:46:01
  • 转载请务必保留本文链接：https://go.coder-hub.com/72915138.html
  • go
  • go-sqlite3
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定