go database/sql -- Request and release of connections: exceeding maximum number of connections

APeng Released 19 minutes ago

The database/sql that comes with go has the ability to pool connections. SetMaxOpenConns allows you to configure the maximum number of connections. When the maximum number of connections is exceeded, how is the database/sql handled?

The answer is to put the request in a waiting queue and block it, wake up when a connection is released, and give it the released connection.

Apply for Connection

Application for connection process:

First check if the connection pool has an idle connection, and if so, return to that connection.
Check if the maximum number of connections is exceeded:
- If the maximum connection is exceeded, the request is placed in a waiting queue;
- Otherwise, create a new connection object to return;

// conn returns a newly-opened or cached *driverConn.
func (db *DB) conn(ctx context.Context, strategy connReuseStrategy) (*driverConn, error) {
   ......
   //Check the connection pool for idle connections first, and if so, return the conn
   ......

   //If the maximum number of connections is exceeded, place the connection request in the waiting queue and block it
   if db.maxOpen > 0 && db.numOpen >= db.maxOpen {
      req := make(chan connRequest, 1)
      reqKey := db.nextRequestKeyLocked()
      db.connRequests[reqKey] = req
      db.waitCount++
      
      select {
      case <-ctx.Done():
         ......
      case ret, ok := <-req:
         ......
         return ret.conn, ret.err
      }
   }

   //Create a new connection object and return
   ......
}

Focus on behaviors that exceed the maximum number of connections, placing connection requests in the waiting queue connRequests:map[unit64]chan connRequest type, key is unit64, value is Chan connRequest, the logic for requesting a map:

req := make(chan connRequest, 1)
reqKey := db.nextRequestKeyLocked()
db.connRequests[reqKey] = req
db.waitCount++

//reqKey is an incremental integer value
func (db *DB) nextRequestKeyLocked() uint64 {
   next := db.nextRequest
   db.nextRequest++
   return next
}

In addition to adding requests to the map, use select to block requests:

// Timeout the connection request with the context.
select {
    //cxt.Done()
    case <-ctx.Done():
        ......
    //req is chann connRequest, which means that if there is data on the channel, the connection is returned
    case ret, ok := <-req:
        return ret.conn, ret.err
}

This blocked select waits for data on the req channel.

When a connection is released, data is placed on the channel, which is no longer blocked and can be moved down.

Release Connection

The entry function is releaseConn:

func (dc *driverConn) releaseConn(err error) {
   dc.db.putConn(dc, err, true)
}

func (db *DB) putConn(dc *driverConn, err error, resetSession bool) {
    ......
    dc.inUse = false    
    added := db.putConnDBLocked(dc, nil)    //Release Connection
    db.mu.Unlock()
    // Close dc if not added to idle list
    if !added {
        dc.Close()
    }
}

The logic to release connections is in db.putConnDBLocked:

First check if there are any blocked requests in the connection waiting queue;
If so, give it the connection, send data to the channel, and the blocked connection request gets connected.
Otherwise, place the connection in the freeConn connection pool;

func (db *DB) putConnDBLocked(dc *driverConn, err error) bool {
    //There are blocked waiting requests in the waiting queue
    if c := len(db.connRequests); c > 0 {        
        var req chan connRequest
        var reqKey uint64
        for reqKey, req = range db.connRequests {
            break
        }
        delete(db.connRequests, reqKey)     //Delete the request
        //Give the request a piece of data, that is, put the data on the channel so that the blocked request can proceed
        req <- connRequest{
            conn: dc,
            err:  err,
        }
        return true
    } else if err == nil && !db.closed {    //Put in idle connection pool
        if db.maxIdleConnsLocked() > len(db.freeConn) {
            db.freeConn = append(db.freeConn, dc)
            db.startCleanerLocked()
            return true
        }
        db.maxIdleClosed++
       }
    ......
}

golang

mysql