背景
针对Go语言modernc.org/sqlite驱动并发读写过程中的报错“database is locked (5) (SQLITE_BUSY)”的研究。
测试代码
代码语言：go复制package main
import (
"database/sql"
"fmt"
"sync"
"time"
_ "modernc.org/sqlite"
)
func main() {
var db, err = sql.Open("sqlite", "db.sqlite")
if err != nil {
panic("connect db error" err.Error())
}
defer db.Close()
db.Exec("PRAGMA journal_mode = wal;")
db.Exec("PRAGMA busy_timeout = 10000;")
db.Exec("DROP TABLE IF EXISTS user;")
db.Exec("CREATE TABLE user(id integer primary key, name text not null, age integer not null);")
db.Exec("insert into user(name, age) values('user1', 1);")
if res, err := db.Query("PRAGMA journal_mode;"); err != nil {
panic("query error:" err.Error())
} else {
for res.Next() {
var mode string
if err := res.Scan(&mode); err != nil {
panic("scan error:" err.Error())
}
fmt.Printf("journal_mode:%sn", mode)
}
res.Close()
}
wg := sync.WaitGroup{}
wr := sync.RWMutex{}
startT := time.Now()
for wi := 0; wi < 10; wi {
wg.Add(1)
go func(idx int) {
for i := 0; i < 10000; i {
wr.Lock()
if _, err := db.Exec("update user set age = ? where id = 1;", i); err != nil {
panic("update error:" err.Error())
}
wr.Unlock()
}
wg.Done()
fmt.Printf("update %d donen", idx)
}(wi)
}
for ri := 0; ri < 10; ri {
wg.Add(1)
go func(idx int) {
for i := 0; i < 10000; i {
wr.Lock()
if res, err := db.Query("select * from user where id = 1;"); err != nil {
panic("query error:" err.Error())
} else {
res.Close()
}
wr.Unlock()
}
wg.Done()
fmt.Printf("query %d donen", idx)
}(ri)
}
wg.Wait()
tc := time.Since(startT)
fmt.Printf("time cost:%sn", tc.String())
}结论
journal_mode = wal和busy_timeout = 10000无法保证并发读写不报错；- 读写锁无法保证并发读写不报错（包括并发读）；
- 所有SQL操作都用写锁，能保证并发读写不报错。
另外，wal模式性能提升明显，测试记录如下（测试环境：win10；i7-10700k；WD_BLACK SN750 1T）：
代码语言：txt复制journal_mode:delete
update 0 done
query 2 done
update 6 done
update 9 done
update 1 done
query 0 done
query 6 done
update 8 done
query 5 done
update 2 done
query 3 done
update 5 done
query 1 done
query 7 done
query 9 done
query 8 done
update 3 done
update 4 done
update 7 done
query 4 done
time cost:1m17.0827793s
journal_mode:wal
query 8 done
update 0 done
query 2 done
update 4 done
query 6 done
update 1 done
query 0 done
query 9 done
update 2 done
update 5 done
query 4 done
query 1 done
update 9 done
update 7 done
update 8 done
query 3 done
update 3 done
query 7 done
query 5 done
update 6 done
time cost:31.9394113s
