最近写产品用到了sqlite3作为单机数据库，碰到一个挺有意思的问题。需求大体是两张表，查询需要连表查询，更新也需要连表更新；总共只有几百条数据，但运行过程中出现明显的超时和异常，以为是sqlite有问题，但想想觉得不可能，不至于扛不住几百条数据的并发查询，后来发现似乎是因为sqlite的锁是个库级别的完全单线程锁引起的。
代码是用go写的，model部分如下：

package main

type App struct {
	Id 			int 		`gorm:"id;primary_key"`
	Name 		string 		`gorm:"name"`
	UpdateAt	int64		`gorm:"updateAt"`
}

type Task struct {
	Id			int			`gorm:"id;primary_key"`
	Name		string		`gorm:"name"`
	AppId 		int 		`gorm:"appId"`
	AppName 	string 		`gorm:"-"`
	updateAt	int64		`gorm:"updateAt"`
}

其中 task表的AppName字段需要去app表查询，通过appId作为外键。
为了对比，这里同时使用mysql和sqlite作为数据库。
如果单纯查询，两种数据库都没有问题：

package main

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	_ "github.com/jinzhu/gorm/dialects/sqlite"
	"time"
)


func main(){
	count := 0
	flag := 3
	for i:=0;i < flag;i++ {
		go func(name string) {
			sqliteSearch(name)
			count++
		}("sqlite")
	}
	for ;count < flag; {
		time.Sleep(time.Second)
	}
}


