· 7 years ago · Nov 29, 2018, 08:24 PM
1package main
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "math/rand"
8 "runtime"
9
10 //"sync"
11 "time"
12
13 sqlite3 "github.com/mattn/go-sqlite3"
14)
15
16const (
17 setupSql = `
18CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, user_name TEXT);
19`
20)
21
22var r *rand.Rand
23
24func init() {
25 r = rand.New(rand.NewSource(time.Now().UnixNano()))
26}
27
28func main() {
29 db, err := sql.Open("sqlite3", "database_file.sqlite?cache=shared")
30 if err != nil {
31 log.Fatal("could not open sqlite3 database file", err)
32 }
33 defer db.Close()
34 db.SetMaxOpenConns(6)
35 setup(db)
36
37 count := runtime.GOMAXPROCS(-1)
38 done := make(chan string, count*2)
39 write_count := 5000
40 fmt.Printf("NumCpu %d\n", runtime.NumCPU())
41 fmt.Printf("GOMAXPROCS %d\n", count)
42
43 go func() {
44 // writes to users table
45 for i := 0; i < count; i++ {
46 go func(i, count int) {
47 done <- fmt.Sprintf("Started writing goroutine %d", i)
48 totalRetries := 0
49 totalDuration := time.Duration(0)
50 for j := 0; j < write_count; j++ {
51 retries, duration := write(db, i, count)
52 totalRetries += retries
53 totalDuration += duration
54 }
55 done <- fmt.Sprintf("Finished writing goroutine %d doing %d INSERTs: retries: %d, duration: %s", i, write_count, totalRetries, totalDuration.String())
56 }(i, count)
57 }
58
59 }()
60
61 finished := 0
62 for msg := range done {
63 fmt.Println(msg)
64 finished += 1
65 if finished >= count*2 {
66 break
67 }
68 }
69}
70
71func read(db *sql.DB, i, count int) {
72
73 rows, err := db.Query(`SELECT * FROM users LIMIT 100;`)
74 if err != nil {
75 fmt.Printf("\nusers select %d/%d. Query error=%s\n", i, count, err)
76 } else {
77 rows.Close()
78 }
79
80}
81
82func write(db *sql.DB, i, count int) (int, time.Duration) {
83 const MaxTries = 50
84 retryCount := 0
85 start := time.Now()
86
87Retry:
88 result, err := db.Exec(fmt.Sprintf(`INSERT INTO users (user_name) VALUES ("someuser%d");`, i))
89 if err != nil {
90 switch e := err.(sqlite3.Error); e.Code {
91 case sqlite3.ErrBusy:
92 retryCount += 1
93 if retryCount < MaxTries {
94 <-time.After(1 * time.Second)
95 goto Retry
96 } else {
97 fmt.Printf("\nFAIL ErrBusy. Exec error=%s goroutine %d\n", err, i)
98 }
99 case sqlite3.ErrLocked:
100 fmt.Printf("\nErrLocked. Exec error=%s goroutine %d\n", err, i)
101 default:
102 fmt.Printf("\nuser insert. Exec error=%s goroutine %d\n", err, i)
103 }
104 } else {
105
106 _, err = result.LastInsertId()
107 if err != nil {
108 fmt.Printf("\nuser writer. LastInsertId error=%s goroutine %d\n", err, i)
109 }
110 }
111 t := time.Now()
112 elapsed := t.Sub(start)
113 return retryCount, elapsed
114
115}
116
117func setup(db *sql.DB) {
118 _, err := db.Exec(setupSql)
119 if err != nil {
120 log.Fatal(err)
121 }
122}