Go and SQL memory leaks

The scenario: You’ve got some Go code. You can run the unit tests individually, but when you try go test -v ./... or try to run continuous integration, the tests fall over from lack of memory.

There are lots of things that could be causing the problem, but one you should definitely check for is unclosed SQL database statements and connections.

You’re probably aware that the general pattern for SQL queries in Go, assuming you have a sql.DB connection pool in the variable db, is something like this:

rows, err := db.Query("select * from tablename where id = ?", id)
if err != nil {
  return fmt.Errorf("can't query tablename for %d: %v", id, err)
}
defer func() {
  err := rows.Close()
  if err != nil {
    fmt.Fprintf(os.Stderr, "warning: failed to close SQL statement\n")
  }
}()
for rows.Next() {
  // process returned data
}

The problem is what happens if you forget that deferred rows.Close() (or it fails for some reason). Some or all of the retrieved data sits around in memory, and the SQL statement stays open. Depending on the database, that may even prevent Go from being able to close the database, keeping all the database connection structures stuck in memory too. (I believe MariaDB will let you close a database connection even if there are unclosed statements, but DB2 most definitely will not.)

The end result? Database structures and data collect in memory every time that piece of code is called.

For this reason, it’s a good idea to make your unit tests which involve database operations check for unclosed DB connections and statements.

The main hurdle is that you need to write your own TestMain method in any unit test file that performs database operations. Here’s what it will look like:

var db *sql.DB

func TestMain(m *testing.M) {
  // Get test database 
  db = testutils.GetTestDatabase()
  // Tell Go to return closed DB connections to the pool 
  // rather than leaving them idle
  db.SetMaxIdleConns(0)
  testutils.ResetTestData(db)
  // This actually runs your unit tests
  code := m.Run()
  // Now we check to see if any connections were left open
  c := db.Stats().OpenConnections
  if c != 0 {
    panic("connection left open")
  }
  os.Exit(code)
}

Of course once you have identified that DB connections are being left open, you need to identify which ones. In general, of course, it’ll be whichever one you wrote most recently. If you have a codebase which has never been tested like this before, though, you might have a lot of database leaks. In that case, the workaround is to check OpenConnections in each individual test.