Using sqlx + sqlite in goroutines with GOMAXPROCS > 1

[marbemac]

Hi, I'm trying to use sqlx in a non-blocking manner in a http.Handler. To do this I put the sqlx code in a goroutine. Everything works OK until I set GOMAXPROCS > 1, when it starts panicking under load.

This code errors when you throw a decent number of requests at it:
https://play.golang.org/p/JzdcbvkrvU

Bench the server with wrk or similar

wrk -c10 -d5 http://localhost:3000/sql

I'm relatively new to go, and could be missing some obvious no-no with GOMAXPROCS and goroutines. If anybody could point me in the right direction I'd appreciate it.

The errors look like this:

goroutine 2031 [runnable, locked to thread]:
github.com/mattn/go-sqlite3._Cfunc__sqlite3_open_v2(0x53b4330, 0xc20cd86a40, 0x10006, 0x0, 0x0)
    github.com/mattn/go-sqlite3/_obj/_cgo_gotypes.go:106 +0x43
github.com/mattn/go-sqlite3.(*SQLiteDriver).Open(0xc20801e020, 0x4429510, 0x42, 0x0, 0x0, 0x0, 0x0)
    /Users/Marc/gocode/src/github.com/mattn/go-sqlite3/sqlite3.go:269 +0x20d
database/sql.(*DB).conn(0xc20805e1e0, 0x0, 0x0, 0x0)
    /usr/local/Cellar/go/1.4/libexec/src/database/sql/sql.go:664 +0x506
database/sql.(*DB).query(0xc20805e1e0, 0x4407c50, 0x20, 0xc20b075700, 0x2, 0x2, 0x0, 0x0, 0x0)
    /usr/local/Cellar/go/1.4/libexec/src/database/sql/sql.go:933 +0x43
database/sql.(*DB).Query(0xc20805e1e0, 0x4407c50, 0x20, 0xc20b075700, 0x2, 0x2, 0x200000000000000, 0x0, 0x0)
    /usr/local/Cellar/go/1.4/libexec/src/database/sql/sql.go:924 +0xa6
github.com/jmoiron/sqlx.(*DB).QueryRowx(0xc20803aab0, 0x4407c50, 0x20, 0xc20b075700, 0x2, 0x2, 0xc20b075700)
    /Users/Marc/gocode/src/github.com/jmoiron/sqlx/sqlx.go:335 +0x69
github.com/jmoiron/sqlx.Get(0x47142e0, 0xc20803aab0, 0x42d7fc0, 0xc209e0b500, 0x4407c50, 0x20, 0xc20b075700, 0x2, 0x2, 0x0, ...)
    /Users/Marc/gocode/src/github.com/jmoiron/sqlx/sqlx.go:637 +0x7b
github.com/jmoiron/sqlx.(*DB).Get(0xc20803aab0, 0x42d7fc0, 0xc209e0b500, 0x4407c50, 0x20, 0xc20b075700, 0x2, 0x2, 0x0, 0x0)
    /Users/Marc/gocode/src/github.com/jmoiron/sqlx/sqlx.go:302 +0xc6
main.func·001()
    /Users/Marc/gocode/request-mirror.go:46 +0x1c4
created by main.(*handler).sqlHandler
    /Users/Marc/gocode/request-mirror.go:47 +0x90

[tristanwietsma]

See mattn/go-sqlite3#51

Maybe a proof-of-concept with using standard sql.DB would be worthwhile?

[jszwedko]

Indeed, I'd try database/sql to try to isolate the problem -- my guess is it is the driver when looking at that stack trace.

[jmoiron]

Hi marbemac,

I suspect this is related to sqlite3 or the sqlite3 driver. A quick examination of the sqlite3 driver shows that it's using the SQLITE_OPEN_FULLMUTEX open mode (which opens it in serialized threaded mode) and that it also checks that sqlite was compiled in multi-threaded mode, so the kind of obvious looking bug doesn't seem to be present.

• did you let the go sqlite3 driver create this db, or did something else?
• Is the actual error a SIGSEGV from the C code? it's not in your traceback

It is possible that sqlx is somehow misusing database/sql and causing this error, but I can't think of a way it might be doing that.

[marbemac]

I use sqlx to open the DB, but you're correct in that this might be a sqlite3 specific issue. I've generalized the example code a bit more so that you can give it a shot when you have a second:
https://play.golang.org/p/2w8nWrwV1u

If you run the server in that example and then use apache bench or similar it errors out.

ab -n 10000 -c10 http://127.0.0.1:3000/

[aronatkins]

The linked test program from https://play.golang.org/p/2w8nWrwV1u is just spawning goroutines without any attempt at limiting concurrency. This ends up calling sqlite C code, which needs real threads. The initial error output (running with Go 1.8.1 on macOS 10.12.4) is:

runtime/cgo: pthread_create failed: Resource temporarily unavailable
SIGABRT: abort

The reported goroutine 2031 [syscall, locked to thread]: stacks come later. It's making some C call and that thread cannot service other goroutines until it exits. You run out of threads and everything blows up.

A similar discussion:
https://groups.google.com/d/msg/golang-nuts/8gszDBRZh_4/Jj3pfIdrutYJ

You can avoid this issue by not permitting unlimited concurrency against the database (sqlite), which constrains the number of sqlite C calls.

db.SetMaxOpenConns(42)

I agree that there's nothing sqlx can do here.