What are COMMIT and ROLLBACK in SQL?

Answer 1

COMMIT is a command on executing which all changes in the DB are made permanent and cannot be undone.

A COMMIT is automatically executed when you exit normally from SQL*Plus.
A COMMIT does not apply to any SELECT commands as there is nothing to commit.
A COMMIT does not apply to any DDL commands (eg CREATE TABLE, CREATE INDEX, etc). These are automatically committed and cannot be rolled back.

Where as there are few command whose effect can be rolledback if not producing the desired result. During the process of inserts, updates and deletes, the SQL needs to preserve the integrity of the database and allow multi-user access. A transaction that has not yet been committed needs to be transparent to users. For example, an uncommitted insert should not be accessible to another user.

Answer 2

The COMMIT Command:

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

The syntax for COMMIT command is as follows:

COMMIT;

The ROLLBACK Command:

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

The syntax for ROLLBACK command is as follows:

ROLLBACK;