Re: Issue on UPSERT when concurrency is high

[Rick James]

That timestamp looks like only 1-second resolution. What if both the INSERT and UPDATE were done in the same second? Seems like you don't have enough resolution?

Do the UPDATEs always have all the columns? If not, the cannot be turned into this UPSERT.

Is it just this one table that is involved?

I would suggest staging the changes in a separate table, clean up various things in the staging table, only then copy to the real table. This will speed things up for various reasons.

Here is more on 'staging'. It is not aimed at your particular case, but I think the ping-ponging, etc, would benefit you.
https://mariadb.com/kb/en/mariadb/data-warehousing-high-speed-ingestion/