Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Program that tests the concurrency issues with go-sqlite3. This will create two tables: `products` and `users`. One goroutine will repeatedly read from the `products` table in N fresh goroutines. At the same time ONE goroutine writes to the other table.
package main
import (
"database/sql"
"fmt"
"log"
"math/rand"
"sync"
"time"
_ "github.com/mattn/go-sqlite3"
)
const (
setupSql = `
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, user_name TEXT);
CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, product_name TEXT);
DELETE FROM products;
`
count = 5000
)
var r *rand.Rand
func init() {
r = rand.New(rand.NewSource(time.Now().UnixNano()))
}
func main() {
db, err := sql.Open("sqlite3", "database_file.sqlite")
if err != nil {
log.Fatal("could not open sqlite3 database file", err)
}
defer db.Close()
// db.SetMaxOpenConns(1)
done := make(chan struct{})
mu := &sync.Mutex{}
go func() {
// writes to users table
for i := 0; i < count; i++ {
write(db, mu, i, count)
randomSleep()
}
done <- struct{}{}
}()
go func() {
// reads from products table, each read in separate go routine
for i := 0; i < count; i++ {
go func(i, count int) {
read(db, mu, i, count)
done <- struct{}{}
}(i, count)
randomSleep()
}
}()
for i := 0; i < count+1; i++ {
<-done
}
}
func randomSleep() {
time.Sleep(time.Duration(r.Intn(5)) * time.Millisecond)
}
func setup(db *sql.DB) {
_, err := db.Exec(setupSql)
if err != nil {
log.Fatal(err)
}
for i := 0; i < 1000; i++ {
_, err := db.Exec(`INSERT INTO products (product_name) VALUES ("computer");`)
if err != nil {
log.Fatalf("filling up products table failed. Exec error=%s", err)
}
}
}
func read(db *sql.DB, mu *sync.Mutex, i, count int) {
// mu.Lock()
// defer mu.Unlock()
rows, err := db.Query(`SELECT * FROM products WHERE id = 5`)
if err != nil {
fmt.Printf("\nproducts select %d/%d. Query error=%s\n", i, count, err)
} else {
rows.Close()
}
fmt.Printf(".")
}
func write(db *sql.DB, mu *sync.Mutex, i, count int) {
// mu.Lock()
// defer mu.Unlock()
result, err := db.Exec(`INSERT INTO users (user_name) VALUES ("Bobby");`)
if err != nil {
fmt.Printf("user insert. Exec error=%s", err)
return
}
_, err = result.LastInsertId()
if err != nil {
fmt.Printf("user writer. LastInsertId error=%s", err)
}
fmt.Printf("+")
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment