Implicit commit of Oracle transactions

To avoid implicit commit or rollback, make sure that only one or more DML operations are submitted or rolled back. This prevents subsequent DCL or DDL operations from automatically submitting the previous D

To avoid implicit commit or rollback, make sure that only one or more DML operations are submitted or rolled back. This prevents subsequent DCL or DDL operations from automatically submitting the previous D

Definition of implicit commit

It is also known as automatic submission, that is, the process of executing the commit statement is not displayed, and the operations in the session are automatically submitted to the database.

Implicit submission

1. Run the ddl statement normally. Including create, alter, drop, truncate, and rename.

2. Run the dcl statement normally. Including grant and revoke.

3. Exit isql * plus normally and do not explicitly issue a commit or rollback.

Considerations for implicit submission

1. When executing ddl statements, the previous dml operations will also be submitted to the database.

Because it is in a session, the preceding dml statement will be submitted to the Database "not spared" when executing the ddl statement.

2. Even if the ddl statement execution fails, the previous dml operations will be submitted to the database.

This is a bit strange. ddl execution fails. How can I submit it? This requires us to explore the nature of implicit commit (as described below ).

3. Summary based on previous 1 and 2

To avoid implicit commit or rollback, make sure that one or more DML operations are submitted or rolled back after they are completed. This prevents subsequent DCL or DDL operations from automatically submitting DML operations.

Nature of implicit commit

1. A ddl statement executes commit twice.

Commit;

Ddl statement;

Commit;

The first commit implicitly commits uncommitted transactions in the current session to ensure the rollback position when the ddl statement fails.

The second commit Changes the ddl

2. Why is implicit commit required?

To ensure transaction consistency. When executing ddl statements, Oracle needs to record metadata in its system tables (that is, in addition to table creation, many insert operations are performed ), consistency cannot be guaranteed if it is not submitted implicitly. From the perspective of the internal running mechanism, ddl statements and dml statements are quite different, dml logs each record of each statement for rollback, and ddl often does not need to be so complicated. In terms of functionality and ease of use, implicit commit is the best choice.

Related reading:

Implicit commit caused by DDL in Oracle PL/SQL

Oracle transaction row-Level Lock storage point rollback commit

Oracle transaction processing multiple SQL statements

Analysis of the complete process of Oracle transactions

Small Oracle transaction instances