Commit:

  • Commit command is used to commit or save transaction permanently.
  • It is similar to a save command of other windows based application.
  • When we apply commit command all the transaction and modification done in to the database will be recorded permanently.
Syntax:
Commit;
Example:
Commit;
  • We can set commit command to be executed automatically at some interval.
  • By default auto commit is off.
  • The following command can be used to set auto commit on or off.
Syntax:
Set auto commit on/off;
  • We can use the following command to check the status of the auto commit command.
Show auto commit;
  • We can also set commit command to be executed automatically after performing specified number of transaction.
Example:
Set auto commit 3;
  • It will execute commit command after three commands or transaction has been performed.
  • It has the following syntax:
Set auto commit <no of transaction>

Save Point

  • Save point command creates a logical mark up for number of transaction performed.
  • It does not save or undo operations but it keeps track of logical mark up for the transaction.
  • We can provide save point name with the roll back command to roll back transaction up to specified save point.
Syntax:
Savepoint <savepoint_name>;
Example:
Savepoint sp1;

Rollback Command

  • Rollback command is used to rollback or cancel all transaction up to a specified save point or to up to the last commit operations is performed.
  • It is similar to an undo command of window base application.
Syntax:
rollback [ To <savepoint_name> ] ;
Example:
rollback ;
  • When rollback command is given without specifying Savepoint then it will undo all operations performed up to the last commit command.
  • The following command cancels all the transactions performed up to the Savepoint SP1
rollback To sp1 ;