Oracle _ transaction control statement

Oracle _ transaction control statement

A transaction is a logical unit for database operations. A transaction can contain one or more DML (data manipulation language), DDL (Data Definition Language), and DCL (Data Control Language) statement, which forms a logical whole.

There are only two types of transaction execution results: either to execute all, bring the database into a new state, or not to execute all, and do not modify the database.
There are two transaction operations: COMMIT and ROLLBACK ).

When a transaction is committed, the changes made to the database are permanently written to the database.
When a transaction is rolled back, all the modifications made to the database are revoked, and the database is restored to the status before the operation.
Transactions can be used to operate databases, including applications, stored procedures, and triggers.

A transaction has four attributes. The first letter of these four attributes is ACID.

These four attributes are:
· Atomicity: the transaction is either executed in full or not, and partial execution is not allowed.
· Consistency: transactions bring databases from one consistent state to another consistent state.
· Independence: the execution of a transaction is not affected by other transactions.
· Durability: Once a transaction is committed, it is permanently valid and not affected by shutdown.
A transaction can contain multiple DML statements, one DDL statement, or one DCL statement.

The transaction starts with the first SQL statement and ends in one of the following cases:
· Encountered the COMMIT or ROLLBACK command.
· Encountered a DDL or DCL command.
· System errors, exits, or crashes.
In short, a transaction is a series of operations that can bring the system into a new state. If the transaction is committed, the database enters a new state. Otherwise, the database is restored to the previous state of the transaction.
The advantage of using transactions in the database is that the data consistency can be ensured first, and the previous data changes can be previewed before the data is permanently modified, you can also group logical operations.
There are two ways to control transactions. One is implicit control. The database management system determines whether to commit or roll back the transaction based on the actual situation;
Another method is explicit control. Put a COMMIT or ROLLBACK command at the end of the transaction to COMMIT or roll back the transaction.
For implicit control, the transaction encounters a DDL command, such as CREATE, or a DCL command, such as GRANT, or Exits normally from SQL * Plus, this transaction is automatically committed even if no COMMIT or ROLLBACK command is issued.
If SQL * Plus unexpectedly exits or the system crashes, the system automatically rolls back the transaction.
For explicit control, COMMIT the transaction through the COMMIT command at the end of the transaction, or roll back the transaction through a ROLLBACK command.

If the transaction is committed, the changes made to the database are written to the database.

If a transaction is rolled back, it is generally rolled back to the beginning of the transaction, that is, the database is not modified.
In Oracle, partial rollback transactions are allowed, that is, transactions can be selectively rolled back to a certain point in the middle.
Partial rollback is implemented by setting the SAVEPOINT.

In a transaction, you can use the SAVEPOINT command to set several save points, so that you can selectively roll back the transaction to a save point.

When a user accesses a database, the data in the database is stored in the buffer zone. Currently, the user can query and view the data operation results.
If no transaction is committed, other users cannot see the Modification result of the transaction.

When a user modifies the data in a table, the modified data will be locked. Other users cannot modify the data in this row until the user submits or changes the transaction.
If the COMMIT command is executed at the end of the transaction, the changes to the data will be written to the database. The previous data will be permanently lost and cannot be recovered. Other users can view the modified results, the lock on the data is released. Other users can make new changes to the data and delete all the Save points set in the transaction.

If a save point is set in the transaction and the ROLLBACK command is run at the end of the transaction to roll back to a save point, the modifications made by the DML statement after the save point are discarded, however, the changes made by the DML statement before saving the vertex are still not written to the database, and can be committed or rolled back.