5

I am trying to understand Savepoints and Transactions in SQLite. I had the following commands on a Table/Database and I am using Savepoints.

SAVEPOINT aaa;
RELEASE aaa;
BEGIN;

Now, if I execute all the above statements at once, its throwing an error saying that A transaction cannot be started inside another transaction. If I run them one at a time, it works fine. if I run the first two Savepoint and release commands and try to start another transaction by executing the Begin. it again throws the same error as previous.

The link here says that

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. `

So, Is it absolutely necessary for a Commit or Rollback command after the Release Savepoint Command? Doesn't release command commit and allow us to start a new transaction using BEGIN?

Programmerzzz
  • 1,237
  • 21
  • 48
  • Are you using Python? – CL. Jul 28 '16 at 07:27
  • Nope, we are using C# – Programmerzzz Jul 28 '16 at 16:32
  • How? Show the code. – CL. Jul 28 '16 at 16:37
  • we meant to use C# but I am trying the above commands in Plain SQLite Editor just to understand Savepoints to decide between Savepoints and Transactions or a mix of both. – Programmerzzz Jul 28 '16 at 16:40
  • There is no program called "Plain SQLite Editor". Are you using the `sqlite3` command-line shell? If yes, show the exact sequence of commands you're using. – CL. Jul 28 '16 at 17:58
  • apologies for wrong wording, I meant the SQL Editor which is available as a Tab on "SQLite Expert Professional" software where in we type in the queries on the table. Coming to the Sequence of the statements that I am using is same as in my question. SavePoint aaa; some DML statements, Release aaa and Begin; Please let me know if you need more info. I tried on a test database on my local machine. – Programmerzzz Jul 28 '16 at 19:39
  • 2
    This sounds like a bug in that software. Try to reproduce it in `sqlite3`. – CL. Jul 28 '16 at 20:59

1 Answers1

0

SAVEPOINT aaa; RELEASE aaa; BEGIN;

is interpreted by sqlite as

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

The following would be fine:

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

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

David Andreoletti
  • 4,485
  • 4
  • 29
  • 51
  • 5
    According to the documentation link you have posted releasing the outermost savepoint should commit: “If a RELEASE command releases the outermost savepoint, so that the transaction stack becomes empty, then RELEASE is the same as COMMIT.”. It also dependes on current autocommit value - according to https://www.safaribooksonline.com/library/view/using-sqlite/9781449394592/ch04s07.html “...However, unlike with most commands, the autocommit transaction will not automatically commit after the SAVEPOINT command returns, leaving the system inside an open transaction...” – awattar Dec 21 '17 at 20:58
  • 4
    The linked document says "When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED TRANSACTION." It says **same as** not "also". Your interpretation is incorrect. An outermost SAVEPOINT started without a BEGIN TRANSACTION will be committed on the matching RELEASE, just as awattar commented. The behavior describe in the question is likely due to buggy/automatic transaction handling of the 3rd-party tool. I have observed many similar issue with multiple sqlite tools. – C Perkins Aug 15 '19 at 02:18
  • 3
    Listen to what @awattar and CPerkins are saying. This "is interpreted by sqlite as" part of your answer is wrong. It adds uncertainty to an already convoluted topic. Please reformulate your answer to avoid this statement. The "would be fine" part is fine :) – Antony Hatchkins Jun 19 '20 at 13:00