What is the difference between Rollback, Commit and Savepoint is SQL?
All these statements fall in the category of Transaction Control Statements.
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:
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:
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
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: