Oracle Foundation (VII): Database transactions

first, the basic concept1, transaction (Transaction): is the basic unit of concurrency control. The so-called transaction, which is a sequence of operations that are either executed or not executed, is an inseparable unit of work. For example, a bank transfer job: Debit from one account and add another account, both of which are either executed or not executed. Therefore, they should be regarded as a business. A transaction is a unit of data consistency maintained by a database that maintains data consistency at the end of each transaction.
2. Transaction and lockWhen a transactional operation is performed, Oracle locks the table in effect, preventing other users from changing the structure of the table.
3, the operation of the transaction process1) Start of transaction (default Auto-start): Setautocommit (False)2) Set save point: SavePoint pointaa

• Multiple save points can be set
• Once it has been rolled back, it cannot be returned again.

3) Rollback to savepoint (cannot rollback after commit): Rollback to Pointaa;4) Transaction commit: Commit
2, A transaction has the following 4 basic characteristics.

• < Span style= "font-family:arial" >atomic (atomicity): The operations contained in a transaction are treated as a logical unit, and the operations in this logical unit are all successful, Or they all fail.  
• < Span style= "font-family:arial" >consistency (consistency): only legitimate data can be written to the database, or the transaction should roll it back to its original state.
• isolation (Isolation): Transactions allow multiple users to concurrently access the same data without destroying the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modifications of other parallel transactions.
• durability (persistence): After the transaction ends, the result of the transaction must be cured.  

3, the statement of the transaction

• Start things: Begin TRANSACTION
• Commit a thing: Commit TRANSACTION
• ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

4. Storage point of the transaction savepoint pointname Save Point Name--the name and location of the custom savepoint ROLLBACK to pointname Save point Name-rollback to a custom savepoint
II. Transaction Isolation level (isolation )
(i) standard transaction isolation level1. READ UNCOMMITTED: Not submitted

• Dirty reads occur

2. Read COMMITTED: Reading submitted

• A shared lock is added to the record when the data is read in Trasaction A, but the read end is released immediately. Other transaction B attempts to modify this record will wait until the end of the read process in a, rather than the end of the entire trasaction a. Therefore, the read results for the same record may be different at different stages of trasaction a.
• Problems that may occur: non-repeatable reading.

3, REPEATABLE READ: Repeatable read

• for a read-out record, add a shared lock until transaction a ends. Other transaction B attempts to modify this record will wait until Trasaction a ends.   
• Possible issues: Phantom reads may occur when a scope query is executed.

4. SERIALIZABLE: Serialization

• Add range locks (such as table locks, page locks, and so on) until transaction a ends. This prevents other Trasaction B operations such as insert,update in this range.  
• Magic Reading, dirty reading, non-repeatable reading and other problems will not occur.

(ii) issues of the business1. Dirty reading

• Dirty reads occur when a transaction a reads a modification that has not yet been committed by another transaction B.

2. Non-repeatable reading

• The same query occurs multiple times in the same transaction A , and non-repeatable reads occur each time a different result set is returned because of modifications or deletions made by other commit transaction B.

3. Phantom reading

• The same query occurs more than once in the same transaction , and the phantom reads occur each time a different result set is returned because of another insert operation that commits transaction B.

(iii) Oracle's isolation Agent1, the SQL92 standard 1)readCOMMITTED: Read Committed

• This is the default transaction isolation mechanism for Oracle
• Guaranteed not to be dirty read, single may appear non-repeatable read and Phantom read.

2)SERIALIZABLE: Serialization

• Provides a transaction that looks like a queue execution in one order.
• You can only see changes that were committed by other transactions and changes made in this transaction before the start of this transaction.
• Guaranteed no dirty reads, non-repeatable reads, and Phantom reads
• serializable: Provides a Read-only provides read consistency while allowing DML (UPDATE\INSERT\DELETE) Operations

2, non- SQL92 Standard:read-only

• Guaranteed no dirty reads, non-repeatable reads, and Phantom reads
• Read only, cannot operate DML (update\insert\delete) operation.

(iv) setting up an Oracle isolation Agent1. 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;

Oracle Foundation (VII): Database transactions