COMMIT Vs ROLLBACK top differences to manage data

The role of COMMIT

When performing batch transactions, you must know how often to perform a “commit.” “Controlling Transactions,” a COMMIT statement finalises a transaction. A COMMIT saves a transaction or writes any changes to the applicable table(s). Behind the scenes, however, much more is going on.

Some areas in the database are reserved to store completed transactions before the changes are actually written to the target table. 

Oracle calls these areas rollback segments. When you issue a COMMIT statement, transactions associated with your SQL session in the rollback segment are updated in the target table. After the update takes place, the contents of the rollback segment are removed.

A ROLLBACK command, on the other hand, clears the contents of the rollback segment without updating the target table.

As you can guess, if you never issue a COMMIT or ROLLBACK command, transactions keep building within the rollback segments. Subsequently, if the data you are loading is greater in size than the available space in the rollback segments, the database will essentially come to a halt and ban further transactional activity.

Not issuing COMMIT commands is a common programming pitfall, and regular COMMITs help to ensure stable performance of the entire database system.

The management of rollback segments is a complex and vital database administrator (DBA) responsibility because transactions dynamically affect the rollback segments, and in turn, affect the overall performance of the database as well as individual SQL statements.

So when you are loading large amounts of data, be sure to issue the COMMIT command on a regular basis.

Check with your DBA for advice on how often to commit during batch transactions.

Also read: DBA interview Questions | SQL Interview Questions