Transaction Support in SQL

Transaction Support in SQL

The COMMIT, ROLLBACK, and SAVEPOINT are collectively considered as Transaction Commands

(1) COMMIT: The COMMIT command is used to save permanently any transaction to database.

When we perform, Read or Write operations to the database then those changes can be undone by rollback operations. To make these changes permanent, we should make use of commit

(2) ROLLBACK: The ROLLBACK command is used to undo transactions that have not already saved to database. For example

Consider the database table as

Following command will delete the record from the database, but if we immediately performs ROLLBACK, then this deletion is undone.

For instance -

DELETE FROM Student

WHERE RollNo = 2;

ROLLBACK;

Then the resultant table will be

(3) SAVEPOINT: A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction. The SAVEPOINT can be created as

SAVEPOINT savepoint_name;

Then we can ROLLBACK to SAVEPOIT as

ROLLBACK TO savepoint_name;

For example - Consider Student table as follows –

Consider Following commands

SQL> SAVEPOINT S1 SQL>DELETE FROM Student

Where RollNo=2;

SQL> SAVEPOINT S2

SQL>DELETE FROM Student

Where RollNo=3;

SQL> SAVEPOINT S3

SQL>DELETE FROM Student

Where RollNo=4

SQL> SAVEPOINT S4

SQL>DELETE FROM Student

Where RollNo=5

SQL> ROLLBACK TO S3;

Then the resultant table will be

Thus the effect of deleting the record having RollNo 2, and RollNo3 is undone.