MySQL statement:
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?
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.
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....
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:
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.
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.
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.