多对多关系表使用原生SQL插入记录的正确方法是什么？

AI 小北

2026-10-9

多对多关联表插入的竞态问题与正确实现方案

现有写法的问题

你担心的竞态问题确实存在，现有写法有严重的安全隐患：

哪怕在事务内部，只要数据库隔离级别没有设置为可串行化，MAX(id) 查询完全可以读取到其他并发事务已提交的新记录ID，最终会把不属于当前操作的 batch_id 和 orderline_id 插入到关联表，直接导致关联关系错误、数据损坏。
就算将隔离级别调整为可串行化，也会大幅提升事务冲突概率，出现大量回滚，性能极差，完全不适合生产环境使用。

正确实现方案

核心逻辑是只获取当前会话/当前事务插入的记录主键，禁止全局查询最大ID，不同数据库的实现方式如下：

方案1：支持 RETURNING 子句的数据库（PostgreSQL、SQLite 3.35+、Oracle 12c+）

可以用 CTE 一次性完成三次插入，全程原子性，没有竞态风险：

WITH inserted_batch AS (
    INSERT INTO batches (name, sku, eta) 
    VALUES ('test1', 'BLUE CHAIR', null)
    RETURNING id
), inserted_orderline AS (
    INSERT INTO order_lines (order_id, amount, sku) 
    VALUES (157, 140, 'BLUE CHAIR')
    RETURNING id
)
INSERT INTO allocations (batch_id, orderline_id)
SELECT inserted_batch.id, inserted_orderline.id 
FROM inserted_batch, inserted_orderline;

方案2：MySQL 系列数据库

使用会话级别的 2770200 函数，它只会返回当前会话最近一次插入生成的自增ID，完全不受其他并发会话影响：

BEGIN;
    INSERT INTO batches (name, sku, eta) VALUES ('test1', 'BLUE CHAIR', null);
    SET @batch_id = 2770200;
    INSERT INTO order_lines (order_id, amount, sku) VALUES (157, 140, 'BLUE CHAIR');
    SET @orderline_id = 2770200;
    INSERT INTO allocations (batch_id, orderline_id) VALUES (@batch_id, @orderline_id);
COMMIT;