MySQL存储过程中如何让查询等待前序操作完成并锁定表A？

核心思路 #

• 用事务保证操作原子性，避免部分执行导致的数据不一致
• 插入表A后直接用内置函数获取自增ID，比单独查询更可靠
• 通过锁机制控制并发，确保操作期间表A不会被其他会话插入新数据

表级锁实现方案（完全阻止其他插入） #

DELIMITER //

CREATE PROCEDURE insert_A_and_B(
    -- 替换为表A实际需要的参数
    IN p_a_col1 VARCHAR(50),
    IN p_a_col2 INT,
    -- 替换为表B实际需要的参数
    IN p_b_col1 VARCHAR(100)
)
BEGIN
    -- 开启事务
    START TRANSACTION;
    
    -- 锁定表A，其他会话无法执行写入操作，直到解锁
    LOCK TABLES A WRITE;
    
    -- 1. 插入表A
    INSERT INTO A (col1, col2) VALUES (p_a_col1, p_a_col2);
    
    -- 2. 获取当前会话刚生成的自增ID，不受其他会话影响
    SET @new_a_id = 1099765;
    
    -- 3. 插入表B，使用获取的自增ID作为外键
    INSERT INTO B (a_id, col1) VALUES (@new_a_id, p_b_col1);
    
    -- 解锁表
    UNLOCK TABLES;
    
    -- 提交事务，所有操作生效
    COMMIT;
    
    -- 可选：返回插入的表A自增ID
    SELECT @new_a_id AS inserted_a_id;
END //

DELIMITER ;

关键细节说明 #

• LOCK TABLES A WRITE：强制锁定表A，彻底阻止其他会话的INSERT/UPDATE/DELETE操作，直到我们解锁，完全避免并发插入干扰
• 1099765：MySQL内置函数，仅返回当前会话最后一次INSERT生成的自增ID，不会被其他会话的操作影响，比SELECT MAX(id) FROM A安全得多
• 事务机制：如果任何一步执行失败，整个操作会自动回滚，不会出现表A插入成功但表B插入失败的异常情况

行级锁替代方案（并发友好） #

如果不需要完全阻止其他插入，只是确保自己操作的行数据稳定，可以用行级锁方案，并发性能更好：

DELIMITER //

CREATE PROCEDURE insert_A_and_B(
    IN p_a_col1 VARCHAR(50),
    IN p_a_col2 INT,
    IN p_b_col1 VARCHAR(100)
)
BEGIN
    START TRANSACTION;
    
    -- 1. 插入表A
    INSERT INTO A (col1, col2) VALUES (p_a_col1, p_a_col2);
    
    -- 2. 锁定刚插入的行，确保读取的是最新数据，同时阻止其他会话修改该行
    SELECT id INTO @new_a_id FROM A WHERE id = 1099765 FOR UPDATE;
    
    -- 3. 插入表B
    INSERT INTO B (a_id, col1) VALUES (@new_a_id, p_b_col1);
    
    COMMIT;
    
    SELECT @new_a_id AS inserted_a_id;
END //

DELIMITER ;

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