sqlite_concurrent_demo/sqlite_concurrent_demo.go

package main

import (
	"database/sql"
	"log"
	"os"
	"strconv"
	"sync"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

const (
	File = "demo.db"
	// DSN points to a local DB file without cache enabled.
	DSN = "file:" + File
)

func setup() {
	_ = os.Remove(File)
	db, err := sql.Open("sqlite3", DSN)
	if err != nil {
		log.Fatalf("open: %v", err)
	}

	defer func() {
		_ = db.Close()
		log.Println("finishing setup")
	}()
	if _, err = db.Exec(`
CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    description VARCHAR(255)
);
`); err != nil {
		log.Fatalf("creating schema: %v", err)
	}
}

func ping(wg *sync.WaitGroup, id int, start, linger time.Duration) {
	logger := log.New(os.Stderr, "("+strconv.Itoa(id)+") ", log.LstdFlags)
	defer func() {
		wg.Done()
	}()
	time.Sleep(start)
	db, err := sql.Open("sqlite3", DSN)
	if err != nil {
		logger.Fatalf("open: %v", err)
	}
	logger.Println("open: OK")
	res, err := db.Exec(`
INSERT INTO foo(id, description)
VALUES (?, ?)
	`, id, strconv.Itoa(id))
	if err != nil {
		logger.Fatalf("insert: %v", err)
	} else {
		ra, _ := res.RowsAffected()
		last, _ := res.LastInsertId()
		logger.Printf("inserted count %d / last %d", ra, last)
	}
	row := db.QueryRow(`
SELECT COUNT(id)
FROM foo
	`)
	count := 0
	if err = row.Scan(&count); err != nil {
		logger.Fatalf("scanning: %v", err)
	}
	logger.Printf("select %d rows: OK", count)
	time.Sleep(linger)

	if err := db.Close(); err != nil {
		logger.Fatalf("close: %v", err)
	}
	logger.Println("close: OK")
}

func main() {
	setup()
	wg := sync.WaitGroup{}
	wg.Add(2)
	go ping(&wg, 1, 0, 2*time.Second)
	go ping(&wg, 2, time.Second, 0)
	wg.Wait()
	log.Println("all done")
}