A transaction in Oracle

All changes of data in an Oracle database can only be done within a transaction. A transaction must either be committed or rolled back.

Data changed within a transaction is not visible to another session until it is commited.

A transaction is ended either by a commit or a rollback.

If the transaction is commited, the database ensures that the modified data can always be found, even if the database or the hard disk crashes. Of course, for the latter to be true, a decent backup and recovery concept must be in place.

See also commit [sql].

If a transaction is rolled back, the database system ensures that no trace of the modified data will be found, that is, the database behaves as though the transaction had never been started.

Oracle uses undo to execute a rollback.

Rollback segments serve two purposes: a) It makes it possible to rollback changes and b) it makes it possible for other sessions to read the befor-data of data that a session is changing.

Whenever a session changes data on an database block, oracle creates a rollback entry in a rollback segment. Such a rollback entry consists of the original data (that is, how the db block looked like before the update), a block address, a datafile number, a transction id and the transaction's status (active or commited).

Whenever someone starts a query, there is a current system SCN, say 490249. Now, every block's SCN is compared to 490249.
If this blocks SCN is grater this 490249 (that is, the block has been changed since the query began), Oracle looks for a block that was actual with SCN 490249. This is a consistent get.

A commit executed too early can have distasterous effects: Assume you need to transfer $1500 from bank account 70-490930.1 to 70-909249.1. Then the following approach is very, very dangerous:

update cust_accounts
  set balance = balance - 1500
where
  account_no = '70-490930.1';

commit;

update cust_accounts
  set balance = balance + 1500
where
  account_no = '70-909249.1';

commit;

Further, assume, that the programm doing this transactions crashes somewhere between the first commit and the second commit. In this case, account 70-490930.1 will be deducted by $1500 but the money is lost.

This is where the beauty of transactions come into play. If you leave the first commit out, everything is save. The program may crash if it wishes, still it is guaranteed that there is no loss of money.