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()
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()