CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Colin P. Hill
Colin P. Hill

Reputation: 422

Is there difference in overhead between COMMIT and ROLLBACK?

I am developing an application which, within a single transaction, writes to a table and then reads that table. Once it's done reading, those changes are no longer needed and can be safely discarded. But on the other hand, the changes will not interfere with anything else if they are committed.

In short, I can use either rollback or commit to end the transaction, with only efficiency to consider.

So which, if either, will be faster than the other, and why?

Upvotes: 3

Views: 373

Answers (2)

neshkeev
neshkeev

Reputation: 6476

Oracle generates redo on-line logs files and undo data. On-line redo log files contain DML/DDL statements to redo your transaction (in case of blackout) and to undo your data if the rollback statement is invoked.

Commit is a very fast operator, its time is constant and isn't depended on the size of the transaction. This is possible because the LGWR process writes redo changes to the disc during the transaction in background. If you use asynchronous commits, for example with commit write nowait batch;, the time of a commit will be almost equal to 0.

Rollback depends on the size of transaction, because it needs to undo any statement you have in the redo log files that is related to the transaction, so the time of a transaction might be equal to time of the rollback of the transaction.

For short-term transaction there might be no differences but for middle-term and long-term transaction you will notice that time or rollback is almost equal to time of the transaction.

Upvotes: 5

Martin Drautzburg
Martin Drautzburg

Reputation: 5243

Commit should be faster, because Oracle uses "fast commit", i.e. it assumes you will commit anyways and writes you changes to the DB files or the buffer cache right away.

Since you may still decide to rollback, it stores "before" information in its rollback segemnts. A commit essentially discards portions of the rollback segments and marks the transaction as committed.

A rollback however is like a full "undo" in an application. Oracle needs to replace its actual view of the data by the stuff in the rollback segments. This is real work.

Still eventually you need to clean up your data and then you need to invest some time.

Upvotes: 2

Related Questions