Oracle Database manages DDL statements and DML statements

OracleIn database operations, we will encounter ProcessingDDL statementAndDML statementsIf the Oracle DDL statement and DML statement need to be processed in one transaction, the method either succeeds or fails. SQL Server and other databases can solve this problem because it can roll back DDL statements. Oracle will execute commit before executing DDL statements, therefore, you cannot roll back the DDL statement.

Transaction related concepts:

1. First, let's talk about the database's definition of transactions:

A transaction in a database is a logical unit of work. A transaction is composed of one or more SQL statements that complete a group of related behaviors, the transaction mechanism is used to ensure that the operations performed by these SQL statements are either completely successful, and the whole unit of work is completed or not executed at all. Transactions have ACID properties: Atomicity), Consistency), Isolation, also known as independence), persistent Durability), ACID concept in ISO/IEC 10026-1: the fourth section of the 1992 file indicates that I haven't downloaded this standard for a long time, and some of them sent me a copy ).

2. Assume that the Oracle version is 10 GB and the jdbc version corresponding to 10 Gb is 3. Let's take a look at the transaction concepts related to JDBC3:

JDBC3 mentions transactions and distributed transactions. We have not mentioned nested transactions. In essence, databases never have the concept of nested transactions, but applications nest the transaction operation process for different purposes, even Oracle's autonomous transactions can be classified as applications (stored procedures or triggers). From the perspective of applications, nested transaction processing is how the application converts the nesting at the application layer to single transaction operations at the database layer. Spring provides a solution for Java EJB. In addition, Let's mention JTA, it provides the suspend and resume functions of transactions. In fact, what is the concept of suspension in database transactions? It only changes to a database connection, so that new database transactions begin, the old database transactions are no longer operated and are directly resume .).

Solution:

To solve a problem, finding the key to the problem is half the success. As mentioned above, what is the key to this problem?

Oracle will execute commit before executing DDL statements, so it cannot roll back the DDL statements. Jdbc transactions do not provide related methods to roll back spring and ejb. It may be acceptable, but it has not been tested. It is hard to say ).

Since some frameworks of the application cannot provide this function, you can only find a solution by yourself. How can this problem be solved? Reverse operation! It may feel a little troublesome, but what if I don't do so? After the SQL statement is successfully operated, it will be used in conjunction with the definition of the reverse operation ), in a large transaction, if an exception occurs, execute the reverse operation in the exception. Furthermore, if all DDL statements can be connected to different databases with DML statements, the DML statements do not need reverse operations, saving a lot of work, how nice it is to roll back transactions with DML statements! Of course, the premise is that during DDL execution, it cannot cause conflicts with the resources of DML operations, that is, the lock issue.

Here is an introduction to the transaction management of DDL statements and DML statements in the Oracle database. I hope this introduction will be helpful to you!