Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Database Transactions / (Oracle Database ) / COMMIT / ROLLBACK

DZone's Guide to

Database Transactions / (Oracle Database ) / COMMIT / ROLLBACK

Free Resource

Just released, a free O’Reilly book on Reactive Microsystems: The Evolution of Microservices at Scale. Brought to you in partnership with Lightbend.

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

 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.

 

Strategies and techniques for building scalable and resilient microservices to refactor a monolithic application step-by-step, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

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

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}