MySQL插入唯一索引重复值为何触发锁等待？非唯一索引则无锁？

1. 锁等待超时的原因 #

你的表中name字段设置了唯一索引，且默认值为'1'：

• 事务1执行insert into test_un_index(id) values (1)时，由于未指定name，会使用默认值'1'插入。InnoDB在维护唯一索引时，会对唯一索引idx_test_name中值为'1'的记录加上排他锁（X锁），确保唯一性约束不被破坏，且该锁会直到事务1提交/回滚才释放。
• 事务2执行insert into test_un_index(id) values (2)时，同样会使用默认值'1'插入。此时InnoDB会检查唯一索引，发现已有'1'的记录，需要获取该记录的锁来完成唯一性校验，但该锁被事务1持有且未释放，事务2进入锁等待状态，超过innodb_lock_wait_timeout设置的时间后，就触发了Lock wait timeout exceeded错误。

2. 改为非唯一索引后锁消失的原因 #

当idx_test_name改为非唯一索引时，InnoDB的锁机制发生了变化：

• 非唯一索引允许重复值存在，插入新记录时，InnoDB不需要对已存在的'1'记录加锁，而是直接在非唯一索引的合适位置插入新的索引条目（指向新的行记录）。
• 事务2插入时无需等待事务1释放任何锁，可以直接完成插入操作，因此不会出现锁等待超时。

关于show engine InnoDB status未找到锁详情的说明 #

你看不到锁信息大概率是因为事务2触发超时后已经自动回滚，对应的锁等待关系已经解除。如果要查看锁详情，需要在事务2报错之前（也就是处于锁等待状态时）执行show engine InnoDB status，此时在TRANSACTIONS段中就能看到事务2的锁等待信息，包括等待的锁类型、持有锁的事务ID等。

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