Here we will learn what are the save points in sqlite and how to use sqlite save points to mark specific points in the transaction to rollback or commit to a particular point in the transaction and achieve nested transaction in SQLite with examples.
In SQLite, savepoints are useful to mark specific points in the transaction. By using these savepoints we can rollback or accept the changes to particular save-points in transaction based on our requirements.
We can create more than one save-point in transaction based on our requirements and sometimes we will call savepoints are the nested transactions because we will create savepoints within the transaction to rollback or commit changes to particular savepoint within the transaction.
Generally, these savepoints are useful with large or multi-step transactions because we can rollback transactions to particular step or savepoint based on our requirements.
We can create a save-points in SQLite with SAVEPOINT command.
Following is the SQLite SAVEPOINT syntax to create a new savepoint with new name.
SAVEPOINT savepoint_name
By using SQLite RELEASE command we can release a save-point and accept all the changes made since the savepoint was set.
Following is the syntax to release savepoint.
RELEASE [SAVEPOINT] savepoint_name
By using the ROLLBACK command we can cancel the transaction and undo everything back to where save-point was set.
Following is the syntax to rollback transactions using the ROLLBACK command.
ROLLBACK [TRANSACTION] TO [SAVEPOINT] savepoint_name
Now we will see how to use SQLite savepoints to create specific points in transaction with examples.
To see how to use savepoints in transactions first create a table called SAVE_POINT and insert data using the following queries in your database.
CREATE TABLE SAVE_POINT(i INTEGER);
BEGIN;
INSERT INTO SAVE_POINT VALUES(1);
SAVEPOINT a;
INSERT INTO SAVE_POINT VALUES(2);
SAVEPOINT b;
INSERT INTO SAVE_POINT VALUES(3);
If you observe above statements we created different savepoints (a, b) in transaction while inserting records in the SAVE_POINT table.
Now we will try to rollback SAVE_POINT table records to savepoint b using ROLLBACK command and see the records of table using the following queries.
sqlite> ROLLBACK TO b;
sqlite> SELECT * FROM SAVE_POINT;
i
----------
1
2
When we execute “ROLLBACK” command it will rollback table records to the point where we set savepoint. If you observe the above result it rolled back value “3” because we set the savepoint “b” in the transaction before the value “3” is inserted.
Now we will see what will happen if we remove all the savepoints and rollback complete transactions using the following statements.
sqlite> RELEASE a;
sqlite> ROLLBACK;
sqlite> SELECT * FROM SAVE_POINT;
sqlite>
In the above statements we are releasing savepoint “a” using command “RELEASE SAVEPOINT a” and we fired command ROLLBACK then the whole transaction is rollbacked and table SAVE_POINT contains no records.
This is how we can use savepoints in SQLite to mark a particular point in a transaction to rollback or commit changes in transaction to particular point based on our requirements.