Statement.Close是否会影响golang中mysql的线程安全LAST_INSERT_ID？

Question

I'm using go to insert a new user into the mysql database. Before inserting the user I save some kind of "log-message" in the msg table. Both tables (msgand user) have auto-increment. In order to receive the id chosen by auto-increment I use mysql's LAST_INSERT_ID() function. This should be thread-safe as pointed out in lots of other discussions on stack overflow, because it's bound to a single connection.

I asked myself if stmt.Close() after every stmt.Exec() will change mysql's behavior (specially thread-safeness) in any way?

stmt, _ := db.Prepare("INSERT INTO msg (message) VALUES(?)")
stmt.Exec(msg)
stmt.Close()

stmt, _ = db.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&msgid)
stmt.Close()

stmt, _ = db.Prepare("INSERT INTO user (msg_id) VALUES(?)")
stmt.Exec(msgid)
stmt.Close()

stmt, _ = db.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&id)
stmt.Close()

Answer 1

Closing the statement, as you're doing, does not close the database connection. The database connection is what's important for LAST_INSERT_ID. Unless you call db.Close(), which closes the underlying connection, your connection remains open.

However, db is a pool of connections, so there is no guarantee that you'll get the same connection on subsequent queries, regardless of closing the connection, unless you use a transaction.

So in summary, you should do this (adding error handling, of course)

tx, _ := db.Begin()

stmt, _ := tx.Prepare("INSERT INTO msg (message) VALUES(?)")
stmt.Exec(msg)
stmt.Close()

stmt, _ = tx.Prepare("SELECT LAST_INSERT_ID()")
stmt.QueryRow().Scan(&msgid)
stmt.Close()

_ = tx.Commit()

---

However, note that a better way to do this is with Result.LastInsertId:

stmt, _ := tx.Prepare("INSERT INTO msg (message) VALUES(?)")
res, _ := stmt.Exec(msg)
stmt.Close()

msgid := res.LastInsertId()