What is the difference between rollback and savepoint in sql with examples?

Answer 1

Rollback:

This is used for undoing the work done in the current transaction. This command also releases the locks if any hold by the current transaction. The command used in SQL for this is simply:

ROLLBACK;

Savepoint:

This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a savepoint defined in it. The command used in SQL for this is simply:

SAVEPOINT savepointname;

For example:

UPDATE…..

DELETE….

SAVEPOINT e1;

INSERT….

UPDATE….

SAVEPOINT e2;

……

It is also possible to define savepoint and rollback together so that programmer can achieve rollback of part o a transaction. Say for instance in the above

ROLLBACK TO SAVEPOINT e2;

This results in the rollback of all statements after savepoint e2

Commit:

This is used to end the transaction and make the changes permanent. When commit is performed all save points are erased and transaction locks are released. In other words commit ends a transaction and marks the beginning of a new transaction. The command used in SQL for this is simply:

COMMIT;