Oracle transaction Summary

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. Data exceptions
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, the following three situations occur in concurrent transactions:
Wrong read | dirty read
Non-repeated read | non-repeated read
False read | phantom 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.

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.

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.

The exceptions encountered in the transaction are related to the isolation settings of the transaction. The more the isolation settings of the transaction, the fewer exceptions occur, but the lower the concurrency effect, the less the isolation settings of transactions, the more exceptions occur, and the higher the concurrency effect.
2. 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:
Incorrect read at the isolation layer | non-repeated read by dirty reads | non-repeated read by false reads | phantom read
Read uncommitted (non-committed READ) is yes
Read committed (submit READ) No Yes
Repeatable READ (Repeatable READ) No Yes
Serializable (Serial read) No

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.



3. Create a set transaction statement
List as follows:
Set transaction read only
Set transaction read write
Set transaction isolation level read committed
Set transaction isolation level serializable

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


4. End the transaction:
Under what circumstances does the transaction end?

1. Commit a transaction using Commit.
2. rollback

// Read-only case
SQL> set transaction read only;

Transaction set

SQL> select * from users where username = 'mj ';

USERNAME PASSWORD NAME ADDRESS ZIP
---------------------------------------------------------------------------------------
Mj redarmy Chen hongjun

SQL> update users set password = '000000' where username = 'mj ';

Update users set password = '000000' where username = 'mj'

ORA-01456: cannot perform insert/delete/update operations in read only transactions

// Dirty read case
SQL> set transaction isolation level read committed;

Transaction set

SQL> select * from users where username = 'mj ';

USERNAME PASSWORD NAME ADDRESS ZIP
---------------------------------------------------------------------------------------
Mj redarmy Chen hongjun

SQL> update users set password = '000000' where username = 'mj ';

1 row updated

SQL> select * from users where username = 'mj ';

USERNAME PASSWORD NAME ADDRESS ZIP
---------------------------------------------------------------------------------------
Mj 123 Chen hongjun

SQL> rollback;


The above content is created by redarmy_chen, If You Need To reprint, please attach the source, if you have any questions, please send an email to the redarmy_chen@qq.com