database/sql: improve recovery from server timeouts

[gopherbot]

by arnehormann:

database/sql attempts to issue queries 10 times (value of maxBadConnRetries) until it
succeeds.

When all connections are idle for longer than some server timeout, the network
connections in the whole connection pool may have been closed by the server.
If 10 database connections are tried and all have been closed by the server, the query
will return an error.

Recovery is still possible by recreating the connections, but the number of retries
should be larger than the pool size (e.g. 1 + db.maxOpen).

[ianlancetaylor]

Comment 1:

Labels changed: added repo-main, release-go1.5.

[gopherbot]

Comment 2 by lperjiao:

should the number of retries be 1+db.MaxIdle ?

[gopherbot]

Comment 3 by arnehormann:

Probably. And that's probably a lot better than db.maxOpen.

[gopherbot]

Comment 4 by marko@joh.to:

I've implemented what I think is a cleaner approach in
https://golang.org/cl/168060044.  It essentially makes maxBadConnRetries the
maximum number of bad connections to discard from the pool per query, and then forces an
attempt with a fresh connection after maxBadConnRetries.

[gopherbot]

Comment 5 by arnehormann:

Neat idea!
That probably also helps to integrate deadlines in database/sql (to support
code.google.com/p/go.net/context - as proposed in 
https://golang.org/issue/8874).
And I think maxBadConnRetries should be 1, then.

[xiezhenye]

Why maxBadConnRetries is hard coded but not a configurable option? Sometimes I do not want it to retry when connect fails.

[johto]

Why maxBadConnRetries is hard coded but not a configurable option? Sometimes I do not want it to retry when connect fails.

maxBadConnRetries isn't really about connection attempts; I'd expect a driver not to return ErrBadConn on a failed connection attempt, because it hides the error message from the user. If the user does want to retry, he/she can do it in the call outside of database/sql. Further, setting maxBadConnRetries to 0 would mean that database/sql would stop cleaning up bad connections from the pool, which doesn't sound reasonable at all.

What's the actual use case here?

[xiezhenye]

Reties may seldom success when first attempt fails, and the latency time will be longer (e.g. ReadTimeout * 3).

when maxBadConnRetrie set to 0, connection pool will actually be ignored, and maxIdleConn should also set to 0.

p.s.
recently, maxBadConnRetries can not be simply set to 0. I've write a patch to implement a method SetMaxBadConnRetries on *DB.

see https://golang.org/cl/22968