去数据库/ SQL-奇怪的连接和交易行为

Question

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("
testdb1 - 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
", 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
")
        return
      }
    }

    poDbTxn, oOsError := pogDbConn.Begin()

    if oOsError != nil {
        fmt.Printf("Begin Transaction failed.  Error = %s
", 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
", oOsError)
      return
    }

        _, oOsError = poDbTxn.Exec("COMMIT")
        if oOsError != nil {
      fmt.Printf("COMMIT for Insert failed.  Error = %s
", oOsError)
      return
    }
    poDbTxn     = nil
    iCommitted += 1

    if iPos%100 == 0 {
            fmt.Printf("Iteration = %d, Inserted = %d   
", iPos, iCommitted)
    }

    if tCloseConn {
      pogDbConn.Close()
      pogDbConn = nil
    }  
  }

  fmt.Printf("Inserts completed - committed = %d
", iCommitted)
}

func fRollback(poDbTxn *sql.Tx, iCommitted int) {
    println("In fDbRollbackTran
")
  fmt.Printf("Committed trans = %d
", 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
", 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)
    }
  }
}

Answer 1

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

oOsError = poDbTxn.Commit()