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. To exit a program will notify database to disconnect from this session and then close the program. The program could be CLI or GUI programs.

  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.

SET AUTOCOMMIT

To turn autocommit on, please follow the command:

SQL> SET AUTOCOMMIT ON;
SQL> SHOW AUTOCOMMIT;

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:

SQL> SET AUTOCOMMIT 100;
SQL> SHOW AUTOCOMMIT;

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.

Leave a Reply

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