Over a million developers have joined DZone.

Database Transactions / (Oracle Database ) / COMMIT / ROLLBACK

Learn more about the advantages of moving from a monolithic to microservices architecture.  Brought to you in partnership with IBM.

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 or ROLLBACK statement or implicitly when a DDL statement is issued.
Open sql developer and enter the following

  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.
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:
  • 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.


From Idea to Application gives you the architecture to quickly build, manage and run a range of applications (web, mobile, big data, new smart devices, etc.) on an open-standard, cloud-based platform. See why developers are using IBM Bluemix. Brought to you in partnership with IBM.


Published at DZone with permission of Achala Chathuranga Aponso, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}