MySQL保留唯一索引时如何仅在值冲突场景下对表加锁？

你遇到的阻塞并非表锁，是InnoDB在**可重复读（RR，MySQL默认事务隔离级别）下为唯一索引添加的间隙锁（Gap Lock）**导致的：

• 唯一索引需要保证值全局唯一，RR隔离级别下为了避免幻读，InnoDB对更新、插入唯一索引的操作，会额外加间隙锁锁定唯一值所在的区间，而非仅锁定目标行
• 你举的测试场景中，如果两个更新操作要设置的foobar_id落在同一个间隙区间内，间隙锁范围重叠就会导致第二个事务被阻塞
• 普通索引不需要保证全局唯一性，不会触发间隙锁逻辑，因此你测试非唯一键时不会出现阻塞

可行解决方案 #

方案1：调整事务隔离级别为读提交（RC）（最推荐） #

这是成本最低、适用场景最广的方案，RC隔离级别下会自动关闭间隙锁，仅对实际修改的记录加行锁，只要更新的foobar_id值不冲突就不会互相阻塞。

1. 临时对当前会话生效执行：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. 全局永久生效可以修改MySQL配置文件my.cnf/my.ini，添加配置后重启服务：

[mysqld]
transaction-isolation = READ-COMMITTED

注意：RC级别下不再具备RR级别的防幻读能力，会出现不可重复读问题，但绝大多数业务场景都能接受该取舍，同时RC级别还能减少大量不必要的锁冲突，整体数据库并发性能会明显提升。

方案2：保留RR隔离级别的兼容方案 #

如果业务必须使用RR隔离级别，可按以下方式调整：

• 执行更新前先通过主键查询出目标行当前的foobar_id，如果要更新的新值和旧值完全一致，直接跳过更新操作，避免不必要的唯一键修改触发锁
• 若业务允许，可将唯一约束校验上移到应用层，搭配分布式锁保证唯一性，去掉数据库层面的唯一键。该方案会提升业务复杂度，若锁逻辑有漏洞容易出现重复数据，不优先推荐

内容的提问来源于stack exchange，提问作者cr001