Goroutines阻止连接池 [英] Goroutines blocked connection pool
问题描述
package main
import（
database / sql
fmt
_github.com/ lib / pq
sync
）
func main（）{
db，_：= sql.Open（postgres，fmt.Sprintf（ host =％s dbname =％s user =％s sslmode = disable，localhost，dbname，postgres））
defer db.Close（）
db .SetMaxOpenConns（15）
var wg sync.WaitGroup
for i：= 0;我< 15; i ++ {
wg.Add（1）
go func（）{
推迟wg.Done（）
//＃1
rows，_：= db。 Query（SELECT * FROM reviews LIMIT 1）
for rows.Next（）{
//＃2
db.Exec（SELECT * FROM reviews LIMIT 1）
）
}（）
}
wg.Wait（）
}
查询＃1打开15个连接，当 rows.Next（）被执行时，它们将被关闭。但是 rows.Next（）将永远不会执行，因为它包含等待空闲连接的 db.Exec（） 。
如何解决这个问题？
是陷入僵局。在最坏的情况下，你有15个goroutine持有15个数据库连接，并且所有这15个goroutine都需要一个新的连接才能继续。但为了获得新的连接，人们必须推进并释放连接：死锁。
链接的维基百科文章详细介绍了防止死锁的方法。例如，一个代码执行应该只有在它需要（或需要）所有资源时才进入临界区（即锁定资源）。在这种情况下，这意味着您必须保留2个连接（正好2;如果只有1个可用，请保留并等待），如果您有2个连接，则只能继续查询。但在Go中，您无法提前预订连接。当您执行查询时，会根据需要分配它们。
通常应避免使用这种模式。你不应该编写首先保留一个（有限的）资源的代码（在这种情况下为db连接），并且在它释放它之前，它需要另一个。
一个简单的解决方法是执行第一个查询，将其结果保存（例如，保存到Go切片中），当您完成后，继续进行后续查询（但也不要忘记关闭 sql.Rows 首先）。这样你的代码不需要同时连接2个。
不要忘记处理错误！我简单地省略了它们，但不应该在代码中。
这可能是这样的：
< pre $ go func（）{
defer wg.Done（）
rows，_：= db.Query（SELECT * FROM reviews LIMIT 1 ）
var data [] int //使用任何类型描述的数据查询
作为rows.Next（）{
var something int
rows.Scan（& something）
data = append（data，something）
}
rows.Close（）
for _，v：=范围数据{
// You如果需要，可以使用v作为查询参数
db.Exec（SELECT * FROM reviews LIMIT 1）
}
}（）
注意 defer 语句来确保它会被执行（即使在恐慌的情况下）。但是，如果您只是简单地使用推迟rows.Close（），那只会在后续查询执行后执行，所以它不会阻止死锁。所以我会重构它在另一个函数（可能是一个匿名函数）中调用它，你可以使用 defer ：
rows，_：= db.Query（SELECT * FROM reviews LIMIT 1）
var data [] int //使用您查询的任何类型描述数据
func（）{
推迟rows.Close（）
为rows.Next（）{
var something int
rows.Scan（& something）
data = append（data，something）
}
}（）
还要注意，在第二个 for 循环中，准备好的语句（ sql.Stmt ）通过 DB.Prepare（） 可能是多次执行相同（参数化）查询的更好选择。
另一种选择是启动后续查询在新的goroutine中，以便在当前锁定的连接被释放时（或其他任何其他goroutine锁定的任何其他连接）执行的查询可能会发生，但是，如果没有显式同步，则在执行时没有控制权。它可能是这样的：
go func（）{
defer wg.Done（）
rows，_：= db.Query（SELECT * FROM reviews LIMIT 1）
推迟rows.Close（）
为rows.Next（）{
var something int
rows.Scan（&something）
//如果需要的话传递一些东西
go db.Exec（SELECT * FROM reviews LIMIT 1）
}
}（ ）
为了让程序也等待这些goroutine，使用 WaitGroup 你已经有了行动：
//如果需要的话传递一些东西
wg.Add 1）
go func（）{
defer wg.Done（）
db.Exec（SELECT * FROM reviews LIMIT 1）
}（）
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
"sync"
)
func main() {
db, _ := sql.Open("postgres", fmt.Sprintf("host=%s dbname=%s user=%s sslmode=disable", "localhost", "dbname", "postgres"))
defer db.Close()
db.SetMaxOpenConns(15)
var wg sync.WaitGroup
for i := 0; i < 15; i++ {
wg.Add(1)
go func() {
defer wg.Done()
//#1
rows, _ := db.Query("SELECT * FROM reviews LIMIT 1")
for rows.Next() {
//#2
db.Exec("SELECT * FROM reviews LIMIT 1")
}
}()
}
wg.Wait()
}
Query #1 opens 15 connections and they will be closed when rows.Next() is executed. But rows.Next() will be never executed because it contains db.Exec() that waits for a free connection.
How to solve this problem?
What you have is a deadlock. In the worst case scenario you have 15 goroutines holding 15 database connections, and all of those 15 goroutines require a new connection to continue. But to get a new connection, one would have to advance and release a connection: deadlock.
The linked wikipedia article details prevention of deadlock. For example a code execution should only enter a critical section (that locks resources) when it has all the resources it needs (or will need). In this case this means you would have to reserve 2 connections (exactly 2; if only 1 is available, leave it and wait), and if you have those 2, only then proceed with the queries. But in Go you can't reserve connections in advance. They are allocated as needed when you execute queries.
Generally this pattern should be avoided. You should not write code which first reserves a (finite) resource (db connection in this case), and before it would release it, it demands another one.
An easy workaround is to execute the first query, save its result (e.g. into a Go slice), and when you're done with that, then proceed with the subsequent queries (but also don't forget to close sql.Rows first). This way your code does not need 2 connections at the same time.
And don't forget to handle errors! I omitted them for brevity, but you should not in your code.
This is how it could look like:
go func() {
defer wg.Done()
rows, _ := db.Query("SELECT * FROM reviews LIMIT 1")
var data []int // Use whatever type describes data you query
for rows.Next() {
var something int
rows.Scan(&something)
data = append(data, something)
}
rows.Close()
for _, v := range data {
// You may use v as a query parameter if needed
db.Exec("SELECT * FROM reviews LIMIT 1")
}
}()
Note that rows.Close() should be executed as a defer statement to make sure it will get executed (even in case of a panic). But if you simply use defer rows.Close(), that would only be executed after the subsequent queries are executed, so it won't prevent the deadlock. So I would refactor it to call it in another function (which may be an anonymous function) in which you can use a defer:
rows, _ := db.Query("SELECT * FROM reviews LIMIT 1")
var data []int // Use whatever type describes data you query
func() {
defer rows.Close()
for rows.Next() {
var something int
rows.Scan(&something)
data = append(data, something)
}
}()
Also note that in the second for loop a prepared statement (sql.Stmt) acquired by DB.Prepare() would probably be a much better choice to execute the same (parameterized) query multiple times.
Another option is to launch subsequent queries in new goroutines so that the query executed in that can happen when the currently locked connection is released (or any other connection locked by any other goroutine), but then without explicit synchronization you don't have control when they get executed. It could look like this:
go func() {
defer wg.Done()
rows, _ := db.Query("SELECT * FROM reviews LIMIT 1")
defer rows.Close()
for rows.Next() {
var something int
rows.Scan(&something)
// Pass something if needed
go db.Exec("SELECT * FROM reviews LIMIT 1")
}
}()
To make your program wait for these goroutines too, use the WaitGroup you already have in action:
// Pass something if needed
wg.Add(1)
go func() {
defer wg.Done()
db.Exec("SELECT * FROM reviews LIMIT 1")
}()
这篇关于Goroutines阻止连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
