How to Set Autocommit ON

  • by

Implicit Commit

In the default behavior, if session A wants to see session B's results, he must be waiting for session B to commit his transaction explicitly or an implicit commit is triggered.

Implicit commits will happened under normal session terminations:

  1. Gracefully exit.
  2. Exiting any connection program will notify database to disconnect from this session and then close it. The program could be CLI or GUI programs.

    SQL*Plus is EXITCOMMIT ON by default, other tools may prompt you to confirm the change.

  3. Issue disconnect.
  4. Issuing disconnect will notify database to disconnect from this session, but it does not exit the program.

  5. Issue connect.
  6. Issuing connect will notify database to disconnect from current session implicitly and start another new session in the same program.

A normal session with any open or uncommitted transactions will never be expired. Database will wait and wait until an explicit indication from the client, which means there will be no timeout on a session holding any open or uncommitted transactions.

An abnormal session is another story. Once PMON acknowledges a connection is dead due to abnormal termination or network problems, it will rollback all open and uncommitted transactions, release locks and memory, and cleanup or reset the process.

In some cases, AP needs real-time data to serve different sessions, you can SET AUTOCOMMIT ON to automatically commit every successful DML statement including insert, update and delete operation.


To turn autocommit on, please follow the command:


autocommit IMMEDIATE
Commit Complete

Automatic commit every single successful DML statement will impact performance when thousands of statements must be committed individually in a few second during hot hours. Hence, automatic commit every N successful DML statements is more reasonable and efficient.

To set automatic commit for every N successful DML, please follow the command:


AUTOCOMMIT ON for every 100 DML statements

Before set autocommit on, you would be better to understand the features and what the differences between autocommit on and off.

  • By default, autocommit is OFF, so we can manipulate data with chances to regret by rollback to original state, once the data is ready, we have a last chance to confirm all the changes are correct before the data goes public.
  • There is no "fake" insert, update and delete with autocommit ON, every movement will immediately make data open to other sessions to retrieve, record and reserve. If the data contains sensitive information or legal facts, autocommit ON may not be a good idea.

For your reference, there're more interoperabilities among AUTOCOMMIT, EXITCOMMIT and EXIT command.

Leave a Reply

Your email address will not be published. Required fields are marked *