func mysqlSearch(name string){
	fmt.Println(name)
	conn,err := gorm.Open("mysql","root:root@tcp(localhost:3306)/test?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer conn.Close()
	tasks,err := search(conn)
	if err != nil{
		fmt.Println(err)
		return
	}
	p(tasks)
}

func sqliteSearch(name string){
	fmt.Println(name)
	conn,err := gorm.Open("sqlite3","D:\\code\\go\\learning\\src\\main\\db.db")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer conn.Close()
	tasks,err := search(conn)
	if err != nil{
		fmt.Println(err)
		return
	}
	p(tasks)
}

func p(tasks []*Task){
	for _,task := range tasks{
		fmt.Println(task.Id,task.Name,task.AppName)
	}
}

func updateApp(conn *gorm.DB,task *Task) error {
	appId := task.AppId
	app := &App{}
	err := conn.Table("app").Where("id = ?",appId).Find(app).Error
	if err != nil{
		return err
	}
	task.AppName = app.Name
	return nil
}


func search(db *gorm.DB) ([]*Task,error){
	tasks := make([]*Task,0)
	rows,err := db.Table("app").Raw("select id,name,appId from task").Rows()
	if err != nil{
		return nil,err
	}
	defer rows.Close()
	for rows.Next(){
		task := &Task{}
		err = rows.Scan(&task.Id,&task.Name,&task.AppId)
		if err != nil{
			return nil, err
		}
		err = updateApp(db,task)
		if err != nil{
			return nil, err
		}
		tasks = append(tasks,task)
	}
	return tasks,err
}

同时开启多个协程去查询表格，数据都可以被正常查出来。
如果在查询过程中有更新或者更新查询同时有，问题就来了：

package main

import (
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
	_ "github.com/jinzhu/gorm/dialects/sqlite"
	"time"
)


func main(){
	count := 0
	flag := 3
	for i:=0;i < flag;i++ {
		go func(name string) {
			sqliteSearch(name)
			count++
		}("sqlite")
	}
	for ;count < flag; {
		time.Sleep(time.Second)
	}
}


func mysqlSearch(name string){
	fmt.Println(name)
	conn,err := gorm.Open("mysql","root:root@tcp(localhost:3306)/test?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer conn.Close()
	tasks,err := search(conn)
	if err != nil{
		fmt.Println(err)
		return
	}
	p(tasks)
}

func sqliteSearch(name string){
	fmt.Println(name)
	conn,err := gorm.Open("sqlite3","D:\\code\\go\\learning\\src\\main\\db.db")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer conn.Close()
	tasks,err := search(conn)
	if err != nil{
		fmt.Println(err)
		return
	}
	p(tasks)
}

func p(tasks []*Task){
	for _,task := range tasks{
		fmt.Println(task.Id,task.Name,task.AppName)
	}
}

func updateApp(conn *gorm.DB,task *Task) error {
	appId := task.AppId
	app := &App{}
	err := conn.Table("app").Where("id = ?",appId).Find(app).Error
	if err != nil{
		return err
	}
	task.AppName = app.Name
	return nil
}


func search(db *gorm.DB) ([]*Task,error){
	tasks := make([]*Task,0)
	rows,err := db.Table("app").Raw("select id,name,appId from task").Rows()
	if err != nil{
		return nil,err
	}
	defer rows.Close()
	for rows.Next(){
		task := &Task{}
		err = rows.Scan(&task.Id,&task.Name,&task.AppId)
		if err != nil{
			return nil, err
		}
		now := time.Now().Unix() // 这里在查询时更新表格
		err = db.Table("task").Where("id = ?",task.Id).Update("updateAt",now).Error
		if err != nil{
			return nil, err
		}
		err = updateApp(db,task)
		if err != nil{
			return nil, err
		}
		tasks = append(tasks,task)
	}
	return tasks,err
}

上面代码在查询时，更新了目标表的updateAt字段，此时就会出现如下报错：
在这里插入图片描述
先声明，这个报错mysql是不会出现的。
sqlite只有一把库级别的锁，当多个请求同时执行查询请求时，请求不会被阻塞；但是如果多个写请求发生，就会涉及到对锁的抢夺；如果各个请求都是很明确的读或者写区分开，那么也没什么问题，写请求被sqlite串行执行即可；但如果一个请求中有读有写，就会有问题；上面的代码中，查表时读多条数据，因此需要迭代，我怀疑这里虽然是读，极可能对表加了锁；而在通过appId查询appName时，进行了写操作，此时就造成了死锁；这种情况甚至不是因为多个请求并发引起的，将上面的flag变量改为1，使整个请求只有一条，依然会有锁死的问题。
解决这个问题的方案是访问sqlite时，每次都只进行读或者写操作，不允许同时进行，上面代码改为：

func search(db *gorm.DB) ([]*Task,error){
	lock.Lock()
	defer lock.Unlock()
	tasks := make([]*Task,0)
	rows,err := db.Table("app").Raw("select id,name,appId from task").Rows()
	if err != nil{
		return nil,err
	}
	defer rows.Close()
	for rows.Next(){
		task := &Task{}
		err = rows.Scan(&task.Id,&task.Name,&task.AppId)
		if err != nil{
			return nil, err
		}
		err = updateApp(db,task)
		if err != nil{
			return nil, err
		}
		tasks = append(tasks,task)
	}
	now := time.Now().Unix()
	for _,task := range tasks {
		err = db.Table("task").Where("id = ?",task.Id).Update("updateAt",now).Error
		if err != nil{
			return nil, err
		}
	}

	return tasks,err
}

上面有两点注意：
1 引入了锁，限制请求的并发数，这个似乎是必须的，如果不加锁依然会有锁超时的问题；
2 都查询完毕后，再循环对每一条数据进行更新；
这样操作，表锁死的问题就没有了；但代价有点儿高，没法多路写库。
另外查到其它一些方案，但并没有什么用，比如这里 https://blog.csdn.net/weixin_43851310/article/details/100709797 提到的建立连接时设置&_busy_timeout=9999999，使等待锁的时间无限长（默认为5秒，5秒后如果没有轮到锁就会报database is locked错误），但并没什么用，这个仅针对表非常大确实需要比较长时间等待才有用，这里的场景下几百条数据都要等待很久是不科学的；其它方案中还提到，比如忘记Close等，也确实可能引起这个问题，如上面循环查询时，一定要defer rows.Close().

Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

解锁HDC 2024之旅：从购票到报名，全程攻略

avatar 华为云开发者联盟
cover

从原始边列表到邻接矩阵Python实现图数据处理的完整指南

avatar 华为云开发者联盟
cover

华为云云原生FinOps解决方案，释放云原生最大价值

avatar 华为云开发者联盟