In a transaction that writes to both an on-disk and in-memory database, when something looks wrong I'd like to write the in-memory database to disk and then rollback the transaction.
Is it possible to have an inner transaction using savepoints that can commit even though the outer transaction is rolled back?
I'm not clear on the SQLite docs at https://sqlite.org/lang_savepoint.html. It appears that it is not possible and all that can be done is to rollback to a savepoint and commit the rest of the transaction. It reads that when the inner transaction fails, it restarts the transaction and runs it again ignoring the inner transaction. But I don't see anything mentioned about the reverse.
I tried the built-in backup method and vacuum into but they won't work because the write transaction has the in-memory database locked. I tried to re-arrange my code to not write to the on-disk database until the validations were performed such that I could write the in-memory data to disk and commit them and then exit the program before anything is written to disk. But that may not always be possible.
I'd just like to know how to capture the in-memory data before a rollback so that I can search for the cause of a problem. Or know that it just 'aint possible because nested transactions don't work that way. In which case, I'll have to write it to the application memory in some form and, after rollback, write that to disk. I'm using the SQLite Tcl Interface, so perhaps the serialization method would be a reasonable approach. (https://sqlite.org/tclsqlite.html#serialize)
Thank you.
The following does not answer the question concerning an inner and outer transaction but it does solve the problem of writing the in-memory database to disk within a transaction before the rollback and capturing the pre-rollback state. I just needed to write it to disk but this shows that the captured database can also be added to a different connection within the application. At least that is how it looks to me but it's all new to me.
This serializes the entire database. I have not figured out how to write just specific tables to disk and not have that rolled back also. Serializing before rollback and deserializing on a new connection after rollback and writing only specific tables in a new transaction should work; but I assume the full serialized database is going to be held in memory.
package require sqlite3
sqlite3 mem ":memory:";
mem eval {
create table docs (id integer);
insert into docs values (1),(2),(3);
begin immediate;
update docs set id=4 where id=2;
}
if {1} {
set fp [open memtest.db wb]
set s [mem serialize main]
chan puts $fp $s
# chan puts $fp [mem serialize main]
close $fp
mem eval {rollback;}
} else {
mem eval {commit;}
}
chan puts stdout [mem eval {select * from docs;}]
# 1 2 3
# Read back in from disk just to illustrate.
sqlite3 dbusr memtest.db -create false -readonly false
chan puts stdout [dbusr eval {select * from docs;}]
# 1 4 3
# Add a new in-memory database and deserialize the
# the serialized data on the main database of the
# new connection.
sqlite3 mem_ds ":memory:";
mem_ds deserialize main $s
chan puts stdout [mem eval {select * from docs;}]
# 1 2 3
chan puts stdout [mem_ds eval {select * from docs;}]
# 1 4 3
Rolling back to a savepoint undoes everything done after that savepoint, including other savepoints. Completely rolling back the transaction will undo everything, including all the savepoints.
The whole point of transactions is that everyone except the writer will either see all operations within it happen together, or none of them. Savepoints allow you to mess with the operations within the transaction, deciding to undo some but not all of the operations as you go, but none of that is allowed to be visible to anyone outside the transaction. If you commit, the final state is saved all at once. If you rollback, it's like nothing happened at all.
I believe the app should be the one to provide enough information to figure out what went wrong if it cannot perform what it's supposed to do. I'd probably output a log or something.