Oracle BASICS (7): database transactions and oracle transactions

Oracle BASICS (7): database transactions and oracle 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. SAVEPOINT pointName of the transaction storage point -- name and position of the custom storage point -- rollback to pointName -- rollback to the custom storage 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 for the entire session

• Alter session set isolation_level read committed;

• Alter session set isolation_level serializable;