Talk about the transaction management of Oracle Processing DDL and DML statements __oracle

First, say the question

In the development process, you encounter situations where Oracle DDL statements and DML statements need to be handled in one transaction, either successfully or fail. For this problem, databases such as SQL Server can be resolved because they can rollback DDL statements, and Oracle executes a commit while executing a DDL statement, so the DDL statement cannot be rolled back.

II. Business-related concepts

1, first of all, said that the database on the transaction definition:

In a database, a transaction is a logical unit of work, consisting of one or more SQL statements that complete a set of related behaviors, ensuring that the operations performed by this set of SQL statements are either completely successful, complete the unit operation, or do nothing at all. Transactions have ACID properties (i.e. atomicity (atomicity), consistency (consistency), isolation (isolation, also known as independence), persistence (durability), and the concept of acid in ISO/IEC 10026-1:1992 file in the fourth paragraph of the description (I looked for a half-day also did not download to this standard, who have sent me a ah, hehe).

2, the Oracle version of my machine for 10g,10g corresponding JDBC version of 3. Take a look at JDBC3. About Transaction Concepts:

JDBC3 refers to transactional and distributed transactions. There is no mention of nested transactions that are often said. The database has never had the concept of nested transactions in nature, but the application is nesting the operations of transactions for different purposes, even if Oracle's autonomous transactions can be classified as applications (stored procedures or triggers or something), from an application standpoint, Nested transactions are how the application transforms the application-level nesting into a single transaction at the database level, which provides a solution to the Java domain ejb,spring, and JTA, which provides the Suspend,resume function of the transaction, In fact, database transactions there is what the concept of what hangs, it is only a database connection, so that the new database transactions began, the old database transactions will no longer operate, directly its resume. ）。

So let's look at the definition of the transaction in JDBC3:

Transactions are used to provide data integrity, correct application, semantics, and a consistent view of data during Concu Rrent access. All JDBC compliant drivers are required to provide transaction. Transaction Management in the JDBC API mirrors the SQL99 specification and includes these concepts:
Auto-commit mode
Transaction Isolation Levels
Savepoints

Third, the solution

To solve a problem, to find the crux of the problem, is equal to half the success. As mentioned above, what is the crux of the problem?

Oracle executes a commit while executing a DDL statement, so the DDL statement cannot be rolled back. JDBC transactions also do not provide relevant methods for rolling back (spring, EJB may be, but not tested, not easy to say).

Since the application of a number of frameworks can not provide this function, you can only find ways to solve, how to solve. Reverse operation. Maybe it's a bit of a hassle, but how about not doing it, after the operation of the SQL statement succeeds, followed by the definition of the reverse operation (the internal class comes in handy), in a large transaction, if there is an exception, in the exception of the reverse operation can be carried out. Further, if all DDL statements can be connected to DML statements using a different database, then the DML statement does not have to be reversed, saving a lot of work, and DML statements with the transaction rollback how good ah. The premise of this is, of course, that when DDL executes, the resource that cannot and DML operations causes a conflict, a lock problem.