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.
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.
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 asACID properties. ACID is an acronym for the following:
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.
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.
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.
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.