COMMIT

From Oracle FAQ
Jump to: navigation, search

COMMIT is an SQL command used to instruct the database to save all changes made and end the current transaction. Use the ROLLBACK command to undo changes.

Example:

INSERT INTO tab1 VALUES ('val1', 'val2', 'val3');
COMMIT;

[edit] Sync COMMITs

This is the default behavior.

Example:

COMMIT WRITE IMMEDIATE WAIT;

[edit] Async COMMITs

Example:

COMMIT WRITE BATCH NOWAIT;

Async commits are normally a bad idea (transactions may be lost if the system crashes) and should only be allowed under very limited and controlled conditions.

When doing batch loads, you should rather do batch commits. This feature should only be used if batch commits cannot be done (which, in general should not be the case).

[edit] COMMIT performance

To increase COMMIT performance, look at:

  • don't COMMIT after each record, batch COMMITS together
  • put your redo logs on faster disks
  • ensure the DB doesn't switch too many logs (increase redo log size)
  • use Async COMMITs