LOCK TABLES触发触发器时出现ERROR 1100 (HY000)错误排查

问题场景 #

我在schema db1中有表my_table，需迁移至同服务器的schema db2，为此在db1.my_table上创建增删改触发器同步操作至db2.my_table。开发环境正常，但本地迁移环境中db1和db2分属不同Docker容器的MySQL实例。

为解决本地复制触发器问题，我在触发器中添加IF语句，确认服务器存在db2才执行逻辑，示例AFTER INSERT触发器如下：

create trigger insert_my_table
    after INSERT
    on db1.my_table
    for each row
begin
    IF
        (SELECT EXISTS(SELECT SCHEMA_NAME
                       FROM INFORMATION_SCHEMA.SCHEMATA
                       WHERE SCHEMA_NAME = 'db2')) THEN
        insert into db2.my_table
        select *
        from db1.my_table ff
        where ff.id = NEW.id
        on duplicate key update db2.my_table.id=NEW.id;
    END IF;
end;

本地直接插入数据无问题，但执行LOCK TABLES后插入（如mysqldump逻辑）：

lock tables `my_table` write;

insert into my_table (id, name, description) value (1, 'test', 'test_desc');

会报错：

"ERROR 1100 (HY000): Table 'my_table' was not locked with LOCK TABLES"

错误原因 #

1. LOCK TABLES的作用范围限制：执行LOCK TABLES my_table WRITE;时，MySQL仅锁定当前会话默认schema下的my_table。触发器中明确访问了db1.my_table，若当前会话默认schema不是db1，或未显式锁定db1.my_table，触发器执行时会尝试访问未锁定的表，触发1100错误。
2. 全限定名表的锁定规则：即使当前默认schema是db1，用db1.my_table全限定名访问时，MySQL会将其视为与不带前缀的my_table独立的表实例，而你只锁定了不带前缀的表，导致触发器访问的表未被锁定。
3. 冗余的原表查询：触发器中通过SELECT * FROM db1.my_table WHERE id=NEW.id获取数据完全多余——NEW关键字已包含刚插入行的所有字段，无需再查询原表，这是引发锁定问题的直接根源。

解决方案 #

方案1：优化触发器逻辑（推荐） #

移除触发器中对db1.my_table的查询，直接使用NEW字段构造插入语句，彻底避免访问原表，消除锁定问题：

create trigger insert_my_table
    after INSERT
    on db1.my_table
    for each row
begin
    IF EXISTS(SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db2') THEN
        INSERT INTO db2.my_table (id, name, description) -- 明确列出字段，避免表结构变更导致异常
        VALUES (NEW.id, NEW.name, NEW.description)
        ON DUPLICATE KEY UPDATE 
            name = NEW.name,
            description = NEW.description; -- 仅更新需要同步的字段，主键无需重复更新
    END IF;
end;

方案2：修正LOCK TABLES语句 #

若必须保留原触发器逻辑，执行LOCK TABLES时需显式锁定db1.my_table（即使当前默认schema是db1）：

LOCK TABLES `db1`.`my_table` WRITE;

INSERT INTO `db1`.`my_table` (id, name, description) VALUES (1, 'test', 'test_desc');

注意：使用LOCK TABLES时，会话（包括触发器内）要访问的所有表都必须被显式锁定，否则会触发1100错误。

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