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 BEFORE INSERT OR UPDATE ON atomicity FOR EACH ROW BEGIN IF INSERTING THEN UPDATE atom SET cola = cola + 1; ELSE UPDATE atom SET cola = cola – 1; END IF; END; 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.
BEGIN INSERT INTO atomicity VALUES (22); INSERT INTO atomicity VALUES (27); END;
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.