MySQL statement:
MySQL声明：
INSERT ... ON DUPLICATE KEY UPDATE ...
INSERT ... ON DUPLICATE KEY UPDATE ...
appears like a single statement that should be thread-safe (in the sense that no concurrent query on the same data can happen during its execution). But on the other hand, it could be internally decomposed into insert and update statements, and I'm wondering if it's still guaranteed to be thread safe?
看起来像一个应该是线程安全的语句（在某种意义上说，在执行期间不会发生对同一数据的并发查询）。但另一方面，它可以在内部分解为插入和更新语句，我想知道它是否仍然保证是线程安全的？
I mean, for example, could there be a delete in between the insertand update coming from a concurrent thread that would cause the update to fail?
我的意思是，例如，来自并发线程的插入和更新之间是否会有删除会导致更新失败？
I think insert on duplicate key update should be thread safe, but the docs do not seem to state it in clear text. Could anyone please provide a proof link on the subject? How is it internally implemented?
我认为重复密钥更新上的插入应该是线程安全的，但是文档似乎没有用明文说明它。有人可以提供关于这个问题的证明链接吗？它是如何在内部实施的？
1 个解决方案
#1
1
RDBMSs use locks and isolation levels to control how parallelly running sessions can access the same data. There is no such thing as thread safety, nor session safety. There is only concurrency control.
RDBMS使用锁和隔离级别来控制并行运行的会话如何访问相同的数据。没有线程安全，也没有会话安全。只有并发控制。
If you use the myisam table type, then all data modification statements lock the entire table (myisam can do concurrent inserts under limited circumstances, but the delete does require a table lock all the time). Therefore, a delete cannot interfere with an insert ... on duplicate key update....
如果使用myisam表类型，则所有数据修改语句都会锁定整个表（myisam可以在有限的情况下执行并发插入，但删除操作始终需要表锁）。因此，删除不能干扰插入...重复键更新....
If you use the innodb table type, then the situation is a bit more complex because it applies row level locks. As mysql manual says on the locks set by various sql statements:
如果使用innodb表类型，则情况稍微复杂一些，因为它应用了行级锁。正如mysql手册所说的各种sql语句设置的锁：
INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.
INSERT ... ON DUPLICATE KEY UPDATE与简单的INSERT的不同之处在于，当发生重复键错误时，在要更新的行上放置独占锁而不是共享锁。对重复的主键值采用独占索引记录锁定。对于重复的唯一键值，采用独占的下一键锁定。
So, first mysql locks the new record for the insert. If a duplicate key error occures, only then is the duplicate record locked. Technically, it is possible that between the duplicate key error and placing the exclusive lock on the duplicate record, a delete statement removes the duplicate record. However, this will not make the update statement fail. It will simply not update any records.
所以，首先mysql锁定插入的新记录。如果发生重复键错误，则仅锁定重复记录。从技术上讲，在重复键错误和在重复记录上放置独占锁之间，删除语句可能会删除重复记录。但是，这不会使更新语句失败。它不会更新任何记录。
But this would require a very accurate timing of the delete statement.
但这需要一个非常准确的删除语句时间。
The insert ... on duplicate key update... statement may fail only if the delete statement acquires the exclusive lock on the duplicate record after the duplicate key error and holds the lock so long that the insert's transaction times out.
只有当delete语句在重复键错误之后获取重复记录上的独占锁并且保持锁定以使插入的事务超时时，insert ... on duplicate key update ...语句才会失败。
#1
1
RDBMSs use locks and isolation levels to control how parallelly running sessions can access the same data. There is no such thing as thread safety, nor session safety. There is only concurrency control.
RDBMS使用锁和隔离级别来控制并行运行的会话如何访问相同的数据。没有线程安全，也没有会话安全。只有并发控制。
If you use the myisam table type, then all data modification statements lock the entire table (myisam can do concurrent inserts under limited circumstances, but the delete does require a table lock all the time). Therefore, a delete cannot interfere with an insert ... on duplicate key update....
如果使用myisam表类型，则所有数据修改语句都会锁定整个表（myisam可以在有限的情况下执行并发插入，但删除操作始终需要表锁）。因此，删除不能干扰插入...重复键更新....
If you use the innodb table type, then the situation is a bit more complex because it applies row level locks. As mysql manual says on the locks set by various sql statements:
如果使用innodb表类型，则情况稍微复杂一些，因为它应用了行级锁。正如mysql手册所说的各种sql语句设置的锁：
INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.
INSERT ... ON DUPLICATE KEY UPDATE与简单的INSERT的不同之处在于，当发生重复键错误时，在要更新的行上放置独占锁而不是共享锁。对重复的主键值采用独占索引记录锁定。对于重复的唯一键值，采用独占的下一键锁定。
So, first mysql locks the new record for the insert. If a duplicate key error occures, only then is the duplicate record locked. Technically, it is possible that between the duplicate key error and placing the exclusive lock on the duplicate record, a delete statement removes the duplicate record. However, this will not make the update statement fail. It will simply not update any records.
所以，首先mysql锁定插入的新记录。如果发生重复键错误，则仅锁定重复记录。从技术上讲，在重复键错误和在重复记录上放置独占锁之间，删除语句可能会删除重复记录。但是，这不会使更新语句失败。它不会更新任何记录。
But this would require a very accurate timing of the delete statement.
但这需要一个非常准确的删除语句时间。
The insert ... on duplicate key update... statement may fail only if the delete statement acquires the exclusive lock on the duplicate record after the duplicate key error and holds the lock so long that the insert's transaction times out.
只有当delete语句在重复键错误之后获取重复记录上的独占锁并且保持锁定以使插入的事务超时时，insert ... on duplicate key update ...语句才会失败。