SQLite Forum

Changes are missing after savepoint release
Login

Changes are missing after savepoint release

(1.1) By Sergei Fundaev (fundaev) on 2021-02-08 13:28:32 edited from 1.0 [link] [source]

Hi there!

I'm faced by the following problem. The changes, made between "savepoint <name>" and "release <name>" queries, are not saved in the database if there was another savepoint before <name> one, which was rollbacked before <name> is started.

For example, let's we have the following table in a database:

create table t1 ( id integer primary key, name text, age integer );

These SQL-queries do not change t1 table content in fact:

savepoint sp1; insert into t1 (name, age) values('item1', 1); rollback to sp1; savepoint sp2; insert into t1 (name, age) values('item2', 2); release sp2;

If sp1 savepoint is "released" (i.e. if release sp1 query is ran instead of rollback to sp1 one), the t1 table will have both new rows.

The problem does not occur if all queries are "wrapped" into another savepoint:

savepoint sp0; savepoint sp1; insert into t1 (name, age) values('item1', 1); rollback to sp1; savepoint sp2; insert into t1 (name, age) values('item2', 2); release sp2; release sp0;

As far as I understand, it is a bug in sqlite, isn't it?

(2) By Mark Lawrence (mark) on 2021-02-08 11:48:17 in reply to 1.0 [link] [source]

I would check with a later version of SQLite. For me with a relatively recent version it works:

-- Loading resources from /home/mark/.sqliterc
SQLite version 3.34.0 2020-09-01 00:26:21
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1 (
   ...>   id integer primary key,
   ...>   name text,
   ...>   age integer
   ...> );
sqlite> savepoint sp1;
sqlite> insert into t1 (name, age) values('item1', 1);
sqlite> rollback to sp1;
sqlite> savepoint sp2;
sqlite> insert into t1 (name, age) values('item2', 2);
sqlite> release sp2;
sqlite> select * from t1;
id  name   age
--  -----  ---
1   item2  2
sqlite>

(3) By Sergei Fundaev (fundaev) on 2021-02-08 11:56:23 in reply to 2 [link] [source]

Yes, I can't reproduce it using sqlite3 util too. Here is the code, demonstrating the problem. It creates test.db database with empty "t1" table.

SQLite version 3.34.1 GCC v7.5.0

(4.1) By Dan Kennedy (dan) on 2021-02-08 12:06:08 edited from 4.0 in reply to 3 [source]

ROLLBACK TO does not close the named savepoint. So your program exits without committing the top level transaction. If you add:

    exec("RELEASE sp1");

before the sqlite3_close() it will work as expected.

https://sqlite.org/lang_savepoint.html

(5) By Sergei Fundaev (fundaev) on 2021-02-08 12:28:42 in reply to 4.1 [link] [source]

Thank you!