golang sqlite database connection pooling

Question

I'm having trouble with SQLite throwing a wrench in my machinery when I call a database write at the same exact time as a read. This happens when different methods happen to attempt to access the database at the same exact time.

What I'm doing is similar to what is being done in this thread, the accepted answer explains how to use database transactions to avoid database locks.

Here is some of my code:

stmt, err := dbtx.Prepare(`statement`)
if err != nil {
    log.Fatal(err)
}

_, err = stmt.Exec(values, values, values)
if err != nil {        
    log.Fatal(err)
}

err = dbtx.Commit()
if err != nil {
    fmt.Println("database lock?")
    fmt.Println(err)
    dbtx.Rollback()
}

fmt.Println("Database storage complete!")

The confusing thing is the program exists after outputting this:

database lock?
database is locked
Database storage complete!
2014/09/09 18:33:11 database is locked
exit status 1

I don't want my program to halt on a database lock, I want it to store the data in memory and continue about its business until the database is unlocked and I can try again.

Is there some standard way I can achieve this, maybe a queue or data structure of some sort, or is there a database-specific way to go about solving this issue?

Why does the program exit after outputting Database storage complete!?

Edit:

I believe I've fixed the problem, but I can't be sure. I'm using goroutines and a package-wide DB connection. Previously, each func within my code was initializing a database connection when it was called. Now, I have a "global" variable for the DB connection defined at the top of the package and initialized before any routines begin. Here's the code in a nutshell:

var nDB *sql.DB

Later in the main func...

mypkg.InitDB()
go mypkg.RunDatabaseOperations()
mypkg.BeginHTTPWatcher(rtr)

InitDB() is defined as the following:

func InitDB() {
    fmt.Println("Init DB ...")
    var err error
    nDB, err = sql.Open("sqlite3", "./first.db")
    if err != nil {
        log.Fatal(err)
    }
    if nDB == nil {
        log.Fatal(err)
    }
    fmt.Printf("nDB: %v\n", ODB)
    fmt.Println("testing db connection...")
    err2 := nDB.Ping()
    if err2 != nil {
        log.Fatalf("Error on opening database connection: %s", err2.Error())
    }
}

So, RunDatabaseOperations scans an online resource for data periodically and stores it into the database when there is a change (once every few seconds). BeginHTTPWatcher listens for HTTP requests so data can be read from the running program and transmitted over-the-wire to the requestor of the data, whether it is a local or external request. I haven't had a problem yet.

Answer 1

The documentation says:

A single connection instance and all of its derived objects (prepared statements, backup operations, etc.) may NOT be used concurrently from multiple goroutines without external synchronization.

(This is a different SQLite driver, but this restriction also applies to yours.)

When you are using goroutines, you must use separate database connections.

By default, SQLite aborts immediately when it encounters a database that is locked by another transaction. To allow more concurrency, you can tell it to wait for the other transaction to finish by setting a busy timeout.

Use the BusyTimeout function, if your SQLite driver has it, or execute the PRAGMA busy_timeout SQL command directly.