Provide DB level Read/Write timeout settings.

[sjmudd]

Issue description

The dsns used to connect to a server have a new optional readTimeout and writeTimeout parameter. This assumes that all queries want these values to be applied when in real life some queries may need to use a different value.

It would be convenient to have a mechanism to set these values at the query level, that is prior to db.Query(), db.Exec() calls, so maybe as part of the DB layer, in a call similar to the existing:

func (db *DB) SetConnMaxLifetime(d time.Duration)

Context

Applications like orchestrator (https://github.com/outbrain/orchestrator) need to connect frequently to a number of MySQL servers to monitor them or to do maintenance on them. In this case the number of servers being monitored could potentially be high.

I am already using the DSN timeout values but the problem is the single connections to a monitored server may try to do two different things:
(a) monitoring: check the server is reachable , get some status information from it. In cases like this the short timeouts are good as the server may sometimes not respond properly or even at all and it's better to find out fast.
(b) maintenance operations on a server may require reconfiguring it, changing settings and running tasks that if not completed correctly could break the server. Consequently a longer timeout is acceptable and probably better to avoid issues.

(a) and (b) have conflicting requirements and currently require me to setup different pools to the same server using different timeouts in the dsn. That's not efficient and makes coding more complex when a large number of connections are being used.

Thus moving this "timeout" layer if possible to the DB connection seems like a better solution.

Example interface

Suggestion: consider adding an interface similar to:

func (db *DB) SetReadTimeout(d time.Duration)
func (db *DB) SetWriteTimeout(d time.Duration)

Other ways to address this same problem would also be fine.

[methane]

I think this issue should be in Go's issue tracker.

Pesonally speaking, I don't think write_timeout is useful enough. Adding context to each APIs is better.
e.g. func (db *DB) QueryCtx(ctx context.Context, query string, args ...interface{}) (*Rows, error)

[dgryski]

golang/go#15123

[methane]

This issue seems be superseded by #496.
Can I close this?

[methane]

I close this, in faver of context support.
Feel free to add comment with realistic and common example which isn't happy with context.