Golang 中对长时间运行的 MSSQL 事务的连接响应错误

Question

我有一个请求者，负责管理针对 Azure SQL 数据库的 SQL 查询。负责事务查询的函数如下：import (    "context"    "database/sql"    "fmt"    "log"    "strings"    "time"    "github.com/cenkalti/backoff"    _ "github.com/denisenkom/go-mssqldb" // Need to import the SQL driver so we can tell Golang how to interpret our requests)// Helper function that does a single Exec with a transaction with a context on a query and variables.// This function will return an error if there are any failuresfunc (requester *Requester) doTransaction(ctx context.Context,     isolation sql.IsolationLevel, txFunc func(*sql.Tx) error) error {    // First, get the database connection; if this fails then return an error    conn, err := requester.getConn(ctx)    if err != nil {        return err    }    // Before we continue on, ensure that the connection is clsoed and returned to the connection pool    defer func() {        if err := conn.Close(); err != nil {            log.Printf("Close failed, error: %v", err)        }    }()    // Next, start the transaction with the given context and the default isolation    tx, err := requester.getTx(ctx, conn, isolation)    if err != nil {        return err    }总的来说，这很有效。我注意到的一个问题是，当各个请求之间经过很长时间时，我会在第一次重试时遇到错误，然后在后续重试时出错，最终导致失败。我的想法是，问题与此错误有关。从本质上讲，微软似乎在30分钟后使空闲请求无效。但是，由于我将最大空闲时间设置为10分钟，因此这应该不是问题所在。i/o timeoutbad connection这是怎么回事，我该如何解决这个问题？

Answer 1

经过一些调查，我发现数据库连接在 30 分钟窗口后变得陈旧，修改连接池的生存期或空闲时间并不能真正解决此问题。因此，为了缓解这个问题，我所做的是事先修改我的函数以ping服务器，这样我就可以确保连接是“新鲜的”，因为缺乏更好的术语。getConn

func (requester *Requester) getConn(ctx context.Context) (*sql.Conn, error) {

    // First, attempt to ping the server to ensure that the connection is good

    // If this fails, then return an error

    if err := requester.conn.PingContext(ctx); err != nil {

        return nil, err

    }

    // Create an object that will dictate how and when the retries are done

    // We currently want an exponential backoff that retries a maximum of 5 times

    repeater := backoff.WithContext(backoff.WithMaxRetries(

        backoff.NewExponentialBackOff(), 5), ctx)

    // Do a retry operation with a 500ms wait time and a maximum of 5 retries

    // and return the result of the operation therein

    var conn *sql.Conn

    if err := backoff.Retry(func() error {

        // Attempt to get the connection to the database

        var err error

        if conn, err = requester.conn.Conn(ctx); err != nil {

            // We failed to get the connection; if we have a login error, an EOF or handshake

            // failure then we'll attempt the connection again later so just return it and let

            // the backoff code handle it

            log.Printf("Conn failed, error: %v", err)

            if isLoginError(err, requester.serverName, requester.databaseName) {

                return err

            } else if strings.Contains(err.Error(), "EOF") {

                return err

            } else if strings.Contains(err.Error(), "TLS Handshake failed") {

                return err

            }

            // Otherwise, we can't recover from the error so return it

            return backoff.Permanent(err)

        }

        return nil

    }, repeater); err != nil {

        return nil, err

    }

    return conn, nil

}