在golang中使用预处理语句时，是否总是需要延迟stmt.close（）吗？

Question

According to the post here, http://go-database-sql.org/modifying.html, there is no defer stmt.close(). However, there is a stmt.close() API in sql package. I can't seem to find information on whether it is necessary to defer closing the stmt when using prepared statement. Hence, the question - Do I always need to defer stmt.close() when I use prepared statement in golang?

Answer 1

Using defer is not a requirement, it's a convenience and it offers safety. What matters is that if you create / prepare a statement (which uses some resources internally and may also use resources in the database server itself), then sometime, some point in the future you do call its Close() method in order to release resources allocated by it.

Deferred functions are executed even if your function ends abruptly (e.g. you have an early return statement or even if your function panics), so using defer to close resources gives you safety that your code will not leak resources and / or memory.

Note that defer will only run when the surrounding function returns. There may be cases when it is possible or even appropriate to close the statement earlier, and not wait until the surrounding function returns. A good example could be if you have a for loop doing many-many iterations, you would not want all the statements created in each iterations to keep resources until the for loop completes. In this case it is recommended to close resources before the next iteration begins. And in this case you would simply write a stmt.Close() instead of defer stmt.Close(), because the latter would not close the statement immediately, only when the function returns.

Also note that "outsourcing" the loop's body to a separate function will also give you the possibility to use defer in that separate function, where the deferred close will happen before the next iteration begins. A variant of this may be that you use an anonymous function (a function literal) in the loop's body, where you also have the chance to use defer, e.g.:

for _, v := range values {
    func() {
        stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
        if err != nil {
            // handle error
            return
        }
        defer stmt.Close()

        // Use stmt...
    }()
}