- home page
- special column
- mysql
- Article details
go database/sql -- connection application and release

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).