前置知识:
ON DUPLICATE KEY UPDATE
MySQL 特有的 SQL 语句, 如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。
MySQL的锁
S锁（Shared Lock）是一种共享锁，多个事务可以同时对同一资源加上S锁，读取资源但不进行修改。 这样可以提高并发性能，允许多个事务同时读取同一资源。
X锁（Exclusive Lock）是一种独占锁，一个事务对某一资源加上X锁后，其他事务无法再对该资源进行读取或修改操作，直到该事务释放锁
AUTO-INC锁 是表级锁，如果一张表中有自增列（id int NOT NULL AUTO_INCREMENT）那么当向这张表 插入(INSERT-LIKE) 数据时，InnoDB会先获取这张表的AUTO-INC锁。如果一个事务正在插入数据到有自增列的表时，其他事务会阻塞等待正在持有AUTO-INC锁的事务释放AUTO-INC锁。
死锁问题
insert ... on duplicate key update 本质是将 insert 和 update 两个语句放在同一个事务中执行, 在事务隔离级别为可重复读时分别对insert ... on duplicate key update, insert, update 流程进行分析:
首先说一下可 InnoDB 引擎实现可重复读的策略(也就是为了解决幻读问题的策略):
- 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
- 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
insert ... on duplicate key update, insert, update 均为当前读，所以无一例外都会在需要的时候加上 next-key 的 S 锁避免幻读。
有必要吐槽一下，update 本身就是会导致幻读发生的原因。因为 update 默认读最新提交，会把其他事务已提交的 insert 直接修改（自己读不到但是能修改）导致幻读。
接下来我们模拟一个十分常见的累加计数场景：
以打榜为例，现在“年度黑子投票”榜上有一个练习时长两年半的偶像练习生已经有 100 票，下面有两个客户端发起了投票请求，与此同时可开启了两个事务，服务端选择使用 insert ... on duplicate key update 来更新，因为这样更有效率，于是开启了两个事务。
| 时刻 | 事务A | 事务B |
|---|---|---|
| T1 | begin | |
| T2 | 尝试 insert 发现冲突，加上 S 锁 | begin |
| T3 | 尝试 insert 发现冲突，加上 S 锁 | |
| T4 | 尝试 update，此时需要对记录上 X 锁（被事务B的 S 锁阻塞） | |
| T5 | 尝试 update，此时需要对记录上 X 锁（被事务A的 S 锁阻塞） |
恭喜成功触发死锁。
但是假如我们将 update 和 insert 分开来单语句执行，是不可能发生死锁的，InnoDB 单语句执行时是自动事务，流程如下：
| 时刻 | Insert 语句 A | Insert 语句 B |
|---|---|---|
| T1 | begin | |
| T2 | 尝试 insert 发现冲突，加上 S 锁 | begin |
| T3 | 尝试 insert 发现冲突，加上 S 锁 | |
| T4 | commit 释放锁并返回异常 | |
| T5 | commit 释放锁并返回异常 |
由于 S 锁可共享不会相互阻塞，所以两个语句不会冲突。
| 时刻 | Update 语句 A | Update 语句 B |
|---|---|---|
| T1 | begin | |
| T2 | 尝试 update，此时需要对记录上 X 锁 | begin |
| T3 | 尝试 update，此时需要对记录上 X 锁（被事务A的 X 锁阻塞） | |
| T4 | commit 释放锁 | |
| T5 | 阻塞结束，尝试 update，此时需要对记录上 X 锁 | |
| T6 | commit 释放锁 |
X 锁会导致 update 阻塞，等待第一个语句执行完成后第二个才能继续执行。
X 锁和 S 锁是相互阻塞的，在可重复读的事务隔离级别下，insert ... on duplicate key update 在同一个事务里面使用同时使用 insert 和 update 就导致了死锁。
其实假如多个可重复读事务在同时连续 Insert 和 update 的时候，也会触发死锁，但是这种情况是可以主动避免，而
insert ... on duplicate key update无法避免。
解决方案：
目前很容易想到的解决方案有这样几个：
- 将 update 和 insert 分开使用而不是直接使用 on duplicate key update，每次优先 update (如果涉及到线程冲突的场景，可以尝试乐观锁模式，先用 select 得到版本号后 CAS），判断返回值是否为 空，再去 insert。
- 避免
insert ... on duplicate key update的并发执行，在事务前上锁（串行化策略，并发能力极低）。
是否降低隔离级别就有效了呢？
我们都知道可重复读会上间隙锁避免幻读，那对于没有这个要求的读已提交隔离级别，就不会出现 S 锁，也就不会导致死锁了呢，目前查到的资料来看，貌似 insert 在唯一索引冲突发生的时候仍然会加上 gap-lock，但我的实验还没做验真。
不过既然是不稳定的因素，那么我们还是尽量避免这样的情况发生。
主键疯狂自增问题
主键跳跃自增问题其实来自insert，当insert语句执行的时候，首先会对表上一个 AUTO-INC 锁，来保证主键的单调自增，这个锁在insert语句完成的时候会自动释放，并且，这个锁是不可逆的，即使insert执行失败（比如唯一索引冲突），或者事务回滚，自增锁也不会回退，这就导致了自增主键会不连续的情况。主键跳跃有时候会带来很严重的问题，因为执行失败的语句不会写入 binlog，当我们对 MySQL 分片进行主从复制的时候，从服务器直接 replay 反而会带来主服务和从服务器不一致的情况，这个时候就只能使用 raw 格式复制整个服务器的原始数据才能保证主从之间的一致性。
有必要注意的是，事务回滚的 redo 是通过执行反向操作语句（insert-delete，update-update）来回滚的，这些并不会导致从服务器复制时 repaly 结果的不一致。
了解 insert 带来的主键跳跃问题，也就很好理解为什么 insert ... on duplicate key update 会带来主键疯狂自增的问题，虽然我们期望是在发生唯一索引冲突的时候直接 update 就可以，但是实际情况是每次语句都执行了一次 insert 导致自增锁自增一次，这种做法会导致严重的主键资源浪费，但不会导致 replay 失败。
解决方案：
- 如果是增多改少的环境，主键资源的浪费其实还是可以接受的，比如增加了1000条记录才有一个 on duplicate key 触发了 update，这种情况下主键资源没有多少浪费。
- 如果是改多增少的环境，建议采用先 update 如果失败再 insert 的方案，这样可以尽量减少对数据库的IO，并且 update 本身自带 X 锁，不存在并发问题。
自增锁的一些补充
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
- 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1：
- 普通 insert 语句，自增锁在申请之后就马上释放；
- 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题。
所以，当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。