sqlite_concurrent_demo/sqlite_concurrent_demo.go

package main

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

	_ "github.com/mattn/go-sqlite3"
	"golang.org/x/sys/unix"
)

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)
	f, err := os.Open(File)
	if err != nil {
		logger.Fatalf("file open: %v", err)
	}
	lock := unix.Flock_t{
		Start:  0,
		Len:    0,
		Pid:    int32(os.Getpid()),
		Type:   syscall.F_WRLCK,
		Whence: io.SeekStart,
	}
	for {
		if err := unix.FcntlFlock(f.Fd(), unix.F_GETLK, &lock); err != nil {
			logger.Fatalf("file get lock info: %v", err)
		}
		// if err := unix.Flock(int(f.Fd()), unix.LOCK_SH); err != nil {
		// 	logger.Fatalf("file lock: %v", err)
		// }
		if int(lock.Type) == syscall.F_UNLCK {
			logger.Println("locked DB")
			break
		}
		logger.Println("attempted to locked DB, but it was locked")
		time.Sleep(100 * time.Millisecond)
	}
	defer func() {
		if err := unix.FcntlFlock(f.Fd(), unix.F_SETLK, &lock); err != nil {
			logger.Fatalf("file unlock: %v", err)
		}
		logger.Println("unlocked DB")
	}()

	db, err := sql.Open("sqlite3", DSN)
	if err != nil {
		logger.Fatalf("sql open: %v", err)
	}
	defer db.Close()

	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")
}