sqlite - SQLite 中的 SAVEPOINT 机制

Question

我正在尝试了解 SQLite 中的保存点和事务。
我在表/数据库上有以下命令，我正在使用保存点。

SAVEPOINT aaa;
RELEASE aaa;
BEGIN;

现在，如果我一次执行上述所有语句，它会抛出一个错误说 A transaction cannot be started inside another transaction .如果我一次运行一个，它工作正常。
如果我运行前两个 Savepoint 和 release 命令并尝试通过执行 Begin 开始另一个事务.它再次抛出与以前相同的错误。

链接 here说

If the SAVEPOINT command is issued when SQLite is in autocommit mode—that is, outside of a transaction—then a standard autocommit BEGIN DEFERRED TRANSACTION will be started. However, unlike with most commands, the autocommit transaction will not automatically commit after the SAVEPOINT command returns, leaving the system inside an open transaction. The automatic transaction will remain active until the original save-point is released, or the outer transaction is either explicitly committed or rolled back. `

那么，Release Savepoint 命令之后是否一定需要 Commit 或 Rollback 命令呢？没有 release命令提交并允许我们使用 BEGIN 开始一个新事务?

Answer 1


SAVEPOINT aaa;
RELEASE aaa;
BEGIN;

被sqlite解释为

BEGIN DEFERRED TRANSACTION; SAVEPOINT aaa; // Create a transaction, and mark current db state as savepoint "aaa" [1]
RELEASE aaa; // Remove all db changes made since savepoint "aaa", but keep on executing the transaction
BEGIN; // Create another transaction, while there is already a transaction. This will FAIL because there cannot be 2 transactions executed simultaneously

以下会很好:

BEGIN;
SAVEPOINT "aaa";
RELEASE "aaa";
COMMIT;
BEGIN;

[1] https://sqlite.org/lang_savepoint.html