COMMIT, ROLLBACK and SAVEPOINT

TRANSACTION

A transaction is a set of SQL statements which Oracle treats as a Single Unit i.e. all the statements should execute successfully or none of the statements should execute.

Transaction Control Language (TCL)

Transaction Control Language (TCL) statements manage changes made by Data Manipulation Language (DML) statements.It allows statements to be grouped together into logical transactions

To control transactions Oracle does not made permanent any DML statements unless you commit it. If you don’t commit the transaction and power goes off or system crashes then the transaction is roll backed.

TCL Statements available in Oracle are

COMMIT : Make changes done in transaction permanent.

ROLLBACK: Rollbacks the state of database to the last commit point.

SAVEPOINT: Use to specify a point in transaction to which later you can rollback.

COMMIT

To make the changes done in a transaction permanent issues the COMMIT statement.

The syntax of COMMIT Statement is
COMMIT [WORK] [COMMENT ‘Your Comments’];

WORK: is optional.
COMMENT: is also optional; specify this if you want to identify this transaction in data dictionary DBA_2PC_PENDING.

Example:

INSERT INTO EMP (EMPNO, ENAME, SAL) VALUES (101,’ABID’, 2300);
COMMIT;

ROLLBACK

To rollback the changes done in a transaction give rollback statement. Rollback restore the state of the database to the last commit point.

Example:

DELETE FROM EMP;
ROLLBACK; --undo the changes

SAVEPOINT

Specify a point in a transaction to which later you can roll back.

Example:

INSERT INTO EMP (EMPNO,ENAME,SAL) VALUES (109,’SAMI’,3000);
SAVEPOINT A;
INSERT INTO DEPT VALUES (10,’SALES’,’HYD’);
SAVEPOINT B;
INSERT INTO SALGRADE VALUES (‘III’,9000,12000);

Now if you give

ROLLBACK TO A;

Then row from SALGRADE table and DEPT will be roll backed. Now you can COMMIT the row inserted into EMP table or ROLLBACK the transaction.

If you give

ROLLBACK TO B;

Then row inserted into SALGRADE table will be roll backed. Now you can COMMIT the row inserted into DEPT table and EMP table or ROLLBACK to SAVEPOINT A or completely roll backed the transaction.

If you give

ROLLBACK;

Then the whole transactions are roll backed.

If you give

COMMIT;

Then the whole transaction is committed and all SAVEPOINT are removed.

Reference: http://www.oracle-dba-online.com/sql/commit_rollback_savepoint.htm