sqlitesavepoints

Why arent my modifications being written to the disk using SAVEPOINT?

I have three operations, a read, a write, and another read, each operation is in a transaction started using SAVEPOINT. I read an value, aborted the transaction I used to read it, I set the value, commited the transaction I used to set it, and then I read it again, in a transaction that I later abort. When I open my database again, the set is undone.

I isolated my problem in the following shell script:

$ sqlite3 oi.sqlite3 <<<"CREATE TABLE test (value TEXT);"
$ sqlite3 oi.sqlite3 <<<"INSERT INTO test VALUES (1);"
$ sqlite3 oi.sqlite3 <<<"
 SAVEPOINT '1';
 RELEASE '1';
 SAVEPOINT '2';
 SELECT * FROM test;
 ROLLBACK TO '2';
 SAVEPOINT '3';
 UPDATE test SET value=0;
 RELEASE '3';
 SAVEPOINT '4';
 SELECT * FROM test;
 ROLLBACK TO '4';"
$ sqlite3 oi.sqlite3 <<<"SELECT * FROM test;"

My output is

1 # Read before set
0 # Read after set
1 # Read after reopening the database

What am I missing?

Solution

  • From the documentation:

    Note that unlike that plain ROLLBACK command (without the TO keyword) the ROLLBACK TO command does not cancel the transaction.

    So, I need to run RELEASE after each ROLLBACK TO.