About the transaction management for Oracle to process DDL and DML statements

I. Start with the question

During the development process, if 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.

Ii. concepts related to transactions

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 (I .e., Atomicity), Consistency, Isolation (Isolation, also called independence), and Durability )), the concept of ACID is described in Section 4 of ISO/IEC 10026-1: 1992. (I haven't downloaded this standard for a long time. Someone sent me a copy, haha ).

2. The oracle version of my machine is 10 Gb, And the jdbc version of 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 .).

Then let's take a look at the definition of transactions in JDBC3:

Transactions are used to provide data integrity, correct application semantics, and a consistent view of data during concurrent access. all JDBC compliant drivers are required to provide transaction support. transaction management in the jdbc api mirrors the SQL99 specification and includes these concepts:
Auto-commit mode
Transaction isolation levels
Savepoints

Iii. 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 also do not provide related methods for rollback (spring and ejb may be acceptable, but they have not been tested, so 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 be a little troublesome, but what if I don't do this? After the SQL statement is successfully operated, the reverse operation is followed by the definition (internal classes are used ), 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.