Oracle Transactions

Introduction

In today’s technologically dependent world, every now and then we find ourselves working with a relational database. This means working with transactions, and specifying transactions boundaries: where transactions should start and commit. Oracle implements the concept of a transaction, like most relational databases do. A transaction is a set of related statements that either all execute or do not execute at all. Transactions play an important role in maintaining the data integrity.

What is a transaction?

Transaction is a fundamental concept of all databases. It allows users to make changes to the data and then decide whether to save or to discard them. These database transactions bundle multiple steps into one logical unit of work.

Transactions have the subsequent four standard properties, usually mentioned by the acronym ACID.

• Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the purpose of failure and every one the previous operations are rolled back to their former state.

• Consistency − ensures that the database properly changes states upon a successfully committed transaction.

• Isolation − enables transactions to operate independently of and transparent to each other.

• Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.

What is done inside the transaction is, operations are performed atomically, stored durably, in isolation and leaving the system during a consistent state, that is durability, features a very real impact on the business functionality of the system we develop.

A transaction contains the following statements:

• DML statements which constitute one consistent change to the data. These statements include INSERT, UPDATE, DELETE and MERGE commands

• Either one DDL statement which may include CREATE, ALTER, DROP, RENAME or TRUNCATE.

• One DCL statement either GRANT or REVOKE.

Statement execution

COMMIT Statement

It represents the point of time when the user has made all the desired changes and wants to logically group them together. With no errors in the data the user is ready to save it.

COMMIT makes permanent any database changes made during the present transaction. Queries that are issued after the transaction commits will see the committed changes.

ROLLBACK Statement

It enables user to discard the changes made in the database. If at any time during execution a SQL statement causes a mistake, all effects of the statement are rolled back. The effect of the rollback is as if that statement had never been run. This operation is a statement-level rollback.

Errors discovered during SQL statement execution cause statement-level rollbacks. An example of such an error is attempting to insert a duplicate value in a primary key.

ROLLBACK ends the present transaction and undoes any changes made since the transaction began.

SAVEPOINT Statement

SAVEPOINT marks the present point within the processing of a transaction. You can declare intermediate markers called savepoints within the context of a transaction. Savepoints divide a long transaction into smaller parts.

ROLLBACK TO SAVEPOINT Statement

Allows the user to rollback to a specified savepoint in the transaction. Using savepoints, you’ll arbitrarily mark your work on any point within an extended transaction. You then have the choice later of rolling back work performed before the present point within the transaction but after a declared savepoint within the transaction. For example, you’ll use savepoints throughout an extended complex series of updates, so if you create a mistake, you are doing not got to resubmit every statement.

Used with ROLLBACK, SAVEPOINT will undo a part of the transaction. The statement discards the changes made after the given savepoint was established.

SET TRANSACTION Statement

SET TRANSACTION sets transaction properties like read/write access and isolation level.

HOW DOES TRANSACTION FLOW?

• A transaction begins with the first DML statement.

• It ends in the following cases:

 1)A COMMIT or ROLLBACK statement is issued.

2) A DDL statement is issued.

3) A DCL statement is issued.

4)A user exits from Oracle Database causing the current transaction to be committed.

• After one transaction ends the next transaction takes place with the next SQL statement.

• Every change made into data is temporary until it is committed.

Example:

Consider a transaction by name student:

BEGIN TRANSACTION Student ;

SET TRANSACTION [ READ WRITE | READ ONLY ];

COMMIT;

SAVEPOINT SP1;

DELETE FROM Student WHERE AGE = 20;

SAVEPOINT SP2;

Output:

ROLLBACK TO SP1;

Output:

Conclusion

Transaction processing systems offer a unique response and can be structured to user or organization requirements, although it is very vital to make a decision to choose the most appropriate method which relies heavily on the quantity of data and the type of businesses or tasks it is being used for.

Transactions occur on a day-to-day basis be it by hand or via technology. Oracle performs transactions in a simple yet efficient manner and it is important from a business perspective to know the flow of transaction processing. Oracle makes it easy for any user to implement and understand these transactions and hence is so popular.