Step by Step:如何写出 sqlite：database is locked 的 BUG

写在前面

使用 SQLite 经常会遇到提示 database is locked，那这个 BUG 反映什么意义，又是如何产生呢？

官方文档

先看看 SQLite 官方文档的相关介绍。

SQLite到底在锁什么：SQLite 的锁机制

SQLite 的定位是简单而完整的提供一个支持并发的关系型数据库的解决方案。因此，为了支持并发，SQLite 巧妙的通过 OS 的文件锁来实现库级锁，为了最大限度的保证并发 SQLite 设计了多个不同层级的锁。

SQLite 有 5 个不同的锁状态：未加锁(UNLOCKED)、共享 (SHARED)、保留 (RESERVED)、未 决(PENDING) 和排它(EXCLUSIVE)。SQLite 使用锁逐步上升机制，为了获得写入数据库的权限，连接需要逐级地获得最终的排它锁进行写入。

每个数据库连接在同一时刻只能处于其中一个锁状态，那么对于某个特定的数据库连接而言，当其成功获取到特定的 SQLite 文件某个锁状态时，其意义如下：

锁层级	锁名称	数据库连接的“权限”	数据库对数据库连接的“承诺”
1	UNLOCKED-未加锁	默认状态，即不能读也不能写	无
2	SHARED-共享锁	可读	允许多个连接同时持有共享锁，且保证在持有共享锁期间不会发生数据库写入
3	RESERVED-保留锁	准备写入，可以在缓冲区进行修改	整个数据库文件最多只有一把保留锁
4	PENDING-未决锁	等待缓冲区修改提交	不允许其它连接获得新的共享锁，开始等待已经拥有共享锁的连接完成工作并释放其共享锁。 一旦所有其它共享锁都被释放，拥有未决锁的连接就可以将其锁提升至排它锁
5	EXCLUSIVE-排它锁	可以进行写入	不允许任何其他类型的锁与独占锁共存

database is locked的含义：SQLITE_BUSY 与 SQLITE_LOCKED 的区别

官方解释如下：

错误号	错误提示	含义
SQLITE_BUSY（5）	database is locked	由于其他数据库连接未释放锁，准备写入（读取）数据库的连接等待超时，无法完成操作
SQLITE_LOCKED（6）	database table is locked	由于同一数据库连接中的冲突或与使用共享缓存的不同数据库连接的冲突，写操作无法继续

所以，database is locked 其实并非一定是死锁，它仅仅提示某个连接等待锁超时的一种错误提示。

如何写出 database is locked 的 BUG

作死方法一

• 思路：前一个连接尚未释放锁，下一连接等待写入

• BUG的写法：

package main

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
	"log"
)

var (
	db     *sql.DB
	dbFile = "test.db"
)

func initDb() {
	var err error
	db, err = sql.Open("sqlite3", dbFile)
	if err != nil {
		log.Println("Sql Open:", err.Error())
	} else {
		_, err = db.Exec(`create table test(id integer primary key autoincrement,message text);`)
		if err != nil {
			log.Println("Create Table:", err.Error())
		} else {
			_, err = db.Exec(`insert into test(message) values("a")`)
			if err != nil {
				log.Println("Insert Into:", err.Error())
			}
		}
	}
}

func main() {
	var err error
	initDb()
	rows, err := db.Query("select * from test")
	if err != nil {
		log.Println("Query:", err.Error())
	} else {
		for rows.Next() {
			break
		}
		// rows.Close() /* 开启后会避免 SQLITE_BUSY（5） */
		_, err = db.Exec(`insert into test(message) values("a")`)
		if err != nil {
			log.Println("Insert Into After Query:", err.Error())
		}
		_, err = db.Exec(`select * from test`)
		if err != nil {
			log.Println("Select After Query:", err.Error())
		}
	}
}

• 输出：

2019/07/14 12:55:44 Insert Into After Query: database is locked

Process finished with exit code 0

作死方法二

• 思路：多连接并发写入

• BUG的写法：

package main

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"sync"
)

var (
	db     *sql.DB
	dbFile = "test.db"
)

func initDb() {
	var err error
	db, err = sql.Open("sqlite3", dbFile)
	if err != nil {
		log.Println("Sql Open:", err.Error())
	} else {
		_, err = db.Exec(`create table test(id integer primary key autoincrement,message text);`)
		if err != nil {
			log.Println("Create Table:", err.Error())
		} else {
			_, err = db.Exec(`insert into test(message) values("a")`)
			if err != nil {
				log.Println("Insert Into:", err.Error())
			}
		}
	}
}

func main() {
	var err error
	var wg sync.WaitGroup
	flag := false
	for i := 1; i < 10; i++ {
		go func() {
			wg.Add(1)
			defer wg.Done()
			initDb()
			for {
				if flag {
					break
				}
				_, err = db.Exec(`update test set message = "b"`)
				if err != nil {
					log.Println("Update:", err.Error())
					flag = true
				}
			}
		}()
	}
	wg.Wait()
}

• 输出：

2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:34 Create Table: table test already exists
2019/07/14 14:14:41 Update: database is locked

Process finished with exit code 0