database/sql: Ability to timeout when wating for the connection from the pool

[pavelsmejkal]

Issue is partially related to #8874.

We are facing a problem under heavy load when the connection pool is full and lots of connection requests are in internal database/sql queue.

The result is that all go routines that suppose to handle queries stacks and accumulates on the background. Query time itself can be at least controlled by driver specific statement timeout. But nothing controls connection queue. So if you have enough clients, query can be executed few seconds / minutes after client hits API. Obviously for no reason since API already returned timeout. Support for go context would be the best for query and connections but it would require lot of invasive changes, i guess.

So my proposal is to have poolTimeout.

type DB struct {
    ....
    var poolTimeout time.Duration
}

func (db *DB) SetPoolTimeout(t time.Duration) {
    db.mu.Lock()
    db.poolTimeout = t
    if t < 0 {
        db.poolTimeout = 0
    }
    db.mu.Unlock()
}

// and change function
func (db *DB) conn(strategy connReuseStrategy) (*driverConn, error) {
...

//from 
req := make(chan connRequest, 1)
db.connRequests = append(db.connRequests, req)
db.mu.Unlock()
ret := <-req

//to 
req := make(chan connRequest, 1)
db.connRequests = append(db.connRequests, req)
db.mu.Unlock()
if db.poolTimeout == 0 {
  ret := <-req
} else {
  select {
  case ret := <-req:
    return ret.conn, ret.err
  case <- time.After(db.poolTimeout):
    go func(result chan connRequest) {
        c := <- result
        if !db.closed {
            db.putConn(c.conn, nil)
        }
    }(req)
    return nil, driver.ErrPoolTimeout
  }
}

[redfoxli]

Yes, I face this problem too, when I use SetMaxOpenConns.
Your solution is a good way.
I think there is a small place to be able to modify

db.putConn(c.conn, nil)
----> 
db.putConn(c.conn, c.err)

[TursMA]

the same issue with SetMaxOpenConns. I'll be nice to add poolTimeout per each query.

[kardianos]

This can be solved with the addition of Context #15123 and should probably be merged in.

[bradfitz]

Closing in favor of #15123