Oracle_ Transaction Control Statements

A transaction is a logical unit of database operations that can contain one or more DML (data manipulation languages), DDL (data definition Language), and DCL (Data Control language) statements that form a logical whole.

The execution of a transaction has only two consequences: it is executed all, the database is brought into a new state, or all is not executed, and no modifications are made to the database.
There are two operations on a transaction: Commit (Commit) and rollback (ROLLBACK).

When a transaction is committed, modifications made to the database are permanently written to the database.
When a transaction is rolled back, all modifications made to the database are revoked, and the database reverts to the state before the operation.
Transactions can be used in any situation where the database is manipulated, including applications, stored procedures, triggers, and so on.

A transaction has four properties, and the first letter of the four attributes is acid.

These four properties are:
• Atomicity (atomicity): The transaction is either all executed or not executed, and partial execution is not allowed.
• Consistency (consistency): A transaction brings a database from one consistent state to another.
• Independence (Isolation): The execution of one transaction is not affected by other transactions.
• Continuity (Durability): Once a transaction is committed, it is permanently valid and unaffected by the shutdown and other conditions.
A transaction can contain multiple DML statements, either containing a DDL statement, or containing a DCL statement.

The transaction begins with the first SQL statement and ends in one of the following cases:
• Encountered a commit or rollback command.
• Encountered a DDL or DCL command.
• The system has errors, exits, or crashes.
In summary, a transaction is a series of operations that can bring the system into a new state, and if the transaction is committed, the database enters a new state, or the database reverts to the previous state of the transaction.
The benefit of using transactions in a database is to ensure data consistency first, and then to preview previous data changes before making permanent changes to the data, and to group logically related operations.
There are two ways to control transactions, one is implicit control, the database management system according to the actual situation of the decision to commit the transaction or ROLLBACK transaction;   SPRINGMVC Integrated mybatis Framework Source code
Another way is explicit control, where a commit or rollback command is placed at the end of a transaction to commit or rollback a transaction.
In the case of implicit control, the transaction encounters a DDL command, such as create, or encounters a DCL command, such as Grant, or exits gracefully from SQL *plus, which is automatically committed even if no commit or ROLLBACK command is issued.
If you exit abnormally from Sql*plus or a system crash occurs, the system automatically rolls back the transaction.
In the case of explicit control, the transaction is committed at the end of the transaction through a commit command, or a transaction is rolled back through a rollback command.

If the transaction is committed, the modifications made to the database will be written to the people database.

If a transaction is rolled back, it is generally rolled back to the beginning of the transaction, which makes no modifications to the database.
In Oracle, partial rollback of a transaction is allowed, that is, the transaction can be selectively rolled back to a point in the middle.
Partial rollback is achieved by setting the SavePoint (savepoint).

Several savepoint can be set through the SavePoint command in a transaction so that the transaction can be selectively rolled back to a savepoint.

When a user accesses a database, the data in the database is placed in a buffer, and the current user can browse the results of the data operation through a query operation.
If the transaction is not committed, other users cannot see the result of the modification of the transaction.

When a user modifies data in a table, the modified data is locked and other users cannot modify the row's data during that time until the user commits or returns to resist the transaction.
If the commit command is executed at the end of the transaction, modifications to the data will be written to the database, the previous data will be permanently lost, unrecoverable, other users can browse the modified results, the locks added to the data are freed, other users can perform new modifications to the data, and all the savepoint set in the transaction are deleted

If a savepoint is set in the transaction and the last execution of the rollback command is rolled back to a savepoint, the changes made by the DML statement after this savepoint are discarded, but the changes made by the DML statement before this savepoint are still not written to the database and can be committed or rolled back.

Oracle_ Transaction Control Statements