Oracle Transaction Processing Mechanism explanation, oracle Transaction Processing Mechanism

Oracle Transaction Processing Mechanism explanation, oracle Transaction Processing Mechanism

I. 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.

Ii. Features of things

A) main features: ensure the integrity of the database.

B) ACID properties of transactions

I. 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.

Ii. Consistency)

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

Iii. Isolation)

Isolation refers to the ability of the database to allow multiple concurrent transactions to read 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.

Iv. 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.

Iii. Data exceptions

A) the isolation of a transaction defines the degree of isolation between a transaction and other transactions. to better understand the isolation layer, we first discuss the possible access to the same database by concurrent transactions. in general, concurrent transactions occur.

I. Wrong read | dirty read

Wrong read | dirty read: when a transaction modifies the data, the other transaction reads the data, but the first transaction cancels the data modification for some reason and returns the data to the original state, this is because the data read by the second transaction is inconsistent with the data in the database. this is called an error.

Ii. Non-repeated read | non-repeated read

Non-repeated read: after a transaction reads data from the database, the other transaction updates the data. When the first transaction reads the data again, you will find that the data has changed, which is non-repeated read. The result of Non-repeated reads is that the data read twice before and after a transaction is different.

Iii. False read | phantom read

False read: if a transaction reads data based on a certain condition, the other transaction updates the data in the same table. When the first transaction reads data again, different rows are returned based on the search criteria. This is a false read.

B) The exceptions encountered in the transaction are related to the transaction isolation settings. The more the transaction isolation settings, the fewer exceptions will occur, but the lower the concurrency effect, the less the isolation settings of transactions, the more exceptions occur, and the higher the concurrency effect.

4. Select the isolation layer

For data read in 3, the isolation levels of four transactions are defined in ansi SQL standard 92, as shown in

Oracle supports two of the preceding four isolation layers: read committed and serializable. In addition, the oralce also defines the read only and read write isolation layers.

Read committed: the default isolation layer of oracle.

Serializable: when you set the isolation layer of a transaction, the transaction is completely separated from the transaction, and the transaction is executed in serial mode. This does not mean that a transaction must end before starting another transaction, it means that the execution results of these transactions are consistent with those of one transaction.

When Read only and read write are used, the transaction cannot contain any operation statements to modify data in the database, including insert, update, delete, and create statements. Read only is a subset of serializable. The difference is that read only is Read-only, while serialable can perform DML operations. Read write is the default setting. This option indicates that the transaction can have access statements and modify statements, but is not frequently used.

V. Transaction Control

A) 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.

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

C) Set transaction to Set transaction attributes

D) Set constrains to Set the constraint mode of a transaction.

E) Constraint mode: when the data is modified in a transaction, the constraints in the database are immediately applied to the data, or the constraints are postponed until the end of the current transaction.

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

G) Release savepoint delete a storage point

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

I) Commit commits transactions to persistently store database operations.