database/sql: no good way to set execution deadlines

[gopherbot]

by wmorgan.masanjin:

There's currently (circa go 1.3.3) no good way to set execution deadlines on database
operations as provided by database/sql. Systems that e.g. use go.net/context to
represent end-to-end request deadlines must either stop enforcing deadlines at the
database layer, or make use of system-specific hacks.

For example, a system that uses go.net/context for requests that are backed by database
operations with database/sql might have a function like this:

  func getName(ctx context.Context, db *sql.DB, id int) (name string, err error) { 
    err = db.QueryRow("select name from things where id=? limit 1", id).Scan(&name)
    return
  }

Note that ctx is ignored in this implementation. With the current database/sql API,
there are at least two ways of making use of it, but they both have problems:

1. Run QueryRow() in a goroutine, emit the results to a channel, and select() across
that channel and ctx.Done(). This would bound the execution time of the function, but
would leave the query dangling on the database side, where it would continue to consume
database resources.

2. Wrap every query in a transaction and execute a system-specific timeout command prior
to the query as part of the transaction. For example, Postgres allows setting timeouts
on a per-transaction basis via a 'set local statement_timeout' command. This approach
allows database-side timeouts, but necessitates the overhead of adding a transaction to
every query, which may be significant.

Some alternatives that are not currently possible, but which would allow for the desired
behavior:

3. An alternative (parallel?) API for database/sql that makes use of go.net/context
directly, and requires drivers to implement these constraints when supported by the
underlying database.

4. Additional APIs for exclusive access to individual connections outside of
transactions. This would allow setting per-connection timeouts (and other settings) on
databases that support it, at the application level, without incurring the overhead of
transactions.

5. Something else?

See https://groups.google.com/forum/#!topic/golang-nuts/c7gfPQ8gIvM for some discussion.

[ianlancetaylor]

Comment 1:

Labels changed: added repo-main, release-none.

[c4milo]

In the mailing list was mentioned that this could probably make it into Go 1.5, does that still hold true?

[johto]

I wrote a CL: https://go-review.googlesource.com/#/c/2217/, but it's sort of waiting for my other patches go through first, and those are waiting on bradfitz, who's anxiously waiting for his vacation to end. I can't comment on its chances of getting into 1.5, but I'm certainly willing to put in the work to get it there.

[rajeshucsb]

@johto this is really useful feature, is there any way of getting your CL checked in.

[kardianos]

This should be merged into #15123

[bradfitz]

Indeed, done.