能否在单个SQLite查询中同时执行SELECT与UPDATE操作？

要在SQLite中实现原子地读取数据旧状态并更新为新状态的需求，可以通过以下两种可靠方式实现，解决你遇到的问题：

方式一：使用WITH子句+UPDATE RETURNING（单语句原子操作） #

SQLite 3.35.0及以上支持RETURNING子句，结合WITH子句可以先捕获更新前的旧值，再执行更新并返回旧值，全程是单个原子语句：

WITH old_state AS (
  SELECT is_locked AS was_locked
  FROM migrations_lock
  WHERE index = 1
)
UPDATE migrations_lock
SET is_locked = 0
WHERE index = 1
RETURNING (SELECT was_locked FROM old_state);

为什么这个写法有效？ #

WITH子句会在UPDATE执行前先查询并保存旧值，避免了Postgres写法中SQLite子查询读取更新后数据的问题，确保返回的是更新前的原始状态，且整个操作是原子的。

方式二：使用事务（多语句原子操作） #

SQLite支持标准事务，只要确保在同一个事务中执行SELECT和UPDATE即可实现原子性，注意不要嵌套事务（SQLite默认不支持嵌套事务）：

BEGIN;
-- 读取旧状态
SELECT is_locked AS was_locked FROM migrations_lock WHERE index = 1;
-- 执行更新
UPDATE migrations_lock SET is_locked = 0 WHERE index = 1;
COMMIT;

解决你遇到的嵌套事务错误 #

如果触发嵌套事务错误，说明你的连接在执行BEGIN前已经处于事务中。SQLite不支持嵌套事务，若需要类似嵌套的逻辑，可以使用SAVEPOINT替代，但此场景下只需确保单次事务即可，无需嵌套。

你之前遇到的问题原因说明 #

1. Postgres写法在SQLite返回新数据：SQLite中同一语句内的子查询会看到当前语句的修改结果，而Postgres的RETURNING子查询基于更新前的快照，因此直接复用Postgres写法会返回更新后的值。
2. 无事务的SELECT+UPDATE被忽略：无事务时，SELECT和UPDATE是两个独立操作，若连接开启自动提交，可能因并发或执行逻辑问题导致更新未生效，且不具备原子性。

内容的提问来源于stack exchange，提问作者Thomas Hunter II