SQL transaction is a set of actions performed on a table or tables. These transactions usually modify the data in the table. In order to indicate and complete the transaction, one has to commit or rollback the transaction. That means when we commit the transaction, it will be permanently updated to the database, and when rolled back, it will not change any records in the table. We cannot leave the transaction in the middle without committing or rolling back it. One can issue commit or rollback as follows :
COMMIT;
ROLLBACK;
When these statements are executed alone, it will not have any affect. But when these statements are issued after any insert/ update/ delete, it either completes the transaction or reverts the transactions.
INSERT INTO PUBLISHER VALUES (‘TTMH_2324’, ‘Tata McGraw Hill’, ‘Delhi’);
COMMIT; — completes above transaction
INSERT INTO BOOK_CATEGORY VALUES (‘9789350871249’, ‘PHYS_1234’);
ROLLBACK; — reverts above transaction
We can set the different points in the transaction and revert or complete the transaction till that point. In other words, we can set some labels when some transaction is complete and go on executing the transaction. Later we can commit or rollback to any one of the labels. This is done by using SAVEPOINTS in SQL.
Consider below set of query :
SAVEPOINT SP_1;
INSERT INTO PUBLISHER VALUES ('TTMH_2324', 'Tata McGraw Hill’, ‘Delhi');
SAVEPOINT SP_2;
INSERT INTO BOOK_CATEGORY VALUES ('9789350871249', 'PHYS_1234');
SAVEPOINT SP_3;
DELETE FROM CATEGORY WHERE CATEGORY_ID = 'DBMS_1000';
SAVEPOINT SP_4;Now if we issue
ROLLBACK TO SP_3;
Then last delete transaction will be reverted and data will not be deleted from the table. Suppose we issue
ROLLBACK TO SP_1;
Then all the transaction above will be reverted. Hence savepoint acts as a label to which transaction has to be reverted.