gistfile1.go

package main

import (
	"database/sql"
	"fmt"
	"os"
	"time"

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

func IsDbLockedError(err error) bool {
	if err == nil {
		return false
	}
	if err == sqlite3.ErrLocked || err == sqlite3.ErrBusy {
		return true
	}
	if err.Error() == "database is locked" {
		return true
	}
	return false
}

func DbQuery(db *sql.DB, q string, args ...interface{}) (*sql.Rows, error) {
	slept := time.Millisecond * 0
	for {
		result, err := db.Query(q, args...)
		if err == nil {
			return result, nil
		}
		result.Close()
		if !IsDbLockedError(err) {
			fmt.Printf("DbQuery: query %q error %q\n", q, err)
			return nil, err
		}
		if slept == 30*time.Second {
			fmt.Printf("DB Locked for 30 seconds\n")
			return nil, err
		}
		time.Sleep(100 * time.Millisecond)
		slept = slept + 100*time.Millisecond
	}
}

func DbExec(db *sql.DB, q string, args ...interface{}) (sql.Result, error) {
	slept := time.Millisecond * 0
	for {
		result, err := db.Exec(q, args...)
		if err == nil {
			return result, nil
		}
		if !IsDbLockedError(err) {
			fmt.Printf("DbExec: query %q error %q\n", q, err)
			return nil, err
		}
		if slept == 30*time.Second {
			fmt.Printf("DB Locked for 30 seconds\n")
			return nil, err
		}
		time.Sleep(100 * time.Millisecond)
		slept = slept + 100*time.Millisecond
	}
}

func main() {
	os.Remove("/tmp/x1")
	db, err := sql.Open("sqlite3", "/tmp/x1")
	if err != nil {
		fmt.Printf("Error creating database: %s\n", err)
		return
	}
	stmt := `
	CREATE TABLE certificates (
		id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
		fingerprint int
	);`
	_, err = db.Exec(stmt);
	if err != nil {
		fmt.Printf("%q\n", err)
		return
	}

	fp := "Hi there, how are you?"
	_, err = db.Exec("insert into certificates (fingerprint) values (?);", fp)
	if err != nil {
		fmt.Printf("main: insert gave error %q\n", err)
		return
	}
	const nthreads int = 100

	c := make(chan bool, nthreads)
	done := make(chan bool, nthreads)
	for i := 0; i < nthreads; i++ {
		me := i
		go func() {
			<-c
			fp2 := fmt.Sprintf("Hi there, how are you? I am %d", me)
			_, err = DbExec(db, "insert into certificates (fingerprint) values (?);", fp2)
			if err != nil {
				fmt.Printf("thread%d: insert gave error %q\n", me, err)
				done <- true
				return
			}
			rows, err := DbQuery(db, "select id from certificates where fingerprint=?", fp)
			if err != nil {
				fmt.Printf("thread%d: query gave error %qn", me, err)
			}
			defer rows.Close()
			id := -1
			for rows.Next() {
				var idx int
				err = rows.Scan(&idx)
				if err != nil {
					fmt.Printf("thread%d: scan error %qn", me, err)
				}
				id = idx
				if id == -1 {
					fmt.Printf("thread%d: query return -1\n", me)
				}
			}
			if id == -1 {
				fmt.Printf("thread%d: query gave no error, but results are empty\n", me)
			}
			done <- true
			return
		}()
	}
	for i := 0; i < nthreads; i++ {
		c <- true
	}
	for i := 0; i < nthreads; i++ {
		<-done
	}
	fmt.Printf("done\n")
}