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

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())

}

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