databaselocked3.go

databaselocked3.go

/*
# Filename: databaselocked3.go

# Mysql
DROP DATABASE dbtest;
CREATE DATABASE dbtest;
DROP USER utest@localhost;
CREATE USER utest@localhost IDENTIFIED BY 'ptest2322';
GRANT ALL PRIVILEGES ON dbtest.* TO utest@localhost WITH GRANT OPTION;

# Shell Script
go get
go run databaselocked3.go --dbms=sqlite --initschema
go run databaselocked3.go --dbms=mysql --initschema

go run databaselocked3.go --dbms=sqlite > sqlite.log 2>&1
grep --context=2 --ignore-case  "error" sqlite.log

go run databaselocked3.go --dbms=mysql > mysql.log 2>&1
grep --context=2 --ignore-case  "error" mysql.log
*/

package main

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

var (
	flagDbms       *string
	flagInitSchema *bool

	gDb *sql.DB

	//gDbMaxOpenConns = 250		// SQLite: unlimited connections but starts to fail randomly when #goroutines +- > 200
	gDbMaxOpenConns = 75 // MySQL config: max connections = 100

	//gnRuns = 10
	//gnRuns = 100
	gnRuns = 5000
)

func init() {
	flagDbms = flag.String("dbms", "sqlite", "flag --dbms: [sqlite,mysql]")
	flagInitSchema = flag.Bool("initschema", false, "flag --initschema: create schema and system data")
	flag.Parse()
}
func main() {
	log.Printf("gnRuns = %v\n", gnRuns)
	log.Printf("cliflag dbms: %v\n", *flagDbms)
	log.Printf("cliflag initschema: %v\n", *flagInitSchema)
	if *flagDbms != "sqlite" && *flagDbms != "mysql" {
		log.Fatal("FATAL cliflag dbms: invalid value")

	}

	// catch $kill (except -9)
	chanSignalOsMain := make(chan os.Signal, 1)
	signal.Notify(
		chanSignalOsMain,
		syscall.SIGTERM, syscall.SIGINT, syscall.SIGHUP, syscall.SIGQUIT,
	)
	go func() {
		for sig := range chanSignalOsMain {
			log.Printf("Got signal: %v", sig)
			os.Exit(9)
		}
	}()

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

	//
	myDbConnect()

	//
	if *flagInitSchema {
		myDbAddData()
	}
	myDbTest()

	// PPROF
	if 1 == 0 {
		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 myDbConnect() {
	var (
		err       error
		sSql      string
		sqlResult sql.Result
	)
	_ = sqlResult

	// DB INIT
	if *flagDbms == "sqlite" {
		// 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)
		}

		// SQLITE3 SYSTEM CONFIG
		sSql = "PRAGMA journal_mode=WAL"
		sqlResult, err = gDb.Exec(sSql)
		if err != nil {
			log.Fatal(err)
		}
	}
	if *flagDbms == "mysql" {
		gDb, err = sql.Open("mysql", "utest:ptest2322@/dbtest")
		if err != nil {
			log.Fatal(err)
		}
		err = gDb.Ping()
		if err != nil {
			log.Fatal(err)
		}
	}

	// Default=unlimited...
	gDb.SetMaxOpenConns(gDbMaxOpenConns)
}
func myDbAddData() {
	var (
		err   error
		id    string
		sSql  string
		stmnt *sql.Stmt
	)

	sSql = `DROP TABLE counters`
	_, err = gDb.Exec(sSql)

	if *flagDbms == "sqlite" {
		sSql = `
		CREATE TABLE counters (
			id			VARCHAR NOT NULL PRIMARY KEY,
			description	VARCHAR NOT NULL DEFAULT ''
		)`
	}
	if *flagDbms == "mysql" {
		sSql = `
			CREATE TABLE counters (
				id	        VARCHAR(64)   NOT NULL PRIMARY KEY,
				description VARCHAR(255)  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)
		}
	}

}
func myDbTest() {
	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)
				return // EXIT THE GO FUNC
			}
			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)
				return // EXIT THE GO FUNC
			}
			timeEnd = time.Now()
			log.Printf("Elapsed %v | SQLUPDATE %v\n", timeEnd.Sub(timeBegin), id)
			//
		}(i)
	}

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