英文:
go database/sql - strange connection and transaction behavior
问题
使用Go语言和使用database/sql的数据库驱动程序实现时,我似乎遇到了与事务相关的行为,即每次事务后都需要关闭连接。如果不关闭连接,数据库会耗尽连接并出现以下错误:
"Begin Transaction failed. Error = Error 1040: Too many connections"。这发生在101次提交之后。
我尝试使用了两个来自GitHub的不同驱动程序-lib/pq和go-sql-driver/mysql,但结果相同。
这种行为对我来说很奇怪。这是预期的行为,还是我可能做错了什么?
如请求的那样,下面是代码:
package main
import (
"database/sql"
"fmt"
"time"
"os"
"bufio"
"strconv"
_ "github.com/lib/pq"
//// _ "github.com/go-sql-driver/mysql"
//// _ "github.com/arnehormann/mysql"
)
const C_CONN_RDBMS = "postgres"
const C_CONN_STR = "user=admin dbname=testdb password=admin sslmode=disable"
////const C_CONN_RDBMS = "mysql"
////const C_CONN_STR = "test:test@/testdb?charset=utf8"
var pogDbConn *sql.DB // Db connection
func main() {
fmt.Println("\ntestdb1 - small test on "+C_CONN_RDBMS+" driver")
println()
var iIters int = fGetIterations()
println()
var tCloseConn bool = fGetCloseConn()
tmeStart := time.Now()
fDbTestInserts(iIters, tCloseConn) // run test Insert
fmt.Printf("Elapsed Time to process = %s\n", time.Since(tmeStart))
if pogDbConn != nil {
pogDbConn.Close()
}
}
func fDbTestInserts(iIters int, tCloseConn bool) {
var iCommitted int = 0
var oOsError error
var poDbTxn *sql.Tx
println("Running test inserts .........")
defer func() {fRollback(poDbTxn, iCommitted)} ()
for iPos := 1; iPos <= iIters; iPos += 1 {
if pogDbConn == nil { // must open db
pogDbConn, oOsError = sql.Open(C_CONN_RDBMS, C_CONN_STR)
if oOsError != nil {
fmt.Printf("Failed to open Db Connection. Error = %s\n")
return
}
}
poDbTxn, oOsError := pogDbConn.Begin()
if oOsError != nil {
fmt.Printf("Begin Transaction failed. Error = %s\n", oOsError)
return
}
var sSql string = "INSERT INTO test01 " +
"(sName, dBalance)" +
" VALUES ('Bart Simpson', 999.99)"
_, oOsError = poDbTxn.Exec(sSql)
if oOsError != nil {
fmt.Printf("INSERT for Table failed. Error = %s\n", oOsError)
return
}
_, oOsError = poDbTxn.Exec("COMMIT")
if oOsError != nil {
fmt.Printf("COMMIT for Insert failed. Error = %s\n", oOsError)
return
}
poDbTxn = nil
iCommitted += 1
if iPos%100 == 0 {
fmt.Printf("Iteration = %d, Inserted = %d \n", iPos, iCommitted)
}
if tCloseConn {
pogDbConn.Close()
pogDbConn = nil
}
}
fmt.Printf("Inserts completed - committed = %d\n", iCommitted)
}
func fRollback(poDbTxn *sql.Tx, iCommitted int) {
println("In fDbRollbackTran\n")
fmt.Printf("Committed trans = %d\n", iCommitted)
if poDbTxn == nil {
println("No Rollback required")
} else {
if pogDbConn == nil {
print ("Unable to Rollback - no connection")
} else {
println("Attempting Rollback")
var oOsError error = poDbTxn.Rollback()
if oOsError != nil {
fmt.Printf("Rollback of Transaction failed. Error = %s\n", oOsError)
} else {
println("Rollback Succeeded")
}
}
}
}
func fGetIterations() int {
oBufReader := bufio.NewReader(os.Stdin)
for {
print("Number of Inserts to process : (1 to 10,000) or 'end' : ")
vLine, _, _ := oBufReader.ReadLine()
var sInput string = string(vLine)
if sInput == "end" || sInput == "END" {
os.Exit(1)
}
iTot, oError := strconv.Atoi(sInput)
if oError != nil {
println("Invalid number")
} else if iTot < 1 || iTot > 10000 {
println ("Number must be from 1 to 10,000")
} else {
return iTot
}
}
}
func fGetCloseConn() bool {
oBufReader := bufio.NewReader(os.Stdin)
for {
print("Close Connection every transaction? (y/n/end) : ")
vLine, _, _ := oBufReader.ReadLine()
sInput := string(vLine)
if sInput == "y" || sInput == "n" {
return (sInput == "y")
}
if sInput == "end" || sInput == "END" {
os.Exit(1)
}
}
}
英文:
Using Go and implementations of database drivers using database/sql, the behavior that I appear to be experiencing with transactions appears to be that the connection needs to be closed after every transaction. If I don't, the database runs out of connections and I receive the following error :
> "Begin Transaction failed. Error = Error 1040: Too many connections". This happens after 101 commits.
I have tried using two different drivers from github - lib/pq and go-sql-driver/mysql with the same results.
This behavior appears strange to me. Is this to be expected, or am I perhaps doing something incorrectly?
As requested, the code is below :
package main
import (
"database/sql"
"fmt"
"time"
"os"
"bufio"
"strconv"
_ "github.com/lib/pq"
//// _ "github.com/go-sql-driver/mysql"
//// _ "github.com/arnehormann/mysql"
)
const C_CONN_RDBMS = "postgres"
const C_CONN_STR = "user=admin dbname=testdb password=admin sslmode=disable"
////const C_CONN_RDBMS = "mysql"
////const C_CONN_STR = "test:test@/testdb?charset=utf8"
var pogDbConn *sql.DB // Db connection
func main() {
fmt.Println("\ntestdb1 - small test on "+C_CONN_RDBMS+" driver")
println()
var iIters int = fGetIterations()
println()
var tCloseConn bool = fGetCloseConn()
tmeStart := time.Now()
fDbTestInserts(iIters, tCloseConn) // run test Insert
fmt.Printf("Elapsed Time to process = %s\n", time.Since(tmeStart))
if pogDbConn != nil {
pogDbConn.Close()
}
}
func fDbTestInserts(iIters int, tCloseConn bool) {
var iCommitted int = 0
var oOsError error
var poDbTxn *sql.Tx
println("Running test inserts .........")
defer func() {fRollback(poDbTxn, iCommitted)} ()
for iPos := 1; iPos <= iIters; iPos += 1 {
if pogDbConn == nil { // must open db
pogDbConn, oOsError = sql.Open(C_CONN_RDBMS, C_CONN_STR)
if oOsError != nil {
fmt.Printf("Failed to open Db Connection. Error = %s\n")
return
}
}
poDbTxn, oOsError := pogDbConn.Begin()
if oOsError != nil {
fmt.Printf("Begin Transaction failed. Error = %s\n", oOsError)
return
}
var sSql string = "INSERT INTO test01 " +
"(sName, dBalance)" +
" VALUES ('Bart Simpson', 999.99)"
_, oOsError = poDbTxn.Exec(sSql)
if oOsError != nil {
fmt.Printf("INSERT for Table failed. Error = %s\n", oOsError)
return
}
_, oOsError = poDbTxn.Exec("COMMIT")
if oOsError != nil {
fmt.Printf("COMMIT for Insert failed. Error = %s\n", oOsError)
return
}
poDbTxn = nil
iCommitted += 1
if iPos%100 == 0 {
fmt.Printf("Iteration = %d, Inserted = %d \n", iPos, iCommitted)
}
if tCloseConn {
pogDbConn.Close()
pogDbConn = nil
}
}
fmt.Printf("Inserts completed - committed = %d\n", iCommitted)
}
func fRollback(poDbTxn *sql.Tx, iCommitted int) {
println("In fDbRollbackTran\n")
fmt.Printf("Committed trans = %d\n", iCommitted)
if poDbTxn == nil {
println("No Rollback required")
} else {
if pogDbConn == nil {
print ("Unable to Rollback - no connection")
} else {
println("Attempting Rollback")
var oOsError error = poDbTxn.Rollback()
if oOsError != nil {
fmt.Printf("Rollback of Transaction failed. Error = %s\n", oOsError)
} else {
println("Rollback Succeeded")
}
}
}
}
func fGetIterations() int {
oBufReader := bufio.NewReader(os.Stdin)
for {
print("Number of Inserts to process : (1 to 10,000) or 'end' : ")
vLine, _, _ := oBufReader.ReadLine()
var sInput string = string(vLine)
if sInput == "end" || sInput == "END" {
os.Exit(1)
}
iTot, oError := strconv.Atoi(sInput)
if oError != nil {
println("Invalid number")
} else if iTot < 1 || iTot > 10000 {
println ("Number must be from 1 to 10,000")
} else {
return iTot
}
}
}
func fGetCloseConn() bool {
oBufReader := bufio.NewReader(os.Stdin)
for {
print("Close Connection every transaction? (y/n/end) : ")
vLine, _, _ := oBufReader.ReadLine()
sInput := string(vLine)
if sInput == "y" || sInput == "n" {
return (sInput == "y")
}
if sInput == "end" || sInput == "END" {
os.Exit(1)
}
}
}
答案1
得分: 0
提交应该按照以下方式进行(根据给我的建议):
oOsError = poDbTxn.Commit()
英文:
Commit should be done as follows (as advised to me) :
oOsError = poDbTxn.Commit()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论