英文:
Setting TCP timeout for SQL connection in Go
问题
当我使用标准的Go SQL库通过VPN连接到数据库时，如果VPN接口断开，当我尝试执行SQL查询时会有75秒的超时，无论接口是否在此期间重新连接。我想将这个超时时间减少到一个合理的时间，这样我的应用程序在这种情况下就不会被冻结。
db, err := sql.Open(driverName, dataSourceName)
是否可以通过db变量来设置超时时间？
英文:
When I connect to database (using standard go sql library) using VPN and VPN interface goes down, there's a 75 seconds timeout when I try to do SQL query, no matter if the interface goes up meanwhile. I'd like to decrease this timeout to some reasonable time, so my application won't be frozen for 75 seconds in such case.
db, err := sql.Open(driverName, dataSourceName)
Is it possible to set it somehow via db variable?
答案1
得分: 30
database/sql包没有提供一种通用的方法来设置database/sql.Open调用的超时时间。然而，各个数据库驱动通过DSN（dataSourceName）连接字符串提供了这个功能。
sql.Open("postgres", "user=user dbname=dbname connect_timeout=5")
https://github.com/go-sql-driver/mysql
sql.Open("mysql", "user:password@/dbname?timeout=5s")
https://github.com/denisenkom/go-mssqldb
sql.Open("sqlserver", "sqlserver://username:password@host/instance?dial+timeout=5")
等等...
英文:
The database/sql package doesn't provide a general way to timeout a call to database/sql.Open. However, individual drivers provide this functionality via the DSN (dataSourceName) connection strings.
sql.Open("postgres", "user=user dbname=dbname connect_timeout=5")
https://github.com/go-sql-driver/mysql
sql.Open("mysql", "user:password@/dbname?timeout=5s")
https://github.com/denisenkom/go-mssqldb
sql.Open("sqlserver", "sqlserver://username:password@host/instance?dial+timeout=5")
etc ...
答案2
得分: 6
从Go 1.8开始，sql.DB抽象现在接受context.Context，可以用于更快地超时连接。
func (c *Client) DoLookup(ctx context.Context, id int) (string, error) {
var name string
// 使用超时创建一个子上下文
newCtx, cancel := context.WithTimeout(ctx, time.Second)
// 如果DB操作完成得比超时时间快，释放`newCtx`中使用的资源
defer cancel()
row := c.db.QueryRowContext(newCtx, "SELECT name FROM items WHERE id = ?", id)
err := row.Scan(&name)
if err != nil {
return "", err
}
return name, nil
}
如果你的DoLookup函数还没有接受context.Context（但实际上应该接受！），你可以通过调用context.TODO()来创建一个父上下文。
英文:
Starting with Go 1.8, the sql.DB abstraction now accepts context.Context, which can be used to time out connections faster.
func (c *Client) DoLookup(ctx context.Context, id int) (string, error) {
var name string
// create a child context with a timeout
newCtx, cancel := context.WithTimeout(ctx, time.Second)
// release resources used in `newCtx` if
// the DB operation finishes faster than the timeout
defer cancel()
row := c.db.QueryRowContext(newCtx, "SELECT name FROM items WHERE id = ?", id)
err := row.Scan(&name)
if err != nil {
return "", err
}
return name, nil
}
If your DoLookup function doesn't yet take a context.Context (and it really should!) you can create a parent one by calling context.TODO().
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论