Postgres中ON CONFLICT锁机制及内部工作原理问询

1. Postgres中的ON CONFLICT是否会使用数据库锁来确保不会出现重复行？ #

会，不过锁的类型和粒度取决于你指定的冲突目标（唯一约束或唯一索引）。当执行ON CONFLICT语句时，Postgres会先尝试获取冲突目标对应键值的共享锁，以此排查是否存在重复。如果检测到冲突，会根据后续指定的动作（DO UPDATE或DO NOTHING）调整锁的强度——比如DO UPDATE会将共享锁升级为排他锁，确保后续的修改操作不会被并发事务干扰。整个过程通过锁机制避免了并发场景下的竞态问题，严格保证唯一约束的一致性，不会出现重复行。

2. ON CONFLICT DO NOTHING是否会锁定表以避免插入重复行？ #

不会锁定整张表，只会锁定冲突的唯一键对应的索引条目，采用的是轻量级的共享锁。这种锁的粒度非常细，只会影响针对同一唯一键的并发操作，完全不会干扰其他不冲突行的插入、更新或删除。举个例子：多个会话同时插入不同的唯一键值时，所有操作都可以并行执行；只有当多个会话插入同一个唯一键时，才会触发锁竞争——其中一个会话成功插入，其他会话执行DO NOTHING，锁会在事务结束后自动释放。

3. 不同场景下ON CONFLICT的内部工作机制说明 #

场景1：无冲突的正常插入 #

当插入的键值在目标唯一约束/索引中不存在时，Postgres会直接执行插入操作，不会额外获取冲突相关的锁（仅持有常规插入所需的行级锁），流程和普通INSERT完全一致。

场景2：ON CONFLICT DO NOTHING触发冲突 #

1. 会话尝试插入数据时，先对目标唯一键值获取共享锁；
2. 检查唯一索引，发现已有匹配的键值条目；
3. 放弃插入操作，不做任何数据修改；
4. 事务提交后，释放之前获取的共享锁。
   这种场景下锁的粒度极细，并发友好，不会阻塞其他不冲突的数据库操作。

场景3：ON CONFLICT DO UPDATE触发冲突 #

1. 会话尝试插入数据时，先获取目标唯一键值的共享锁；
2. 检测到冲突后，将共享锁升级为排他锁；
3. 执行UPDATE子句修改现有行的指定字段；
4. 事务提交后，释放排他锁。
   排他锁会阻塞其他针对同一唯一键的插入、更新操作，确保整个“冲突-更新”过程的原子性，避免出现数据不一致。

场景4：基于部分唯一索引的ON CONFLICT #

如果冲突目标是部分唯一索引（比如CREATE UNIQUE INDEX idx_user_active_email ON users (email) WHERE active = true;），Postgres只会检查索引覆盖的行范围：只有当插入的行满足索引的过滤条件（如active = true）且键值（如email）重复时，才会触发冲突。锁机制同样只针对符合部分索引条件的键值获取，不会影响不符合条件的行操作。

内容的提问来源于stack exchange，提问作者Asnim P Ansari