使用LOCK TABLES跨表插入数据时遇1100错误求助

问题 #

我正在创建包含多张关联表的数据库，其中students表与genders表关联。在Docker容器初始化阶段加载测试数据时，需要从genders表取值插入students表。尝试用LOCK TABLES语句操作时，始终报错：

ERROR 1100 (HY000) at line 2: Table 'genders' was not locked with LOCK TABLES

试过不同数据库版本，且数据库启动后手动执行也会触发同样错误，但该操作在在线数据库测试环境中能正常运行。

表结构DDL #

CREATE TABLE genders (
  id_gender int(1) NOT NULL,
  gender_name varchar(50) NOT NULL,
  PRIMARY KEY (id_gender)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE students (
  id_student int(11) NOT NULL,
  student_name varchar(50) DEFAULT NULL,
  gender_id int(1) DEFAULT NULL,
  student_date_of_birth bigint(20) DEFAULT NULL,
  PRIMARY KEY (id_student)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE students ADD CONSTRAINT students_genders_FK FOREIGN KEY (gender_id) REFERENCES genders(id_gender);

测试数据加载脚本 #

LOCK TABLES genders WRITE;
INSERT INTO genders (id_gender, gender_name) VALUES
(1,'Female'),
(2,'Male');
UNLOCK TABLES;

LOCK TABLES students WRITE, genders READ;
INSERT INTO students (id_student, student_name, gender_id, student_date_of_birth) VALUES
(1, '<Student Name>', (SELECT id_gender FROM genders WHERE gender_name = 'Female'),   <Student Date Of Birth>),
-- ... 省略其他数据行
(95, '<Student Name>', (SELECT id_gender FROM genders WHERE gender_name = 'Male'),   <Student Date Of Birth>);

问题原因与解决方法 #

报错的核心原因是：在LOCK TABLES之后使用子查询引用表时，MySQL要求必须为该表的别名也加锁——哪怕你没显式指定别名，MySQL会自动给子查询里的表分配一个隐式别名，而这个别名对应的表没被锁定，就会触发错误。

你可以采用以下两种解决方式：

方式1：用变量存储查询结果后插入 #

提前查询出性别ID并存储到变量中，插入数据时直接引用变量，避免子查询带来的锁表问题：

LOCK TABLES genders READ, students WRITE;
-- 预查询性别ID
SET @female_id = (SELECT id_gender FROM genders WHERE gender_name = 'Female');
SET @male_id = (SELECT id_gender FROM genders WHERE gender_name = 'Male');
-- 使用变量插入数据
INSERT INTO students (id_student, student_name, gender_id, student_date_of_birth) VALUES
(1, '<Student Name>', @female_id, <Student Date Of Birth>),
-- ... 省略其他数据行
(95, '<Student Name>', @male_id, <Student Date Of Birth>);
UNLOCK TABLES;

方式2：显式指定别名并锁定别名表 #

给子查询中的genders表指定别名，同时在LOCK TABLES语句中包含该别名对应的锁：

LOCK TABLES students WRITE, genders READ, genders AS g READ;
INSERT INTO students (id_student, student_name, gender_id, student_date_of_birth) VALUES
(1, '<Student Name>', (SELECT id_gender FROM genders AS g WHERE g.gender_name = 'Female'),   <Student Date Of Birth>),
-- ... 省略其他数据行
(95, '<Student Name>', (SELECT id_gender FROM genders AS g WHERE g.gender_name = 'Male'),   <Student Date Of Birth>);
UNLOCK TABLES;

额外建议 #

如果你的初始化脚本是串行执行（无并发写入），完全可以去掉LOCK TABLES——InnoDB默认的事务隔离级别已经能保证数据一致性，锁表反而会限制并发能力，属于多余操作。

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