英文:
Thread safety of sql.Result.LastInsertId()
问题
SQL文档中提到LAST_INSERT_ID()是基于“每个连接”的基础工作的，也就是说，通过其他连接执行的INSERT语句不会覆盖最后插入的ID值。
据我了解，在Go语言中（与PHP不同），我们不会在每个客户端请求上创建单独的数据库连接。相反，我们被告知只创建一个sql.DB对象实例，该对象在内部管理一个SQL连接池。因此，他们说，不能保证Go程序中的两个连续SQL语句（即使在同一个线程中）将通过同一个数据库连接执行。因此，相反的情况可能发生-两个不同的线程可以在同一个（重用的）数据库连接上执行两个不同的SQL语句。
问题是：sql.DB内部的自动连接管理是否会影响sql.Result.LastInsertId()的线程安全性？
考虑以下情况：在一个线程中的INSERT语句之后，sql.DB对象在另一个线程中重用了连接，并且另一个线程在该相同的（重用的）连接上执行了另一个INSERT语句。然后，第一个线程查询sql.Result.LastInsertId()。
这将返回第二个INSERT的行ID还是第一个INSERT的行ID？最后插入的ID是否在语句执行时被缓存，还是会导致向数据库连接发送单独的语句？
英文:
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?
答案1
得分: 5
MySQL客户端-服务器协议在响应数据包中返回LAST_INSERT_ID()的值，该值是执行INSERT操作的查询的结果。通常，客户端API使用诸如sql.Result.LastInsertId()之类的方法将该值返回给客户端代码。不需要往返查询。
所以，对于你的问题，答案是“第一个INSERT”。
需要明确的是，MySQL连接在广义上并不是线程安全的。相反，它们是可串行重用的资源。多线程客户端环境通过管理串行重用来使其看起来是线程安全的。你在问题中描述了这在golang中是如何工作的。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论