· 7 years ago · Nov 30, 2018, 10:20 AM
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?_busy_timeout=5000")
30 if err != nil {
31 log.Fatal("could not open sqlite3 database file", err)
32 }
33 defer db.Close()
34 db.SetMaxOpenConns(5)
35 setup(db)
36
37 // db2, err := sql.Open("sqlite3", "database_file.sqlite?cache=shared?_busy_timeout=5000")
38 // if err != nil {
39 // log.Fatal("could not open sqlite3 database file", err)
40 // }
41 // defer db2.Close()
42 // db2.SetMaxOpenConns(1)
43
44 count := runtime.GOMAXPROCS(-1)
45 done := make(chan string, count*2)
46 write_count := 5
47 fmt.Printf("NumCpu %d\n", runtime.NumCPU())
48 fmt.Printf("GOMAXPROCS %d\n", count)
49
50 go func() {
51 // writes to users table
52 for i := 0; i < count; i++ {
53 go func(i, count int) {
54 done <- fmt.Sprintf("Started writing goroutine %d", i)
55 totalRetries := 0
56 totalDuration := time.Duration(0)
57 for j := 0; j < write_count; j++ {
58 retries, duration := write(db, i, count) //, db2)
59 totalRetries += retries
60 totalDuration += duration
61 }
62 done <- fmt.Sprintf("Finished writing goroutine %d doing %d INSERTs: retries: %d, duration: %s", i, write_count, totalRetries, totalDuration.String())
63 }(i, count)
64 }
65
66 }()
67
68 finished := 0
69 for msg := range done {
70 fmt.Println(msg)
71 finished += 1
72 if finished >= count*2 {
73 break
74 }
75 }
76}
77
78func read(db *sql.DB, i, count int) {
79
80 rows, err := db.Query(`SELECT * FROM users LIMIT 100;`)
81 if err != nil {
82 fmt.Printf("\nusers select %d/%d. Query error=%s\n", i, count, err)
83 } else {
84 rows.Close()
85 }
86
87}
88
89//func write(db *sql.DB, i, count int, db2 *sql.DB) (int, time.Duration) {
90func write(db *sql.DB, i, count int) (int, time.Duration) {
91 const MaxTries = 50
92 retryCount := 0
93 start := time.Now()
94
95Retry:
96 txn, err := db.Begin()
97 if err != nil {
98 switch e := err.(sqlite3.Error); e.Code {
99 case sqlite3.ErrBusy:
100 retryCount += 1
101 if retryCount < MaxTries {
102 <-time.After(1 * time.Second)
103 goto Retry
104 } else {
105 fmt.Printf("\nFAIL ErrBusy. Begin error=%s goroutine %d\n", err, i)
106 }
107 case sqlite3.ErrLocked:
108 fmt.Printf("\nErrLocked. Exec error=%s goroutine %d\n", err, i)
109 default:
110 fmt.Printf("\nuser insert. Exec error=%s goroutine %d\n", err, i)
111
112 }
113
114 } else {
115 result, err := txn.Exec(fmt.Sprintf(`INSERT INTO users (user_name) VALUES ("someuser%d");`, i))
116 if err != nil {
117 switch e := err.(sqlite3.Error); e.Code {
118 case sqlite3.ErrBusy:
119 retryCount += 1
120 if retryCount < MaxTries {
121 <-time.After(1 * time.Second)
122 goto Retry
123 } else {
124 fmt.Printf("\nFAIL ErrBusy. Exec error=%s goroutine %d\n", err, i)
125 }
126 case sqlite3.ErrLocked:
127 fmt.Printf("\nErrLocked. Exec error=%s goroutine %d\n", err, i)
128 default:
129 fmt.Printf("\nuser insert. Exec error=%s goroutine %d\n", err, i)
130 }
131 err = txn.Rollback()
132 if err != nil {
133 fmt.Printf("\nrollback.error=%s goroutine %d\n", err, i)
134 }
135 } else {
136 _, err := result.LastInsertId()
137 if err != nil {
138 fmt.Printf("\nuser writer. LastInsertId error=%s goroutine %d\n", err, i)
139 }
140 read(db, i, count)
141 //read(db2, i, count)
142 err = txn.Commit()
143 if err != nil {
144 fmt.Printf("\ncommit.error=%s goroutine %d\n", err, i)
145 }
146 }
147 }
148 t := time.Now()
149 elapsed := t.Sub(start)
150 return retryCount, elapsed
151
152}
153
154func setup(db *sql.DB) {
155 _, err := db.Exec(setupSql)
156 if err != nil {
157 log.Fatal(err)
158 }
159}