Database Transactions / (Oracle Database ) / COMMIT / ROLLBACK

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT orROLLBACK statement or implicitly when a DDL statement is issued.

Open sql developer and enter the following 

CREATE TABLE emp22

(

  emp_id NUMBER(2) CONSTRAINT emp_col1_pk22 PRIMARY KEY,

  emp_name  VARCHAR2(20),

  emp_salary NUMBER(5)

);

Next input data to the table

INSERT INTO "HR"."EMP22" VALUES('1','Achala','1500');

INSERT INTO "HR"."EMP22" VALUES('2','ach','1000');

INSERT INTO "HR"."EMP22" VALUES('3','Tendulkar','20000');

Open another sql developer and enter the following

select * from emp22;

The result will be as follows

It shows that the transaction is not committed. Now lets commit the transaction ( from sql developer first window). Enter the following.

COMMIT;

Now go to the sql developer window 2 and use the following query

Open sql developer 1 and enter following

INSERT INTO "HR"."EMP22" VALUES('4','Jayasuriya','2000');

Lets do a ROLLBACK now.

If you use the following query before and after the ROLLBACK you will notice the difference

select * from emp22;

All Oracle transactions obey the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following:

• Atomicity
  All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.
• Consistency
  The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data.
• Isolation
  The effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the hr.employeestable does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially.
• Durability
  Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

Reference : http://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm