Oracle and DB2, Comparison and Compatibility/Process Model/Transaction Management/Oracle

Oracle

In Oracle, transactions are implicitly begun after a connection to the database, and thereafter after each SQL statement after a commit or rollback. By definition the transaction ends when you commit, rollback or disconnect from the database. Implicit commits are issued after each DDL statement (i.e. Alter, Drop, Create). This implicit transaction control means that there can be only one logical transaction per session, and is another feature of Oracle that aims to simplify transaction control by preventing the nesting of transactions.

It is also possible to set savepoints during a transaction. While you might not know if a transaction is complete enough to be committed, you may know that parts of the transaction have executed successfully (and these may be significant units of work that have consumed a large amount of resources). If you set a savepoint after these elements have executed to your satisfaction, then a subsequent problem with the whole transaction can be rolled back to this savepoint. This gives you the option to attempt to fix the problem and continue, continue with another set of operations, or even roll back the whole transaction if appropriate.

Transaction management is made possible by Orace saving a BEFORE and AFTER image of modified data in the undo log buffer and the buffer cache. A commit at this point will force the log writer (LGWR) to copy the undo log buffer entry to the undo log file group on disk. On a log switch, LGWR will call a checkpoint to flush all changed (known as ‘dirty’) blocks in the buffer cache to disk.