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 failures

func (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服务器，这样我就可以确保连接是“新鲜的”，因为缺乏更好的术语。getConnfunc (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}