ORACLE TRANSACTIONS

A series of one or more SQL statements that are logically related or a series of operations performed on Oracle table data is termed as a Transaction. Oracle treats this logical unit as a single entity. Oracle treats changes to table data as a two-step process. First, the changes requested are done. To make these changes permanent a COMMIT statement has to be given at the SQL prompt. A ROLLBACK statement given at the SQL prompt can be used to undo a part of or the entire transaction.

A transaction begins with the first executable SQL statement after a commit, rollback or connection made to the Oracle engine. All changes made to an Oracle table data via a transaction are made or undone at one instance.

Specifically, a transaction is a group of events that occur between any of the following events:

Connecting to Oracle

Disconnecting from Oracle

Committing changes to the database table

Rollback

Closing Transactions

A transaction can be closed by using either a commit or a rollback statement. By using these statements, table data can be changed or all the changes made to the table data undone.

Using COMMIT

A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transactional locks acquired on tables are released.

Syntax:

COMMIT;

Using ROLLBACK

A ROLLBACK does exactly the opposite of COMMIT. It ends the transaction but undoes any changes made during the transaction. All transactional locks acquired on tables are released.

Syntax:

ROLLBACK [WORK] [TO [SAVEPOINT] ];

where,

WORK           Is optional and is provided for ANSI compatibility

SAVEPOINT   Is optional and is used to rollback a transaction partially, as far as the specified savepoint

SAVEPOINTNAME           Is a savepoint created during the current transaction

Creating A SAVEPOINT

SAVEPOINT marks and saves the current point in the processing of a transaction. When a SAVEPOINT is used with a ROLLBACK statement, parts of a transaction can be undone. An active SAVEPOINT is one that is specified since the last COMMIT or ROLLBACK.

Syntax:

SAVEPOINT ;

ROLLBACK can be fired from the SQL prompt with or without the SAVEPOINT clause. The implication of each is described below.

A ROLLBACK operation performed without the SAVEPOINT clause amounts to the following:

Ends the transaction

Undoes all the changes in the current transaction

Erases all savepoints in that transaction

Releases the transactional locks

A ROLLBACK operation performed with the TO SAVEPOINT clause amounts to the following:

A predetermined portion of the transaction is rolled back

Retains the save point rolled back to, but loses those created after the named savepoint

Releases all transactional locks that were acquired since the savepoint was taken