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.

`2014/09/09 18:33:11 database is locked` looks like `log.Fatal(err)` work. Can you provide more information, to make test sample? Which sqlite provider you use? — RoninDev, Sep 10 '14 at 09:06
Seems like a concurrency problem... are you running this code concurrently? If so, please post more of your code so we can see what's going on. — gabe., Sep 11 '14 at 16:07
According to your accepted answer, did you change your goroutine RunDatabaseOperations() to establish a separate DB connection instead of using your global package-wide one? — Strubbl, Nov 03 '15 at 11:49
I'm using the global package-wide connection, but each routine creates a new transaction with `.Begin()`. Also, as per mattn's own suggestion, in the `InitDB` function I am now opening the database with the following extra parameters: `nDB, err = sql.Open("sqlite3", "./first.db?cache=shared&mode=wrc")`. — bvpx, Nov 03 '15 at 19:45

score 8 · Accepted Answer · answered Oct 03 '14 at 07:16

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.

score 0 · Answer 2 · answered Sep 24 '14 at 03:42

Please post more of your code, so that we can get a fuller idea of what's going down.

However, here are a couple of thoughts. Golang pools db connections by default (although, CENTOS seems to perhaps not..). Also, your program is 'halting' because it's waiting for an open connection from the db connection pool. If you want the rest of your program to continue during this time, you should run this as an asynchronous function - check out goroutines here. That will effectively cause your program to queue, as you want it to, since connections will be assigned in the order that they were requested whenever they become available. Read more over here if you're interested in the internals.

If you need some code snippets for how your goroutine might look, let us know.

I've updated my original post with the code I'm using now, and everything seems to be working fine, but I am unsure if I'm using goroutines and a package-wide DB connection correctly or not. — bvpx, Sep 25 '14 at 18:44

golang sqlite database connection pooling

2 Answers2