Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g

A transaction is a logical unit of work. In oracle a transaction relates to a series of Data Manipulation Language statements that does a logical unit of work. That is, changes made to a database, by one or more Data Manipulation Language commands like insert, update or delete can be referred to as a transaction.

It is possible either to make the database transaction permanent by committing or to reverse it by rolling back. This process is executed by the two commands “commit” and “rollback”. Commit makes the changes visible to other database sessions too. Rollback undoes the work done in the transaction. The other commands used to control transactions are “savepoint” and “set transaction” commands.

We can use savepoints to rollback portions of transactions that has occurred. A savepoint is a named processing point in a transaction. Rolling back to a savepoint will cause only all the changes that were done after the savepoint to be undone.

Example
insert into EMP values(‘12457’,’22-Apr-08’,200);
insert into EMP values(‘12333’,’22-Apr-08’,240);
savepoint A;
insert into EMP values(‘22457’,’22-Apr-08’,500);
rollback to savepoint A;
commit;

The execution of above code will cause only the first two insert to be reflected. A commit erases all savepoints in a transaction and also releases the transaction locks. If there are four savepoints A, B, C and D in order and rollback is done to savepoint C only D is erased.
The “set transaction” command is used to set the properties of transaction such as read-write access.

Usually commit and rollback operations are done based on commands that are initiated from the application programs explicitly. However there are certain instances when they are initiated by the system implicitly. These instances when a commit is forced to occur without instructions are, when commands such as quit, exit or any Data Definition Language command is executed.

In some cases when a series of Data manipulation Language commands are executed and not been either explicitly or implicitly committed but some serious error or failure occurs then oracle will rollback work automatically. This is known as the auto rollback feature of oracle.

The transaction control statements generally ensure that the database is consistent to its concurrent users. They can be used to ensure data integrity for example, either to make both debit and credit entries of a financial transaction complete or to make both fail if one entry encounters errors. The rollback allows restoration of original data or lets corrective action be done in the case of exceptions.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |