gistfile1.go

/*
# Filename: databaselocked2.go

# Script
go get
go run databaselocked2.go --addtestdata
go run databaselocked2.go --updates
go run databaselocked2.go --updates > updates.log 2>&1
grep --context=2 --ignore-case  "sqlite3.Error" updates.log 
*/

package main

import (
	"database/sql"
	"flag"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"os"
	"runtime/pprof"
	"strconv"
	"sync"
	"time"
)

var (
	flagSelects, flagUpdates, flagAddTestData *bool

	gDb *sql.DB
	//gDbMaxOpenConns = 2

	//gnRuns = 10
	//gnRuns = 750
	gnRuns = 5000
)

func init() {
	flagAddTestData = flag.Bool("addtestdata", false, "flag --addtestdata: only add the test data")
	flagUpdates = flag.Bool("updates", false, "flag --updates: only do the sql updates")
	flag.Parse()
}
func main() {
	log.Printf("cliflag addtestdata: %v\n", *flagAddTestData)
	log.Printf("cliflag updates: %v\n", *flagUpdates)

	// PPROF
	log.Printf("Start CPU Profiling.\n")
	fc, err := os.Create("cpuprofile.pprofdata")
	if err != nil {
		log.Fatal(err)
	}
	pprof.StartCPUProfile(fc)

	// DB INIT
	// Original Open() of the test case (sql error "database is locked").
	//   gDb, err = sql.Open("sqlite3","databaselocked.sqlite")
	// FIX from mattn:
	gDb, err = sql.Open("sqlite3", "file:databaselocked.sqlite?cache=shared&mode=rwc")

	if err != nil {
		log.Fatal(err)
	}
	err = gDb.Ping()
	if err != nil {
		log.Fatal(err)
	}

	// Default=unlimited...
	//gDb.SetMaxOpenConns(gDbMaxOpenConns)

	//
	if *flagAddTestData {
		myDbAddTestData()
	}
	if *flagUpdates {
		myDbUpdates()
	}

	// PPROF
	log.Printf("Stop CPU Profile.\n")
	pprof.StopCPUProfile()
	log.Printf("Take Heap Profile.\n")
	fh, err := os.Create("heapprofile.pprofdata")
	if err != nil {
		log.Fatal(err)
	}
	pprof.WriteHeapProfile(fh)
	fh.Close()
}
func myDbAddTestData() {
	var (
		err   error
		id    string
		sSql  string
		stmnt *sql.Stmt
		wg    sync.WaitGroup
	)

	sSql = `DROP TABLE counters`
	_, err = gDb.Exec(sSql)
	sSql = `
		CREATE TABLE counters (
			id			TEXT NOT NULL PRIMARY KEY,
			description	TEXT NOT NULL DEFAULT ''
		)`
	_, err = gDb.Exec(sSql)
	if err != nil {
		log.Fatal(err)
	}

	sSql = `
		INSERT INTO counters (
			id,description
		) VALUES (
			'festivalcounter','initial description'
		)`
	_, err = gDb.Exec(sSql)
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("\nLOOP inserting %v rows (sequentially)\n", gnRuns)
	sSql = `
		INSERT INTO counters (
			id,description
		) VALUES(
			?,?
		)`
	stmnt, err = gDb.Prepare(sSql)
	for i := 0; i < gnRuns; i++ {
		log.Printf("Row %v \n", i+1)
		id = "fcl-0" + strconv.Itoa(i)
		_, err = stmnt.Exec(id, "initial description "+id)
		if err != nil {
			log.Fatal(err)
		}
	}
	// Wait for all go routines to complete.
	wg.Wait()

}
func myDbUpdates() {
	var wg sync.WaitGroup

	for i := 0; i < gnRuns; i++ {
		wg.Add(1)
		go func(myI int) {
			var (
				err             error
				id, description string
				sSql            string
				//sqlResult sql.Result
				sqlStmnt           *sql.Stmt
				timeBegin, timeEnd time.Time
			)
			defer wg.Done()
			//
			timeBegin = time.Now()
			id = "fcl-0" + strconv.Itoa(myI)
			sSql = "SELECT description FROM counters WHERE id=? LIMIT 1"
			err = gDb.QueryRow(sSql, id).Scan(&description)
			if err != nil {
				log.Printf("ERROR SQLSELECT gDb.QueryRow(): %#v | %v | %v\n", err, id, sSql)
			}
			timeEnd = time.Now()
			log.Printf("Elapsed %v | SQLSELECT %v \n", timeEnd.Sub(timeBegin), id)
			//
			timeBegin = time.Now()
			id = "fcl-0" + strconv.Itoa(myI)
			description = "updated " + id + time.Now().String()
			sSql = "UPDATE counters SET description=? WHERE id=?"
			sqlStmnt, err = gDb.Prepare(sSql)
			if err != nil {
				log.Printf("ERROR SQLUPDATE gDb.Prepare(): %#v | %v | %v \n", err, id, description)
				return // EXIT THE GO FUNC
			}
			_, err = sqlStmnt.Exec(description, id)
			if err != nil {
				log.Printf("ERROR SQLUPDATE sqlStmnt.Exec(): %#v | %v | %v \n", err, id, description)
			}
			timeEnd = time.Now()
			log.Printf("Elapsed %v | SQLUPDATE %v\n", timeEnd.Sub(timeBegin), id)
			//
		}(i)
	}

	// Wait for all goroutines to complete.
	wg.Wait()
}