go database/sql – 连接和事务行为奇怪

huangapple go评论80阅读模式
英文:

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 (
&quot;database/sql&quot;
&quot;fmt&quot;
&quot;time&quot;
&quot;os&quot;
&quot;bufio&quot;
&quot;strconv&quot;
_ &quot;github.com/lib/pq&quot;
////  _ &quot;github.com/go-sql-driver/mysql&quot;
//// _ &quot;github.com/arnehormann/mysql&quot;
)
const C_CONN_RDBMS = &quot;postgres&quot;
const C_CONN_STR = &quot;user=admin dbname=testdb password=admin sslmode=disable&quot;
////const C_CONN_RDBMS = &quot;mysql&quot;
////const C_CONN_STR = &quot;test:test@/testdb?charset=utf8&quot;
var pogDbConn    *sql.DB // Db connection 
func main() {
fmt.Println(&quot;\ntestdb1 - small test on &quot;+C_CONN_RDBMS+&quot; driver&quot;)
println()
var iIters     int   = fGetIterations()
println()
var tCloseConn bool  = fGetCloseConn()
tmeStart := time.Now()
fDbTestInserts(iIters, tCloseConn) // run test Insert
fmt.Printf(&quot;Elapsed Time to process = %s\n&quot;, 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(&quot;Running test inserts .........&quot;)
defer func() {fRollback(poDbTxn, iCommitted)} ()
for iPos := 1; iPos &lt;= iIters; iPos += 1 {
if pogDbConn == nil {     // must open db
pogDbConn, oOsError = sql.Open(C_CONN_RDBMS, C_CONN_STR)
if oOsError != nil {
fmt.Printf(&quot;Failed to open Db Connection. Error = %s\n&quot;)
return
}
}
poDbTxn, oOsError := pogDbConn.Begin()
if oOsError != nil {
fmt.Printf(&quot;Begin Transaction failed.  Error = %s\n&quot;, oOsError)
return
}
var sSql string = &quot;INSERT INTO test01 &quot; +
&quot;(sName, dBalance)&quot; +
&quot; VALUES (&#39;Bart Simpson&#39;, 999.99)&quot;
_, oOsError = poDbTxn.Exec(sSql)
if oOsError != nil {
fmt.Printf(&quot;INSERT for Table failed.  Error = %s\n&quot;, oOsError)
return
}
_, oOsError = poDbTxn.Exec(&quot;COMMIT&quot;)
if oOsError != nil {
fmt.Printf(&quot;COMMIT for Insert failed.  Error = %s\n&quot;, oOsError)
return
}
poDbTxn     = nil
iCommitted += 1
if iPos%100 == 0 {
fmt.Printf(&quot;Iteration = %d, Inserted = %d   \n&quot;, iPos, iCommitted)
}
if tCloseConn {
pogDbConn.Close()
pogDbConn = nil
}  
}
fmt.Printf(&quot;Inserts completed - committed = %d\n&quot;, iCommitted)
}
func fRollback(poDbTxn *sql.Tx, iCommitted int) {
println(&quot;In fDbRollbackTran\n&quot;)
fmt.Printf(&quot;Committed trans = %d\n&quot;, iCommitted)
if poDbTxn == nil {
println(&quot;No Rollback required&quot;)
} else {
if pogDbConn == nil {
print (&quot;Unable to Rollback - no connection&quot;)
} else {
println(&quot;Attempting Rollback&quot;)
var oOsError error = poDbTxn.Rollback()
if oOsError != nil {
fmt.Printf(&quot;Rollback of Transaction failed.  Error = %s\n&quot;, oOsError)
} else {
println(&quot;Rollback Succeeded&quot;)
}
}
}
}
func fGetIterations() int {
oBufReader := bufio.NewReader(os.Stdin)
for {
print(&quot;Number of Inserts to process : (1 to 10,000) or &#39;end&#39; : &quot;)
vLine, _, _ := oBufReader.ReadLine()
var sInput string = string(vLine)
if sInput == &quot;end&quot; || sInput == &quot;END&quot; {
os.Exit(1)
}
iTot, oError := strconv.Atoi(sInput)
if oError != nil {
println(&quot;Invalid number&quot;)
} else if iTot &lt; 1 || iTot &gt; 10000 {
println (&quot;Number must be from 1 to 10,000&quot;)
} else {
return iTot
}
}
}
func fGetCloseConn() bool {
oBufReader := bufio.NewReader(os.Stdin)
for {
print(&quot;Close Connection every transaction? (y/n/end) : &quot;)
vLine, _, _ := oBufReader.ReadLine()
sInput := string(vLine)
if sInput == &quot;y&quot; || sInput == &quot;n&quot; {
return (sInput == &quot;y&quot;)
}
if sInput == &quot;end&quot; || sInput == &quot;END&quot; {
os.Exit(1)
}
}
}

答案1

得分: 0

提交应该按照以下方式进行(根据给我的建议):

oOsError = poDbTxn.Commit()
英文:

Commit should be done as follows (as advised to me) :

oOsError = poDbTxn.Commit()

huangapple
  • 本文由 发表于 2013年9月17日 20:12:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/18849532.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定