proposal: database/sql: make free connection strategy configurable about go HOT 3 OPEN

CC @kardianos

from go.

@kishoresenji I'm skeptical. However, if you provide a proof of concept or sketch that shows this could be reasonably extracted from the current code base, I'd be game to re-evaluate. If the connection use strategy was able to be extracted to some internal interface, and the type is used cleanly throughout, then perhaps this could clean up existing code.

However, if any implementation resulted in more complicated or harder to understand logic surrounding this, that would be an instant no.

from go.

@kardianos As the freeConn primitive in both strategies (most recently used or least recently used) is the same (i.e both strategies will order from oldest to newest on returedAt), we can keep it very simple and not add a new interface. The only place that needs a change is in the conn method on how the free connection is picked. There should not be any change anywhere else in the code.

We can have a new enum which models which strategy to be used.

type cachedConnPickStrategy uint8
const (
	mostRecentlyUsedStrategy cachedConnPickStrategy = iota
	leastRecentlyUsedStrategy
)

and LeastRecentlyUsed strategy can be enabled with an option on the DB. By default, it will be MostRecentlyUsed strategy.

func (db *DB) SetLeastRecentlyUsedCachedConnectionStrategy() {
	db.mu.Lock()
	defer db.mu.Unlock()
	db.cachedConnPickStrategy = leastRecentlyUsedStrategy
}

The conn method would change like this:

	// Prefer a free connection, if possible.
	if strategy == cachedOrNewConn {
		if conn := db.getCachedConnDBLocked(); conn != nil {
			conn.inUse = true
			if conn.expired(lifetime) {
				db.maxLifetimeClosed++
				db.mu.Unlock()
				conn.Close()
				return nil, driver.ErrBadConn
			}
			db.mu.Unlock()

			// Reset the session if required.
			if err := conn.resetSession(ctx); errors.Is(err, driver.ErrBadConn) {
				conn.Close()
				return nil, err
			}

			return conn, nil
		}
	}

where getCachedConnDBLocked is like the following:

func (db *DB) getCachedConnDBLocked() *driverConn {
	var conn *driverConn
	if len(db.freeConn) == 0 {
		return conn
	}
	switch db.cachedConnPickStrategy {
	case leastRecentlyUsedStrategy:
		conn = db.freeConn[0]
		db.freeConn = db.freeConn[1:]
	default:
		last := len(db.freeConn)-1
		conn = db.freeConn[last]
		db.freeConn = db.freeConn[:last]
	}
	return conn
}

from go.