go database/sql -- connection application and release

a Peng Released today at 20:50

database/sql is the operation sql Library of go. It maintains the sql connection pool, including connection application and release.

Connection pool

datebase/sql maintains the connection pool. Its configuration:

db.SetMaxIdleConns(10)        //Set the maximum number of idle connections in the free connection pool
db.SetMaxOpenConns(100)       //Set the maximum number of database connections opened
db.SetConnMaxLifetime(time.Hour)    //Sets the maximum time for reusable connections

Sample program for operating MySQL:

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, _ := db.Open("mysql", "root:rootroot@/dqm?charset=utf8&parseTime=True&loc=Local")    
    defer db.Close()
    db.SetMaxOpenConns(10)
    if err := db.Ping(); err != nil {
        fmt.Println("connect to MySQL failed, err:", err)
        return
    }
    rows, err := db.Query("select * from test where name = 'jackie' limit 10")
    if err != nil {
        fmt.Println("query error")
    }
    defer rows.Close()
    for rows.Next() {
        fmt.Println("close")
    }
    row, _ := db.Query("select * from test")
    fmt.Println(row, rows)
}

db.Open("MySQL", dsn) does not really connect to MySQL, nor does it verify the database user name / password, but only the dsn format.

The connection is only established when Ping() or the actual Query() operation is performed.

Therefore, in the project, in addition to Open() and Ping() during InitDB() to confirm that the connection is OK.

Using MySQL

database/sql is used to operate the CRUD of the database. When operating mysql, the driver is used:

import (    
    _ "github.com/go-sql-driver/mysql"
)

It will automatically register the mysql driver:

//github.com/go-sql-driver/mysql/driver.go
func init() {
    sql.Register("mysql", &MySQLDriver{})
}

Source code analysis:

Initialize DB

// Open may just validate its arguments without creating a connection
// to the database. To verify that the data source name is valid, call
// Ping.
func Open(driverName, dataSourceName string) (*DB, error) {
   driversMu.RLock()
   driveri, ok := drivers[driverName]
   driversMu.RUnlock()
   if !ok {
      return nil, fmt.Errorf("sql: unknown driver %q (forgotten import?)", driverName)
   }
   if driverCtx, ok := driveri.(driver.DriverContext); ok {
      connector, err := driverCtx.OpenConnector(dataSourceName)
      if err != nil {
         return nil, err
      }
      return OpenDB(connector), nil
   }
   return OpenDB(dsnConnector{dsn: dataSourceName, driver: driveri}), nil
}

Initializing the DB only constructs the structure of the DB and does not create a real connection:

func OpenDB(c driver.Connector) *DB {
   ctx, cancel := context.WithCancel(context.Background())
   db := &DB{
      connector:    c,
      openerCh:     make(chan struct{}, connectionRequestQueueSize),
      resetterCh:   make(chan *driverConn, 50),
      lastPut:      make(map[*driverConn]string),
      connRequests: make(map[uint64]chan connRequest),
      stop:         cancel,
   }
   go db.connectionOpener(ctx)      //goroutine is used to create connections
   go db.connectionResetter(ctx)    //goroutine is used to reset the session
   return db
}

Get connection

The connection is obtained when the specific sql is executed, such as Query and Exec;

func (db *DB) Query(query string, args ...interface{}) (*Rows, error) {
   return db.QueryContext(context.Background(), query, args...)
}

func (db *DB) query(ctx context.Context, query string, args []interface{}, strategy connReuseStrategy) (*Rows, error) {
   dc, err := db.conn(ctx, strategy)   //Get the connection here: create a new one or use cache
   if err != nil {
      return nil, err
   }
    //Pass in dc.releaseConn, and call back the function after the query is completed
   return db.queryDC(ctx, nil, dc, dc.releaseConn, query, args)   
}

Get the connection through db.conn(). First try to get it from freeConn, and then return it; Otherwise, check whether maxConn is exceeded. If not, create it. Otherwise, enter the queue and wait:

func (db *DB) conn(ctx context.Context, strategy connReuseStrategy) (*driverConn, error) {
    ......
    // Try to get from the connection pool
    numFree := len(db.freeConn)
    if strategy == cachedOrNewConn && numFree > 0 {
       conn := db.freeConn[0]
        ...
        return conn, nil
    }
    // If the maximum number of connections is exceeded, block and wait
    if db.maxOpen > 0 && db.numOpen >= db.maxOpen {
        ......
    }
    //Create a new connection
    db.numOpen++ // optimistically  
    ci, err := db.connector.Connect(ctx)
    dc := &driverConn{
       db:        db,
       createdAt: nowFunc(),
       ci:        ci,
       inUse:     true,
    }
    db.addDepLocked(dc, dc)    
    return dc, nil
}

Release connection

The connection is released in db.releaseConn of query. Releasing the connection actually puts the connection into the connection pool db.freeConn:

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

// putConn adds a connection to the db's free pool.
// err is optionally the last error that occurred on this connection.
func (db *DB) putConn(dc *driverConn, err error, resetSession bool) {
    ......
    added := db.putConnDBLocked(dc, nil)
    ......
}

// Satisfy a connRequest or put the driverConn in the idle pool and return true
// or return false.
func (db *DB) putConnDBLocked(dc *driverConn, err error) bool {
    ......
    db.freeConn = append(db.freeConn, dc)
    ......
}

Release of connection during query

The result of query is returned to the Rows structure, and it passes releaseConn to Row;

func (db *DB) queryDC(ctx, txctx context.Context, dc *driverConn, releaseConn func(error), query string, args []interface{}) (*Rows, error) {
    ......
    rows := &Rows{
       dc:          dc,
       releaseConn: releaseConn,    //Pass relaseConn function
       rowsi:       rowsi,
    }
    rows.initContextClose(ctx, txctx)
    return rows, nil
    ......
}

In the Rows.Next() function:

func (rs *Rows) Next() bool {
   var doClose, ok bool
   withLock(rs.closemu.RLocker(), func() {
      doClose, ok = rs.nextLocked()
   })
   if doClose {    //If no record is found, release the connection
      rs.Close()
   }
   return ok
}

func (rs *Rows) Close() error {
   return rs.close(nil)
}

func (rs *Rows) close(err error) error {
    rs.closemu.Lock()
    defer rs.closemu.Unlock()

    if rs.closed {
       return nil
    }
    rs.closed = true    //Set close flag
    ......
    rs.releaseConn(err)   //Release connection
    return err
}

That is, use Rows.Next() to release the connection; However, if it does not traverse to the end when using, it will not automatically release the connection.

It is recommended to use defer rows.Close() to manually confirm the closing. As can be seen from the code, rows.Close() is reentrant (set the closed flag and return directly next time).

golang database

mysql