SQLite Save Points

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.

SQLite Save Points

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.

Syntax of SQLite Save Points

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.

SQLite Save Points 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.