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:
- Gracefully exit
- Issue disconnect
- Issue connect
- Issue valid DDL
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. For example, we disconnect and exit sqlplus program:
Issuing disconnect will notify database to disconnect from this session, but it does not exit the program. For example:
Issuing connect will notify database to disconnect from current session implicitly and start another new session in the same program. For example:
SQL> conn hr/hr@orcl
An implicit commit will be issued before a valid Data Definition Language (DDL). Which means, you should make sure that new data is what you want, a DDL will apply changes to the database.
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 this post, I'll talk about how to turn autocommit on and off in various tools.
- SQL*Plus Autocommit ON and OFF
- SQL Developer Autocommit ON and OFF
- Toad Autocommit ON and OFF
- JDBC Autocommit ON and OFF
SQL*Plus Autocommit ON and OFF
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:
SQL> SET AUTOCOMMIT ON;
SQL> SHOW AUTOCOMMIT;
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.
For your reference, there're more interoperability among AUTOCOMMIT, EXITCOMMIT and EXIT command.
SQL Developer Autocommit ON and OFF
We can also enable autocommit in connection tools like SQL Developer.
Enter SQL Developer Preferences
Check "Auto Commit"
Navigate Database -> Advanced, then check "AutoCommit".
Click OK to exit.
Toad Autocommit ON and OFF
We can also enable autocommit in connection tools like Toad.
Enter Toad Option Page
Then we find Oracle -> Transactions.
Check "Commit after every statement"
Click OK to exit.
JDBC Autocommit ON and OFF
Oracle JDBC driver enables automatic commit on by defaults for every new connection. Since it might be expensive to commit to the database after every successful DML, DBA usually suggests ASA (Application Server Administrator) to disable this mode on application-server-level controlled by ASA or connection-level controlled by Java program to improve the performance.
We can expect the performance will be improved greatly, especially when those DML generate too many but share very little cursors. To disable the auto-commit mode by doing this:
OracleDataSource ods = new OracleDataSource();
Connection conn = ods.getConnection();
// To disable auto-commit mode.
// To enable auto-commit mode again by doing this.
Without auto-commit mode, JDBC hands over the controller of commit to Java program to decide when and where to commit the transactions. Therefore, it will not only improve performance, but also change the business logic.
For example, thousands of various transactions are processing online, they could not see the real-time results from other sessions as usual do. Furthermore, some junior Java developers might forgot to commit DML in the program and causes a lot of unnecessary, repeated or pending transactions, and users may get confused for a long time.