Analyzing Go SQL Connection Pool Resource Release and Transaction Management

Many developers encounter slow MySQL queries or unexpected errors when using Go with databases. This article analyzes the principles of the SQL connection pool, demonstrates several experiments, and provides practical solutions to help avoid resource‑leak problems.

1. Resources not released in time – The database/sql package uses long‑lived connections. When a query returns a Rows stream, the underlying network connection remains occupied until the stream is closed. Therefore Rows.Close() must be called after the result set is processed.

type Rows struct {
    Close() error
    ColumnTypes() ([]*ColumnType, error)
    Columns() ([]string, error)
    Err() error
    Next() bool
    NextResultSet() bool
    Scan(dest ...any) error
}

Experiment 1 – Not calling Rows.Close()

package main
import (
    "context"
    "database/sql"
    "encoding/json"
    "fmt"
    "sync"
    "time"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test")
    if err != nil { panic(err) }
    db.SetMaxOpenConns(1)
    go func() {
        tk := time.NewTicker(3 * time.Second)
        defer tk.Stop()
        for range tk.C {
            bf, _ := json.Marshal(db.Stats())
            fmt.Println("db.Stats=", string(bf))
        }
    }()
    var wg sync.WaitGroup
    for i := 0; i < 10; i++ {
        wg.Add(1)
        go func(id int) {
            defer wg.Done()
            queryOne(id, db)
        }(i)
    }
    wg.Wait()
    fmt.Println("finish")
}
func queryOne(id int, db *sql.DB) {
    start := time.Now()
    rows, err := db.QueryContext(context.Background(), "select * from user limit 1")
    if err != nil { panic(err) }
    // defer rows.Close()   // intentionally omitted
    fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start))
}

The output shows only one goroutine can obtain a connection (InUse=1, WaitCount=9). The other nine goroutines stay blocked because the connection is never returned to the pool.

Experiment 2 – Calling Rows.Close()

func queryOne(id int, db *sql.DB) {
    start := time.Now()
    rows, err := db.QueryContext(context.Background(), "select * from user limit 1")
    if err != nil { panic(err) }
    defer rows.Close()
    fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start))
}

All ten goroutines finish successfully, confirming that closing the rows releases the connection back to the pool.

Experiment 3 – Using a timeout Context

func queryOne(id int, db *sql.DB) {
    start := time.Now()
    ctx, cancel := context.WithTimeout(context.Background(), time.Second)
    defer cancel()
    rows, err := db.QueryContext(ctx, "select * from user limit 1")
    if err != nil { fmt.Println("BeginTx failed:", err); return }
    // defer rows.Close() // optional when context is cancelled
    fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start))
}

When the context is cancelled, the underlying connection is released even if Rows.Close() is omitted. If the cancel call is replaced by a no‑op (e.g., _ = cancel), the query may fail with “context deadline exceeded”.

Solution for resource release

Always use QueryContext (or other *Context* methods) with a timeout‑enabled context.Context.

Defer cancel() to ensure the context is cancelled after the operation.

Always call Rows.Close() (and similarly Stmt.Close(), Tx.Commit() / Tx.Rollback()) to return resources to the pool.

2. Transaction incompleteness

When a transaction ( *sql.Tx) is opened, it must be either committed or rolled back; otherwise the connection stays in use.

tx, err := db.BeginTx(context.Background(), nil)
if err != nil { panic(err) }
// defer tx.Rollback() // uncomment to release the connection
rows, err := tx.QueryContext(context.Background(), "select * from user limit 1")
if err != nil { panic(err) }
defer rows.Close()
fmt.Println("id=", id, "hasNext=", rows.Next(), "cost=", time.Since(start))
// missing tx.Commit() / tx.Rollback() leads to the same waiting problem as above

Adding defer tx.Rollback() (or committing on success) allows all goroutines to finish.

Solution for transactions

Use defer tx.Rollback() immediately after BeginTx and call tx.Commit() only when the business logic succeeds.

Frameworks such as GDP provide helper functions ( mysql.BeginTx) that automatically decide to commit or rollback based on the returned error.

3. Other causes

Some MySQL clusters (e.g., DRDS) have poor support for prepared statements. Adding interpolateParams=true to the DSN disables preparation and improves performance.

DSNParams = "charset=utf8&timeout=90s&collation=utf8mb4_unicode_ci&parseTime=true&interpolateParams=true"

4. How to troubleshoot

Use db.Stats() to monitor MaxOpenConnections, InUse, Idle, WaitCount, etc.

Integrate the GDP application panel to view MySQL client stats in a UI.

Expose metrics via Prometheus or BVAR (e.g., client_connpool{servicer="demo_mysql",stats="InUseAvg"} 0).

Periodically log the stats in a background goroutine for offline analysis.

By following these practices—using timeout contexts, always closing rows, and ensuring transactions are properly finished—developers can avoid connection‑pool exhaustion and improve the stability of Go services that interact with MySQL.