Oracle BASICS (7): database transactions

Oracle BASICS (7): database transactions
I. Basic Concepts1. Transaction: The basic unit of concurrency control. A transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable unit of work. For example, for bank transfers: If you deduct money from one account and add money to another account, either or both of these operations will be executed. Therefore, we should regard them as a transaction. A transaction is the unit in which the database maintains data consistency. Data Consistency can be maintained at the end of each transaction.
2. Transactions and locks when performing transaction operations, oracle will lock the active table to prevent other users from modifying the table structure.
3. Transaction operation process 1) Start of the transaction (automatic start by default): setAutoCommit (false) 2) set the save point: savepoint pointAA
You can set multiple save points.
Once it is rolled back, it cannot be rolled back again. 3) Roll Back to the Save point (rollback failed after submission): rollback to pointAA; 4) transaction commit: commit
2. transactions have the following four basic features.
Atomic (Atomicity): operations contained in a transaction are considered as a logical unit. Operations in this logical unit are either all successful or all failed.
Consistency (Consistency): only valid data can be written to the database, otherwise the transaction should roll back to the initial state.
Isolation: Transactions allow multiple users to concurrently access the same data without compromising data correctness and integrity. At the same time, modifications to parallel transactions must be independent from those of other parallel transactions.
Durability (Durability): after the transaction ends, the transaction processing result must be solidified. 3. Transaction statements
Start TRANSACTION: BEGIN TRANSACTION
Submit TRANSACTION: COMMIT TRANSACTION
Rollback transaction: rollback transaction 4. TRANSACTION retention point SAVEPOINT pointName save point name -- custom save point name and location rollback to pointName save point name -- rollback to custom save point
Ii. transaction isolation level (isolation)
(1) standard transaction isolation level 1. read uncommitted: READ UNCOMMITTED
Dirty READ 2. read committed: READ COMMITTED
When reading data in trasaction A, add A shared lock to the record, but release immediately after reading ends. Other transaction B's attempt to modify this record will wait until the reading process in A ends, without the end of the entire trasaction. Therefore, the reading results for the same record may be different at different stages of trasaction.
Possible problems: repeated reading is not allowed. 3. repeatable read: REPEATABLE READ
For read records, add A shared lock until transaction A ends. Other transaction B's attempt to modify this record will wait until trasaction A ends.
Possible problems: Phantom reads may occur when a range query is executed. 4. SERIALIZABLE: serialization
Add A range lock (such as A table lock or A page lock) until transaction A ends. To prevent other operations such as insert and update in the range of trasaction B.
Phantom read, dirty read, and non-repeated read won't happen.

(2) Transaction Problems 1. Dirty reading
When transaction A reads the changes that another transaction B has not committed, dirty reads are generated. 2. Non-repeated read
The same query is performed multiple times in the same transaction A. Because of the modifications or deletions made by other committed transaction B, different result sets are returned each time, and non-repeated reads occur. 3. Phantom read
The same query is performed multiple times in the same transaction A. Because of the insert operations performed by other committed transaction B, different result sets are returned each time, and Phantom reads occur. (3) Oracle isolation Agent 1. SQL92 standard 1) read committed: READ COMMITTED
This is the default transaction isolation mechanism of oracle.
Make sure that no dirty reads are performed. Non-duplicate reads and Phantom reads may occur on a single ticket. 2) SERIALIZABLE: serialization
Transactions are executed in the same order as queues.
You can only see the changes committed by other transactions and the changes made in the event service before the start of this transaction.
No dirty reads, non-repeated reads, or Phantom reads
Serializable: Provides read consistency provided by read-only, and allows DML (update \ insert \ delete) operations. 2. Non-SQL92 standard: read-only
No dirty reads, non-repeated reads, or Phantom reads
Only read operations are allowed. DML (update \ insert \ delete) operations are not allowed.
(4) set Oracle isolation agent Level 1. Set the isolation level of a transaction
Set transcation isolation level read committed;
Set transcation isolation level serializable;
Set transcation read-only; 2. set the isolation level of the entire session
Alter session set isolation_level read committed;
Alter session set isolation_level serializable;