Implicit COMMIT vs Implicit ROLLBACK

Implicit COMMIT is normal behavior for Oracle. In certain situations, Oracle will commit everything automatically without you being involved. It is called Implicit COMMIT. When will it happen? It happens if someone has issued any DCL command (GRANT, REVOKE) or any DDL command (CREATE, ALTER, DROP, TRUNCATE, RENAME).

Implicit commit may also occur when user exits the session

Any DML command (INSERT, DELETE, MERGE, etc) won’t trigger Automatic Implicit COMMIT. All DML commands must be committed explicitly via COMMIT statement.

TCL command SAVEPOINT won’t trigger Automatic Implicit COMMIT as well.

Now, let’s clarify what may cause Oracle to do Automatic ROLLBACK, means implicit ROLLBACK which is happening without your knowledge or permission. Indeed, we don’t have many situations for it, because rolling back transactions implicitly is something weird. Users must control their transactions when it comes to rolling back. They should explicitly do a ROLLBACK command. But there is one situation when Oracle may decide to do it instead of you. It is a system crash.  Is Oracle is about to crash, or actually crashing already, it will try to roll back all uncommitted transactions. This is the only case when Oracle may allow itself to intrude in your data against your will, in order to protect the data.

Implicit COMMIT Implicit ROLLBACK
DCL command (GRANT, REVOKE) System crash
User exits the session

Leave a comment

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