锁定表后执行INSERT..SELECT报错：Table 'table'未通过LOCK TABLES锁定

报错原因 #

使用LOCK TABLES锁定表后，在INSERT..SELECT的子查询中再次引用同一张表时，MySQL会将子查询中的表引用视为独立的表实例，而你只锁定了原表名，导致子查询中的表未被标记为已锁定，因此触发报错。

修复后的脚本方案 #

方案一：使用统一别名访问表

LOCK TABLES `table` AS t WRITE;

INSERT INTO t (id, fullName) 
SELECT '12', 'mathis plapp' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM t WHERE t.fullName='mathis plapp' LIMIT 1);

UNLOCK TABLES;

此方案通过给表起别名t，并锁定该别名，所有操作（包括子查询）都使用别名访问表，确保所有引用的表实例都已被锁定。

方案二：同时锁定原表名和别名

如果你需要保留原表名的使用，可以同时锁定原表和别名：

LOCK TABLES `table` WRITE, `table` AS t READ;

INSERT INTO `table` (id, fullName) 
SELECT '12', 'mathis plapp' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM `table` AS t WHERE t.fullName='mathis plapp' LIMIT 1);

UNLOCK TABLES;

额外注意事项 #

• 移除原脚本中的START TRANSACTION和COMMIT：LOCK TABLES会自动隐式提交当前事务，且在执行UNLOCK TABLES时会自动提交所有修改，无需额外的事务控制语句。
• MySQL 8.0中表锁与事务的兼容性有限，若后续有优化空间，优先考虑行级锁或添加唯一约束，但当前无法修改表结构的场景下，上述表锁方案可满足需求。

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