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
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.
A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transactional locks acquired on tables are released.
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.
ROLLBACK [WORK] [TO [SAVEPOINT]
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.
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