Golang 中对长时间运行的 MSSQL 事务进行连接时出现错误

积累知识，胜过积蓄金银！毕竟在Golang开发的过程中，会遇到各种各样的问题，往往都是一些细节知识点还没有掌握好而导致的，因此基础知识点的积累是很重要的。下面本文《Golang 中对长时间运行的 MSSQL 事务进行连接时出现错误》，就带大家讲解一下知识点，若是你对本文感兴趣，或者是想搞懂其中某个知识点，就请你继续往下看吧~

我有一个请求程序，用于管理针对 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
    }

    // now, ensure that the transaction is either rolled back or committed before
    // the function ends
    var terr error
    defer func() {
        if p := recover(); p != nil {
            tx.rollback()
            panic(p)
        } else if terr != nil {
            log.printf("an error occurred: %v", terr)
            if err := tx.rollback(); err != nil {
                log.printf("rollback failed, error: %v", err)
            }
        } else {
            if terr := tx.commit(); terr != nil {
                log.printf("commit failed, error: %v", terr)
            }
        }
    }()

    // finally, run the function and return the result
    terr = txfunc(tx)
    return terr
}

// helper function that gets a connection to the database with a backup and retry
func (requester *requester) getconn(ctx context.context) (*sql.conn, error) {

    // 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
}

// helper function that starts a transaction against the database
func (requester *requester) gettx(ctx context.context, conn *sql.conn, 
    isolation sql.isolationlevel) (*sql.tx, error) {

    // 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 tx *sql.tx
    if err := backoff.retry(func() error {

        // attempt to start the transaction with the given context and the default isolation
        var err error
        if tx, err = conn.begintx(ctx, &sql.txoptions{isolation: isolation, readonly: false}); err != nil {

            // we failed to create the transaction; if we have a connection error then we'll
            // attempt the connection again later so just return it and let the backoff code handle it
            if strings.contains(err.error(), "an existing connection was forcibly closed by the remote host.") || 
                strings.contains(err.error(), "bad connection") {
                log.printf("begintx failed, error: %v. retrying...", err)
                return err
            }

            // otherwise, we can't recover from the error so return it
            log.printf("unknown/uncaught exception when attempting to create a transaction, error: %v", err)
            return backoff.permanent(err)
        }

        return nil
    }, repeater); err != nil {
        return nil, err
    }

    return tx, nil
}

请求者对象包装了 sql.db 并按如下方式创建：

// First, create a connection string from the endpoint, port, user name, password and database name
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%s;database=%s;connection timeout=30",
    endpoint, dbUser, dbPassword, port, dbName)

// Finally, attempt to connect to the database. If this fails then return an error
db, err := sql.Open("sqlserver", connString)
if err != nil {
    return nil, err
}

// Ensure that our connections are used and reused in such a way
// as to avoid bad connections and I/O timeouts
db.SetMaxOpenConns(20)
db.SetConnMaxLifetime(10 * time.Minute)
db.SetConnMaxIdleTime(10 * time.Minute)

总的来说，这效果很好。我注意到的一个问题是，当各个请求之间经过很长时间时，我会在第一次重试时收到 i/o timeout 错误，然后在后续重试时收到 bad connection 错误，最终导致失败。我的想法是这个问题与这个错误有关。从本质上讲，microsoft 似乎会在 30 分钟后使空闲请求失效。不过，由于我将最大空闲时间设置为 10 分钟，所以这应该不是问题。

这是怎么回事？我该如何解决这个问题？

正确答案

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

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
}

到这里，我们也就讲完了《Golang 中对长时间运行的 MSSQL 事务进行连接时出现错误》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于的知识点！