线上做热点行计数，两套 SQL 都能实现「不存在就插入、存在就更新」：REPLACE INTO 和 INSERT ... ON DUPLICATE KEY UPDATE。业务语义看起来几乎一样，但在唯一索引冲突时，二者在 InnoDB 里走的路径完全不同：一个先删后插，一个先尝试插入再改走更新。路径不同，产生的锁就不同，锁不同，并发下的死锁概率和主从延迟就不同。这道面试题真正想考的是：你知不知道 REPLACE INTO 在冲突时会拿到两条记录上的排他锁（且顺序与二级唯一索引有关），并且在并发事务里比 ON DUPLICATE KEY UPDATE 更容易制造死锁。下面结合可复现的建表、会话时序和真实报错把这个差异讲透。
一、先固定实验环境与表结构
所有结论都基于同一张表，请在 MySQL 8.0（5.7 亦可，差异会在文中标注）上执行。注意这里刻意建了两个唯一索引：主键 id 和一个二级唯一索引 uniq_k，因为二级唯一索引的存在会显著改变加锁情况。
CREATE DATABASE IF NOT EXISTS lockdemo DEFAULT CHARACTER SET utf8mb4;
USE lockdemo;
DROP TABLE IF EXISTS counter;
CREATE TABLE counter (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
biz_key VARCHAR(64) NOT NULL,
cnt INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uniq_k (biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO counter (biz_key, cnt) VALUES ('a', 1), ('b', 2);
确认隔离级别与自动提交状态，避免实验结论被默认值干扰：
SELECT @@transaction_isolation; -- MySQL 8.0；5.7 用 @@tx_isolation
-- 期望输出：REPEATABLE-READ
SELECT @@autocommit; -- 期望输出：1
实验中请使用两个独立会话（下文称会话 A、会话 B），并在会话里显式 BEGIN，这样锁会一直持有到 COMMIT，便于观察。
二、看锁的第一手工具：performance_schema.data_locks
MySQL 8.0 用 performance_schema.data_locks 取代了 5.7 的 information_schema.innodb_locks。每次执行完一条语句但未提交时，在第三个会话里查询：
SELECT ENGINE_TRANSACTION_ID AS trx,
OBJECT_NAME,
INDEX_NAME,
LOCK_TYPE,
LOCK_MODE,
LOCK_STATUS,
LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'counter'
ORDER BY INDEX_NAME, LOCK_DATA;
字段含义需要记住，后面全靠它判断：LOCK_TYPE=TABLE 表示表级意向锁（IX 之类）；LOCK_TYPE=RECORD 才是行级锁；LOCK_MODE 为 X 是排他，X,GAP 是间隙锁，X,REC_NOT_GAP 是仅锁记录本身，X,GAP,INSERT_INTENTION 是插入意向锁（它和间隙锁冲突，是死锁的常见来源）。LOCK_DATA 显示被锁记录的索引键值，主键是数字，二级唯一索引是字符串。LOCK_STATUS=WAITING 表示正在等锁，这是定位阻塞点的关键。
5.7 上等价查询是
SELECT * FROM information_schema.innodb_locks;，字段名不同但语义一致，看不到 GAP 的显式标注时可用SHOW ENGINE INNODB STATUS\G的 TRANSACTIONS 段辅助判断。
三、REPLACE INTO 的加锁路径：先删后插，两把 X 锁
REPLACE INTO 的执行语义是：尝试插入，若遇到主键或唯一索引冲突，则先删除冲突行，再插入新行。删除和插入是两个独立的行操作，因此对冲突记录会先加一把删除用的排他锁，再对二级唯一索引加插入意向锁与索引记录锁。会话 A 执行并保持未提交：
-- 会话 A
BEGIN;
REPLACE INTO counter (biz_key, cnt) VALUES ('a', 100);
-- 先不要 COMMIT
此时在第三个会话查 data_locks，会看到类似结果：
| INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA | 解读 |
|---|---|---|---|---|
| PRIMARY | RECORD | X,REC_NOT_GAP | 1 | 原记录 id=1 被删除，持有记录锁 |
| uniq_k | RECORD | X,REC_NOT_GAP | ‘a’ | 二级唯一索引上的冲突项也被排他锁定 |
| uniq_k | RECORD | X,GAP | ‘a’ | 为后续插入预留的间隙/插入位置锁 |
| PRIMARY | RECORD | X,REC_NOT_GAP | 3 | 新插入的自增主键（id 不从 1 复用） |
两个容易踩的点由此暴露：
- 自增值被消耗。
REPLACE的删除再插入会让AUTO_INCREMENT前进，原来 id=1 的行变成 id=3，主键不再稳定。业务里若把主键当外部引用，这就是灾难。 - 锁范围更大、持有两套锁。删除锁 + 插入锁同时存在，且二级唯一索引上出现间隙锁，导致别的会话在
'a'附近做插入时被阻塞。会话 B 此时执行相同语句会直接卡住：
-- 会话 B
BEGIN;
REPLACE INTO counter (biz_key, cnt) VALUES ('a', 200);
-- 阻塞，直到会话 A COMMIT / ROLLBACK
在会话 B 上查看等待：
SELECT * FROM performance_schema.data_lock_waits\G
若一直不释放，超过 innodb_lock_wait_timeout（默认 50 秒）后会话 B 报出真实错误：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
另一个高频副作用是被删掉的旧行会留下「删除标记」，而 REPLACE 在存在外键 ON DELETE CASCADE 时会真的触发级联删除。这一点在面试里几乎是必问的加分项。
四、ON DUPLICATE KEY UPDATE 的加锁路径：先插再改，锁在已冲突行
INSERT ... ON DUPLICATE KEY UPDATE 的路径是：先做一次真正的插入尝试，遇到唯一冲突后不删除，而是改走更新分支，对已存在的行做 UPDATE。因此它通常只需要一把针对冲突行的排他记录锁，事务提交前旧行一直存在。
-- 会话 A（新事务）
BEGIN;
INSERT INTO counter (biz_key, cnt) VALUES ('a', 100)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
-- 不要 COMMIT
查看 data_locks，典型结果为：
| INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA | 解读 |
|---|---|---|---|---|
| PRIMARY | RECORD | X,REC_NOT_GAP | 1 | 被更新的那行 |
| uniq_k | RECORD | X,REC_NOT_GAP | ‘a’ | 唯一索引项本身的排他锁 |
对比第三节可以看到：少了新主键的插入记录锁，也没有出现额外的新间隙锁，主键 id 保持为 1。这就是大家常说的「ON DUPLICATE KEY UPDATE 只锁冲突行」的来源。
但有两处细节必须说清，否则结论会被面试官反问推翻：
- 更新列的值没变化时，锁会降级吗？ 在可重复读下，若
UPDATE子句实际未改变任何列值，InnoDB 可能不加行锁（取决于版本与执行计划，可用data_locks实测）。但不能依赖这一点，因为一旦值确实变了，锁就是实打实的X。 - 更新的是唯一索引列本身，例如把
biz_key改成另一个值，加锁会变成「删除原索引项 + 插入新索引项」，此时ON DUPLICATE KEY UPDATE与REPLACE的锁边界会趋于接近，仍然可能产生间隙锁。
五、并发交织下的差异：REPLACE 更容易死锁
单条语句的加锁对比只是静态结论。真正的分水岭出现在两个事务交叉操作相邻键时。下面这个时序在线上非常典型（表只有 uniq_k 一个唯一索引，两个键相邻）：
- 会话 A：
BEGIN; REPLACE INTO counter (biz_key, cnt) VALUES ('a', 1);，持有'a'的删除锁与插入位置锁。 - 会话 B：
BEGIN; REPLACE INTO counter (biz_key, cnt) VALUES ('b', 1);，持有'b'附近的锁。 - 会话 A：执行
REPLACE INTO counter (biz_key, cnt) VALUES ('b', 2);，需要'b'的插入位置，等待 B。 - 会话 B：执行
REPLACE INTO counter (biz_key, cnt) VALUES ('a', 2);，需要'a'的插入位置，等待 A。
因为两侧都是「删除 + 在二级唯一索引的间隙上申请插入意向锁」，而插入意向锁与已有间隙锁互斥，即刻形成环路。InnoDB 检测到后立即杀掉其中一个事务，报出真实错误：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
用 SHOW ENGINE INNODB STATUS\G 的 LATEST DETECTED DEADLOCK 段可以看到两条 SQL 的 lock_mode X locks gap before rec insert intention 互相等待，这正是判定依据。
把上述第 1、2 步换成 INSERT ... ON DUPLICATE KEY UPDATE cnt = cnt + 1，由于不删除旧行、不需要在相邻键间隙上申请插入意向锁，环路通常不成立，死锁概率大幅下降。这也是为什么「热点行计数」场景推荐 ON DUPLICATE KEY UPDATE 而不是 REPLACE。
还有一点常被忽略：ON DUPLICATE KEY UPDATE 的返回值语义。INSERT 成功影响行数为 1，冲突被更新为 2，若冲突但更新值与旧值相同则为 0。应用侧若靠 getUpdateCount() 判断是插入还是更新，必须按 1/2/0 三种情况处理，否则统计口径会出错。
六、工程结论：选型与避坑清单
基于上面的锁定行为，可以把结论收敛成一张判断表：
| 维度 | REPLACE INTO | ON DUPLICATE KEY UPDATE |
|---|---|---|
| 冲突时行为 | 删除旧行 + 插入新行 | 原地更新 |
| 主键稳定性 | 不稳定（自增前进、主键变化） | 稳定 |
| 自增值消耗 | 命中冲突也消耗 | 命中冲突通常不消耗 |
| 二级唯一索引间隙锁 | 常见 | 一般没有（除非改唯一索引列） |
| 并发死锁倾向 | 较高，尤其键相邻时 | 较低 |
| 外键级联 | 可能触发 ON DELETE CASCADE | 不触发 |
| 适用场景 | 整行幂等覆盖、且无外部引用主键 | 计数、累加、部分字段更新 |
- 批量场景注意：
REPLACE INTO ... VALUES (...),(...)与INSERT ... ON DUPLICATE KEY UPDATE多值写法，都是逐行加锁，行数越多持有锁越多；批大小建议控制在 500~1000 行并显式分事务提交，减少锁等待与主从延迟。 - 关闭自动提交事务：这两类语句务必放在短事务里，且必须显式
COMMIT。会话超时后未提交事务在 8.0 中可由innodb_rollback_on_timeout与wait_timeout配置影响行为，排查时先看information_schema.innodb_trx里的trx_state与trx_started。 - 死锁后的重试：捕获
1213与1205后按「指数退避 + 最多 3 次」重试，且重试必须新建事务，不能复用已回滚的连接状态。 - 热点打散：若确实出现热点行锁竞争，可在业务层把一行拆成 N 行（如
biz_key拼接分片号），读取时聚合，这比调大innodb_lock_wait_timeout更有效。
小结
两者都依赖唯一索引冲突来触发「存在则覆盖」的语义，但 REPLACE INTO 是删除加插入，会在主键和二级唯一索引上各留一套排他锁与间隙锁，主键被重写、自增被消耗、并发相邻键时极易形成插入意向锁互等；INSERT ... ON DUPLICATE KEY UPDATE 是先插入失败再原地更新，锁只落在冲突行上，主键稳定，死锁概率低。面试时只要能把「删除 + 插入 → 两把 X 锁 + 间隙锁 → 插入意向锁冲突 → 1213 死锁」这条因果链讲出来，并用 performance_schema.data_locks 佐证，就有足够的说服力。
FAQ
1. 为什么 REPLACE 之后 id 从 1 变成了 3，而不是 2？
因为 REPLACE 的删除再插入分两步：插入前先从自增计数器取号，删除的旧行号不会回退，已被消耗的自增值也不会复用。若表里已有 id=1、2 两行，新插入拿到 3 属正常。要避免主键跳动，就别把自增主键暴露给业务。
2. ON DUPLICATE KEY UPDATE 真的完全不会加间隙锁吗？
不是绝对。当 UPDATE 子句修改了唯一索引列（包括把它改成另一个值）、或者 UPDATE 走了非唯一索引的等值条件并在 RR 下产生范围扫描，都可能出现间隙锁。用 data_locks 实测确认，不要凭直觉写结论。
3. 报 1205 与 1213 有什么区别，处理方式一样吗？
1205 是 Lock wait timeout exceeded，表示单条语句等锁超时（默认 innodb_lock_wait_timeout=50 秒），语句回滚但事务可能仍在；1213 是 Deadlock found，InnoDB 主动选牺牲者并回滚整个事务。前者优先排查长事务与未提交连接，后者优先排查加锁顺序与 SQL 写法，两者都建议捕获后重试。
4. 计数场景能不能用 REPLACE 但把值算好再写？
技术上可行但不是好选择：你得先 SELECT 再写回，多一次往返且仍要面对删除加插入带来的间隙锁与主键重写。这类场景直接 UPDATE counter SET cnt = cnt + 1 WHERE biz_key = ?，配合「更新影响行数为 0 时再 INSERT」的两步法，或者直接用 ON DUPLICATE KEY UPDATE，都更稳。
5. 为什么同样的 SQL 在测试环境从不死锁，上线就报 1213？
死锁依赖并发时序与索引数据分布。测试环境并发低、数据量小、键相邻关系不存在，间隙锁难形成环路。上线前应做并发压测，并抓取 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段，核对两侧 SQL 的锁模式是否包含 insert intention，再针对性地调整 SQL 或拆分热点键。