golang sqlite数据库 rows.Close()造成错误database is locked

分手后的思念是犯贱 2021-08-26 13:20 559阅读 0赞

在最近的一个程序中，使用的是sqlite数据库。涉及到多线程对数据库的读写。因为sqlite本身有五个锁状态：unlocked,shared,reserved,pending,exclusive。每个事务都必须获得相应的锁才可以进行读写操作，所以一开始我自己的程序中是没有加任何锁的。具体sqlite的各种锁状态的知识百度一下就会很多很多。

我的源代码时使用golang编写的，使用了第三方的sqlite包github.com/mattn/go-sqlite3，但是并没有导入这个包，只是import \_"github.com/mattn/go-sqlite3"，调用了包里的初始化函数，实现了database/sql包里的相关接口，具体对于数据库的操作还是使用的是sql包中的DB对象进行操作。

但是当代码跑起来的时候，在进行写数据库操作的时候却报了这样的一个错：error:database is locked。上网查了一下当事务进行并发写操作的时候，是有可能产生死锁的，一开始还以为是死锁造成的，就在代码中在应用层对所有的事务都加了锁，但是令人崩溃的是依旧报错。

到最后我索性把数据库所有的读写操作全部改成串行，但是令人难以置信的是，，，依然报错。

最后在stackoverflow上看到一个回答：https://stackoverflow.com/questions/32479071/sqlite3-error-database-is-locked-in-golang

尝试一下调用rows.Close()

意思就是，当golang对关系型数据库进行操作的时候，读操作的代码一般是这样子的：

rows,err:=db_driver.Query("select * from table")
    if err!=nil{
     
    }
    for rows.Next(){
    //读取数据
    }

他的建议就是调用rows.Close()，即：

rows,err:=db_driver.Query("select * from table")
    defer rows.Close()
    if err!=nil{
     
    }
    for rows.Next(){
    //读取数据
    }

按照这样的方法尝试过以后，便不再报错。看go的标准库代码的时候发现，rows.Close()这个方法是幂等的，当rows.Next()返回false，即所有行数据都已经遍历结束后，会自动调用rows.Close()方法。而我的代码里面，有的地方，当rows.Next()返回true的时候，在循环体当中便有break代码，导致没有调用rows.Close()方法。

为了弄清楚这其中的原理，我做了一个实验，写了这样的一个小程序：

package main
     
    import (
    	"database/sql"
     
    	"fmt"
    	"time"
     
    	_ "github.com/mattn/go-sqlite3"
    )
     
    var db_driver *sql.DB
     
    func DBInit() {
    	var err error
    	db_driver, err = sql.Open("sqlite3", "test.db")
    	if err != nil {
    		fmt.Println(err.Error())
    		return
    	}
    }
     
    func createDBTables() {
    	create_table := `create table test(
    		Seq integer primary key autoincrement,
    		A text,
    		B text
    	);`
    	_, err := db_driver.Exec(create_table)
    	if err != nil {
    		fmt.Println(err.Error())
    		return
    	}
     
    	stmt, err := db_driver.Prepare("insert into test(A,B) values(?,?)")
    	if err != nil {
    		fmt.Println(err.Error())
    		return
    	}
    	_, err = stmt.Exec("a1", "b1")
    	if err != nil {
    		fmt.Println(err.Error())
    		return
    	}
     
    }
     
    func main() {
    	DBInit()
    	createDBTables()
    	rows, err := db_driver.Query("select * from test")
    	if err != nil {
    		fmt.Println(err.Error())
    		return
    	}
    	for rows.Next() {
    		break
    	}
     
    	stmt, err := db_driver.Prepare("insert into test (A,B) values(?,?)")
    	if err != nil {
    		fmt.Println(err.Error())
    		return
    	}
     
    	fmt.Println("insert")
    	fmt.Println(time.Now())
    	_, err = stmt.Exec("a2", "b2")
    	if err != nil {
    		fmt.Println(time.Now())
    		fmt.Println(err.Error())
    		stmt.Close()
    	}
     
    	for {
     
    	}
    }

在数据库初始化的时候，便在数据库中插入一条记录，在main方法中，串行地对数据库进行读操作和写操作，其中，由于数据库中已经有一条记录，第一次调用rows.Next()返回的是true，此时直接break，即rows没有调用Close()方法，此时再次进行写操作，便报出了database is locked的错误。如果加上rows.Close()便不再报错。

sql包中的Stmt结构体代表了一个事务，调用Close()方法代表关闭一个事务，而rows的数据类型是\*sql.Rows，rows调用Close()方法代表读结束。

在另一篇文章中，我找到了更加详细的解释：http://beets.io/blog/sqlite-nightmare.html

sqlite使用的是粗放型的文件锁，对并发读支持地很好而对于并发写支持得并不好。在sqlite当中，写事务需要等待所有的读事务释放了共享锁（读锁）以后才可以进行写操作，如果一个读事务持续太长时间还没有结束的话，其他等待的事务有可能就会一直停住。为了解决这个问题，引入了超时的机制，如果一个事务等待其他事务释放锁的时间超过5秒的话，就会抛出database is locked的错误。

在上面的代码中，我从开始执行写操作的时候打印了一个时间，在报错的时候又打印了一个时间，这个时间差正好是5秒，于是便不难解释这种现象：在main函数中，第一个读事务拿到了sqlite的共享锁（读锁）开始了读操作，但是没有调用rows.Close()方法，所以说第一个读事务没有释放共享锁，第二个写事务必须要等待第一个事务释放共享锁。5秒之后第一个读事务还没有释放共享锁，所以直接报出database is locked的错误。

此时，第一个事务还没有释放共享锁，但此时，其他读事务依旧可以获得共享锁进行读操作，而没有办法进行写操作。从命令行访问数据库也验证了这个猜想：  
![70][]

[70]: /images/20210728/f1ec3ac289454272bcb5a0e06e257c56.png