Oracle Transaction Processing

1. Transaction concept:

Concept: transactions in databases are logical units 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.

Main features: ensure the integrity of the database.

2. ACID properties of transactions

A transaction is formed for a group of SQL statement operations. The database operating system must ensure the atomicity, consistency, isolation, and durability of these operations.

1. Atomicity)

The atomicity of a transaction indicates that all operations contained in a transaction are either fully or not performed. That is to say, all activities are either fully reflected or not reflected in the database to ensure Database Consistency.

2. Consistency)

Transaction consistency means that data in the database must meet the business rule constraints before and after transaction operations and after transaction processing.

3. Isolation)

Isolation refers to the ability of the database to allow multiple concurrent transactions to read, write, or modify the data at the same time. isolation can prevent concurrent execution of multiple transactions, data inconsistency is caused by cross-execution of their operation commands.

4. durability)

Transaction Persistence means that after the transaction processing is completed, its modifications to the data should be permanent. Even if the system encounters a fault, it will not be lost, which is determined by the importance of data.

3. transaction control statements

In the Oracle database, the start transaction processing statement is not provided, and all transactions start implicitly. That is to say, in oracle, you cannot use commands to start a transaction. the first statement of the oracle task to modify the database, or some situations that require transaction processing, is the implicit start of the transaction. However, when you want to terminate a transaction, you must end the transaction with the commit and rollback statements.

According to the ACID attribute of the transaction, oracle provides the following transaction control statements:

Set transaction

Set constrains

The constraint mode refers to whether the constraints in the database are immediately applied to the data when the data is modified in the transaction, or whether the constraints are applied after the end of the current transaction.

The Savepoint creates a storage point in the transaction. when the transaction is rolled back due to an exception in processing the transaction, you can specify that the transaction is rolled back to a storage point and then re-Execute from the storage point.

Release savepoint Delete storage point

Rollback rolls back the transaction to cancel any operations on the database

Commit commits transactions to persistently store database operations.

3. 1. Set the properties of a transaction:

The set transaction statement can be used to set various attributes of a transaction. The statement must be placed in the first statement of transaction processing.

That is to say, it must be processed in any insert, update, delete statements and other transactions.

The Set transaction statement allows you to Set the following attributes of a transaction.

Specify the isolation layer of the transaction

Specify the bucket used by the rollback firm

Naming transactions

Note: When the set transaction statement is used to set attributes, the bucket used for rollback is rarely used. naming transactions are also very simple. Only distributed transactions can reflect the purpose of naming transactions.

Note: set transaction is only valid for the current transaction and ends the transaction. The current settings of the transaction will be invalid.

1 2 3 Next Page