Golang操作SQLite3无并发时报database is locked如何解决

根本原因 #

报错来自SQLite原生的读写锁互斥规则：

• 调用db.Query()查询待执行SQL文件列表时，未遍历完成、未关闭的结果集（即代码中的scripts变量）会一直持有数据库的读锁
• 代码中写的defer scripts.Close()只会在main函数退出时才执行，在遍历结果集的循环内调用db.Exec()执行写操作时，读锁始终处于占用状态
• SQLite要求写操作必须等所有读锁释放后才能获取写锁执行，因此会直接返回database is locked错误。

此前排查方案无效的原因 #

• 添加rows.Close()但使用defer延迟到函数退出才执行，写操作触发时读锁根本没释放，没有实际效果
• 移除sqlx改用原生database/sql库不影响底层锁逻辑，问题和第三方封装无关
• 开关Navicat连接、重启电脑、转储新数据库文件均不生效，因为锁是当前运行的程序自身持有，不是外部进程占用、文件损坏导致的。

修复方法 #

优先选择最稳妥的实现：先把所有待执行的脚本路径全部读取到本地切片，立刻关闭结果集释放读锁，之后再遍历切片执行SQL文件即可。
修复后的核心代码示例：

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 run_order > 0 
	ORDER BY run_order;`

	scripts, err := db.Query(qry)
	if err != nil {
		println("query failed ", qry)
		os.Exit(-1)
	}

	// 先把所有文件路径读取到本地切片
	var fileList []string
	var file string
	for scripts.Next() {
		err = scripts.Scan(&file)
		if err != nil {
			println(err)
			continue
		}
		fileList = append(fileList, file)
	}
	// 主动关闭结果集，立刻释放读锁
	scripts.Close()

	// 锁释放后再遍历执行SQL，不会再报locked错误
	for _, sqlFile := range fileList {
		println(sqlFile[len(sqlFile)-80:])
		sqlScript, err := ioutil.ReadFile(sqlFile)
		if err != nil {
			println("reading script file failed\n", sqlFile)
			continue
		}
		if _, err := db.Exec(string(sqlScript)); err != nil {
			fmt.Println(string(sqlScript[:80]))
			fmt.Println(err)
		} else {
			fmt.Println("Success ", string(sqlScript[:80]))
		}
	}
}

补充：如果待执行的SQL列表特别长、不想全部加载到内存，可以在每次调用db.Exec()前手动关闭当前结果集，但需要自行维护遍历偏移量重新查询后续列表，逻辑复杂度高，非必要不选择该方案。

内容的提问来源于stack exchange，提问作者Darian Hickman