Implicit commit of Oracle transactions

Source: Internet
Author: User
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.