When you "rollback to something;" any work since that savepoint was created is
un-done, but that named savepoint still exists, yes.
That caught me out once where my code was like
for each line in the file:
savepoint foo
do several inserts/updates
if they all went ok:
release foo
if not:
rollback to foo
stuff involved with error
I wound up slowly accumulating a ton of savepoints with the same name as each
time there was an error it left another savepoint on the stack. It should have
been more like
for each line in the file:
savepoint foo
do several inserts/updates
if something went wrong:
rollback to foo
stuff involved with error
release foo whether it went right or wrong
On a humor tangent, am I the only person who has ever named a savepoint
"theHounds" for the sole purpose of being able to run the statement:
release theHounds;
? :)
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Andy Bennett
Sent: Wednesday, February 13, 2019 9:58 AM
To: SQLite mailing list
Subject: Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT
correctly?
Hi,
> For example, would I do this:
> Connect to the DB
> [ Pass the command to save: ] SAVEPOINT 'current'
> [ User choices are processed: SELECT and UPDATE statements ]
> [ if error or user changes their mind ] ROLLBACK TRANSACTION TO
> SAVEPOINT 'current'
>
> [ otherwise... upon success ] RELEASE SAVEPOINT 'current'
That looks pretty good. I think you also need to RELEASE the SAVEPOINT
after you've ROLLed BACK to it (if you no longer want it) otherwise it
stays on the stack so that you can ROLLBACK to it again.
I have also recently implemented something using SAVEPOINT TRANSACTIONS
and I'm unclear (having read the docs) what state one can expect
statements to be in after a ROLLBACK. One of the mental models says that
it's as if the transaction was restarted from the beginning, up until
the SAVEPOINT. However, I assume that SELECT statements stay put,
returning the next row after the one that was last fetched rather the
one that was fetched immediately after the first time that the SAVEPOINT
was passed.
Does anyone know what the guarantees are?
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
