Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand the transactions in oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "how to understand the affairs in oracle"!

For a set of SQL statements to be a transaction, the database management system must guarantee atomicity, consistency, isolation, and durability of the set of operations.

Atomicity: It means that all operations contained in things are either all done or not done, that is to say, all activities of things are either all reflected in the database or not reflected at all, so as to ensure that the data is consistent.

Consistency: refers to the database before transaction operations and operation processing, in which the data must meet the constraints specified by the business.

Isolation: refers to the ability of a database to allow multiple concurrent transactions to read, write and modify data at the same time. Isolation can prevent data inconsistency caused by the cross execution of their operation commands when transactions are executed concurrently.

Persistence: When a transaction ends, its impact on the database is permanent, and even if the system encounters a failure, the data will not be lost.

A database transaction consists of the following components:

One or more DML statements

A DDL statement

A DCL statement

Execution of transactions:

Start with the execution of the first DML statement

End with one of the following:

COMMIT or ROLLBACK statement

DDL or DCL statements (autocommit)

User session ends normally

System anomaly terminated

Note: Using COMMIT and ROLLBACK statements, we can: ensure data integrity; preview data changes before they are committed; and group logically related operations.

transaction control statement

Set transaction: Set transaction properties

Set constraints: Set the constraint mode of the current transaction. Set whether the constraints will take effect immediately when modifying data or after the current transaction ends.

Savepoint: Establish a storage point in the transaction. When rolling back, you can specify where to roll back and then execute it again.

Release savepoint: Delete a savepoint

Rollback: Rolls back the transaction. Level cancels any changes made to the database

Commit: Commit the transaction, and permanently save the changes made to the database in the transaction

data anomalies

Because Oracle supports concurrent execution of multiple transactions, the following data exception occurs.

wrong reading| Dirty read: When user1 is reading table A in the database, user2 is modifying table A, user2 has finished modifying, user1 reads table A again, user1 reads the modified data, and user2 unmodifies, user1 reads table A, called "wrong read" or "dirty read"

unrepeated reading| Non-duplicate read: refers to a transaction reads the data in the database, another transaction updates the data, when the first transaction reads the data again, it will find that the data has changed, this is non-duplicate read. The result of non-duplicate reads is that the data read two times before and after a transaction is different.

dummy read| A false read occurs when one transaction reads data based on a condition and another transaction updates the same table, and the first transaction reads the data again and returns a different row based on the search condition.

These exceptions encountered in a transaction are related to the isolation setting of the transaction. The more isolation the transaction is set, the fewer exceptions occur, but the lower the concurrency effect. The less isolation the transaction is set, the more exceptions occur, the higher the concurrency effect.

selective isolation layer

ANSI SQL Standard 92 defines four transaction isolation levels for inconsistencies when reading data in 3. As shown below:

isolation level

wrong reading| dirty reads

unrepeated reading| Do not repeat reading

dummy read| phantom reading

Read uncommitted

is

is

is

Read committed

no

is

is

Repeatable read

no

no

Serializable

no

no

no

Oracle default isolation level is read committed

Oracle supports the two types of read committed and Serializable in the above address isolation layer. In addition, oracle also sets the read only and read write isolation layers.

Serializable: Set the isolation layer of the transaction to this,

Read only : The transaction cannot have any operation statements that modify data in the database, such as insert, update, delete, create statements, read only is a subset of Serializable, the difference is read only, Serializable and execute DML statements.

Read write: This is the default setting. Changing this option means that access statements can be included in transactions. Modify the statement. But not very often.

Set transaction properties

Create a set transaction statement

For example:

Set transaction read noly

Or:

Set transaction read write

Or:

Set transaction isolation level read commnitted

Or:

Set transaction isolation level serializable

Note: These statements are mutually exclusive. You cannot set two or more options at once

At this point, I believe that everyone has a deeper understanding of "how to understand the affairs in oracle," so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.