modernc.org/sqlite驱动SQLite并发读写研究

背景

针对Go语言modernc.org/sqlite驱动并发读写过程中的报错“database is locked (5) (SQLITE_BUSY)”的研究。

测试代码

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:%s\n", 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 done\n", 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 done\n", idx)
        }(ri)
    }

    wg.Wait()

    tc := time.Since(startT)

    fmt.Printf("time cost:%s\n", tc.String())

}

结论

1. journal_mode = wal 和 busy_timeout = 10000 无法保证并发读写不报错；
2. 读写锁无法保证并发读写不报错（包括并发读）；
3. 所有SQL操作都用写锁，能保证并发读写不报错;
4. 这个问题只在Windows上有，Linux和Mac上好像没有这个问题。

另外，wal模式性能提升明显，测试记录如下（测试环境：win10；i7-10700k；WD_BLACK SN750 1T）：

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