如何在SQLite3中预保留表的新rowid以供后续使用？

你提到的并发场景下last_insert_rowid()不安全的顾虑其实是对SQLite连接隔离性的误解——同一个数据库连接内的事务中，last_insert_rowid()只会返回当前连接插入的最后一个rowid，完全不受其他连接操作的干扰。这是最直接高效的方案，完全不需要额外维护自定义ID表。

推荐实现：事务内插入后直接获取ID #

在同一个事务中完成capacity插入、ID获取、capacity_args插入的全流程，SQLite的事务隔离机制会保证操作的原子性和安全性：

BEGIN TRANSACTION;
-- 插入capacity主记录
INSERT INTO capacity (name, org, user) VALUES ('测试能力', 1, 1);
-- 获取当前连接生成的capacity ID（不同语言有对应API，比如Python的cursor.lastrowid）
SELECT last_insert_rowid() INTO @capacity_id;
-- 插入关联的参数记录
INSERT INTO capacity_args (capacity, arg) VALUES (@capacity_id, X'010203');
INSERT INTO capacity_args (capacity, arg) VALUES (@capacity_id, X'040506');
COMMIT;

用编程语言调用时，优先使用驱动提供的lastrowid类属性，比手动执行SELECT last_insert_rowid()更可靠，避免SQL注入风险。

特殊场景：提前预分配rowid #

如果确实需要在插入capacity之前拿到ID，可以用以下两种原生方案：

方案1：操作sqlite_sequence预占序列 #

SQLite会为带自增主键的表维护sqlite_sequence表，记录当前的序列值。通过事务内更新并获取序列值来预分配ID：

BEGIN TRANSACTION;
-- 递增capacity表的自增序列
UPDATE sqlite_sequence SET seq = seq + 1 WHERE name = 'capacity';
-- 获取预分配的ID
SELECT seq INTO @pre_id FROM sqlite_sequence WHERE name = 'capacity';
-- 插入capacity时指定这个ID
INSERT INTO capacity (id, name, org, user) VALUES (@pre_id, '测试能力', 1, 1);
-- 插入关联参数
INSERT INTO capacity_args (capacity, arg) VALUES (@pre_id, X'010203');
COMMIT;

注意：sqlite_sequence仅在存在自增主键的表中自动创建，事务会锁定该表，避免其他连接同时修改序列。

方案2：插入临时记录预占ID #

无需操作系统表，通过插入临时记录获取ID后再更新为真实数据：

BEGIN TRANSACTION;
-- 插入临时记录获取ID
INSERT INTO capacity (name, org, user) VALUES ('_temp', 0, 0);
SELECT last_insert_rowid() INTO @pre_id;
-- 更新为真实数据
UPDATE capacity SET name = '测试能力', org = 1, user = 1 WHERE id = @pre_id;
-- 插入关联参数
INSERT INTO capacity_args (capacity, arg) VALUES (@pre_id, X'010203');
COMMIT;

该方案兼容性更好，但多一次UPDATE操作，适合无法直接操作sqlite_sequence的场景。

关键说明：为什么并发场景下不会出问题？ #

SQLite的每个数据库连接都有独立的会话上下文，last_insert_rowid()是会话级别的变量——其他连接的插入操作不会修改当前连接的这个值。只要你在同一个连接的事务内完成所有操作，就不会出现ID被其他进程抢占的情况，完全不需要自定义ID管理机制。

内容的提问来源于stack exchange，提问作者Eric '3ToedSloth'