CODEWITHSUNDEEP
mysqlsqlmultithreadinggo
user1548418
user1548418

Reputation: 497

Thread safety of sql.Result.LastInsertId()

The SQL docs say that LAST_INSERT_ID() works on "per-connection" basis, that is, the last insert ID value will not be overwritten by INSERT statements executed through other connections.

AFAIU, in Go (unlike PHP for example) we don't create separate DB connections on each client request. Instead, we are told to create just one instance of sql.DB object, which manages a pool of SQL connections under the hood. Consequently, they say, there is no guarantee that two consecutive SQL statements in a Go program (even in the same thread) will be executed through the same DB connection. Therefore, the opposite could be the case – two different threads could execute two different SQL statements on the same (reused) DB connection.

The question is: could this automatic connection management inside sql.DB affect the thread safety of sql.Result.LastInsertId()?

Consider the following case: Right after the INSERT statement in one thread, the sql.DB object reuses the connection in another thread and the other thread executes another INSERT statement on that same (reused) connection. Afterwards, the first thread queries the sql.Result.LastInsertId().

Will this return row ID of the second INSERT or the first INSERT? Is the last insert ID cached at the moment of the statement execution, or is it causing a separate statement to be sent to the DB connection?

Upvotes: 3

Views: 667

Answers (1)

O. Jones
O. Jones

Reputation: 108651

The MySQL client-server protocol returns the value of LAST_INSERT_ID() in response packets to queries performing an INSERT operation. Generally the client APIs give that back to client code using methods like sql.Result.LastInsertId() in the SQL API. No round-trip query is required.

So the answer to your question is "the first INSERT."

To be clear, MySQL connections aren't thread safe in the broad sense. Instead, they are serially reusable resources. Multi-threaded client environments make them appear thread-safe by managing the serial reuse. You have described how that works for golang in your question.

Upvotes: 5

Related Questions