Oracle Transactions – 2

Hi everyone,

In this article, we will continue to examine the infrastructure of Transactions in Oracle as we had started previously. Before reading this article, reading Oracle Transactions – 1 article would be beneficial.
In the article Oracle Transactions – 1, we have introduced the Transaction notion and talked about its features and how they are controlled. In this article however, we will examine the Atomicity notion and its level which is one of the most important characteristics of Transactions.

The Atomicity characteristic of the Transactions indicate that Transactions are solid and cannot be broken down. This means that either the transaction runs completely and ends or it does not even run. Atomicity has been implemented in different levels of Oracle’s Transaction infrastructures. To mention these briefly,

– Statement Level Atomicity

– Procedural Level Atomicity

– Transaction Level Atomicity

– DDL Atomicity

Now, let’s examine these atomicity levels one by one.

Statement Level Atomicity
To explain it with examples, firstly let us create 2 tables. Let’s cast an insert to one of them and code a trigger that works on it. This table, right before being modified, let the trigger run first then let it accomplish the process and continue to the transaction. Later, let us examine the results from the entry we cast.

CREATE TABLE atomicity(cola NUMBER CHECK(cola<25));
CREATE TABLE atom(cola NUMBER default 0);
INSERT INTO atom values(0);
CREATE TRIGGER atom_trigger
ON atomicity
SET cola = cola + 1;
SET cola = cola – 1;
INSERT INTO atomicity
VALUES (27);

The trigger we code on it is triggered immediately right before an insert, update or delete process is done to the “atomicity” table and it updates the “atom” table according to the type of the process. However, when we run the example above in order and examine the final form of the table “atom”, we observe that cola column in “atom” table does not increase. The reason for that is that, as a result of a constraint violation, entries cannot be cast to the “atomicity” table. But the trigger run right before the insert and did the update process. From this point of view we understand that as the main insert process had not been ended successfully, the process made in the trigger was made ROLLBACK by Oracle. With this behavior, it was assumed that the transaction has never been run and the data went back to its previous form.

Procedural Level Atomicity
To explain it with examples,
Let’s create a table first and then add a check constraint to this table.

CREATE TABLE atomicity(cola NUMBER CHECK(cola<25));

—Later, let’s make 2 insertions into a Begin End block. Let one of these inserts violate the constraint while letting the other one run without violation and let’s examine the results.

INSERT INTO atomicity
VALUES (22);

INSERT INTO atomicity
VALUES (27);


When we run the block above, the first insert phrase run without an error while the second one has got an error which ended the Transaction with an error. When we cast a select as the last process to the table and examine the entries inside, we see that even though the first insert phrase run without an error there is not any entries inside. That characteristic of the transaction comes from the atomicity in statement level. Even if the processes work successfully, any error in any of the processes will cause the whole transaction to be made ROLLBACK. For this reason we should take this into consideration in the functions, procedures or triggers we are going to code into db, and in order not to face unexpected situations we should apply Exception Handling accordingly.

Transaction Level Atomicity
Transaction Level Atomicity actually a notion that encapsulates both of the 2 levels we discussed previously. With this structure Transactions pass the database from one consistent state to another and provides data unity. And while doing that process, it either renders all the changes valid and permanent with commit, or it rolls the db back to a previous consistent state with rollback.

DDL and Atomicity
DDL automatically run as if the commit command is given before and after it runs. This detail should not be forgotten in PL/SQL code blocks we coded. Otherwise, DDLs we run may break the transaction logic as they are auto commit, and it may cause us to spoil the consistent structure that atomicity of the db offers us.


“Expert Oracle Database Architecture 2011″, Tom KYTE


About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , , . Bookmark the permalink.

1 Response to Oracle Transactions – 2

  1. Pingback: Oracle Transactions – 3 | Emrah METE

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s