带事务的MySQL存储过程加FOR UPDATE锁报ERROR 1746，求解决方案

我来帮你搞定这个头疼的问题！你遇到的ERROR 1746 (HY000): Can't update table 'table' while 'oneone' is being created.，本质是MySQL在同一个语句里同时创建临时表并对原表加FOR UPDATE锁，导致后续的更新操作和临时表创建的锁逻辑冲突了。

问题根源 #

当你执行create temporary table oneone as (select id,tt from table where status=0 limit 10 for update);时，这个语句会同时完成两个动作：

1. 对原表table中符合条件的行加行级锁
2. 创建临时表并填充查询结果

但在同一个事务里紧接着执行UPDATE时，临时表的创建操作还没完全释放相关的锁资源（或者说MySQL的锁机制不允许在临时表创建过程中修改原表），所以触发了这个错误。

修复方案：拆分临时表创建与锁操作 #

我们可以把“创建临时表”和“加锁查询插入”拆成两个独立步骤，先建空表，再通过INSERT ... SELECT ... FOR UPDATE的方式获取锁并填充数据，这样就能避免锁冲突。

修改后的存储过程代码如下：

use my_database;
drop procedure if exists sel_upd_ports;
delimiter $$
create procedure sel_upd_ports()
begin
    drop temporary table if exists oneone;
    start transaction;
    
    -- 1. 先创建与原表字段匹配的空临时表
    create temporary table oneone (
        id int, -- 请根据原表id的实际类型调整
        tt varchar(255) -- 请根据原表tt的实际类型调整
    );
    
    -- 2. 插入带FOR UPDATE锁的查询结果，此时会锁定选中的行
    insert into oneone(id, tt)
    select id, tt from `table` where status=0 limit 10 for update;
    
    -- 3. 执行更新操作，此时同一事务内可以访问被锁定的行
    update `table` a 
    join oneone b on a.id=b.id 
    set a.status=1;
    
    commit;
    -- 返回结果集
    select * from oneone;
    drop temporary table oneone;
end$$
delimiter ;

额外注意事项 #

• 原表名table是MySQL的关键字，一定要用反引号``包裹，避免潜在的语法错误
• 临时表的字段类型必须和原表完全一致，否则插入数据时会出现类型转换失败的问题
• FOR UPDATE会锁定选中的行直到事务提交，完美满足你并发场景下的数据一致性需求；如果业务允许跳过已锁定的行，可以考虑用SELECT ... FOR UPDATE SKIP LOCKED（需要MySQL 8.0+支持）

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