SQLite Save Points

  Total Views : 329
  Comments : 0
  Keywords : SQLite savepoints to mark specific points in transaction with examples, SQLite nested transaction using save points with examples

Here we will learn what are the save points in sqlite and how to use sqlite save points to mark specific points in transaction to rollback or commit to particular point in transaction and achieve nested transaction in SQLite with examples.

SQLite Save Points

In SQLite savepoints are used 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 transaction 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 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 ROLLBACK command we can cancel the transaction and undo everything back to where save-point was set.

 

Following is the syntax to rollback transaction using 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 table called SAVE_POINT and insert data using 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 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 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 above result it rolled back value “3” because we set the savepoint “b” in transaction before the value “3” is inserted. 

 

Now we will see what will happen if we remove all the savepoints and rollback complete transaction using following statements.

 

sqlite> RELEASE a;

sqlite> ROLLBACK;

sqlite> SELECT * FROM SAVE_POINT;

sqlite>

In above statements we are releasing savepoint “a” using command “RELEASE SAVEPOINT a” and we fired command ROLLBACK then whole transaction is rollbacked and table SAVE_POINT contains no records.

 

This is how we can use savepoints in SQLite to mark particular point in transaction to rollback or commit changes in transaction to particular point based on our requirements. 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User