Oracle Transactions (RPM)

Review Oracle transactions today with the following records:

1. Transaction definition

           A transaction is the consistency of the data, which is supported by the associated DDL or DML statements, and the results of the statement execution either succeed together or fail together. We know that the SQL92 standard defines four features of database transactions:

• Atomicity (atomicity): All the SQL statements contained within a transaction are performed as a whole, indivisible, either done, or not.
• Consistency (consistency): At the beginning of the transaction, the data in the database is consistent and the data of the database should be consistent at the end of the transaction
• Isolation (Isolation): Multiple transactions concurrently run independently, without interfering with each other, a transaction modification, new, delete data based on the transaction isolation level of the current transaction, the rest of the transaction can see the corresponding results (here's why, I will give my specific examples to analyze)
• Persistence (Durability): After a transaction is committed, the data is permanently saved

2. Transaction control Commands

• Commit COMMIT Transaction
• SavePoint Transaction Save Point (SavePoint a)
• ROLLBACK (TO) rollback [rollback to defined save Point] ROLLBACK to a

3. The concept of locks     Locks are designed to ensure that multiple concurrent transactions protect resources while referencing the same resource, while also preventing the mutual destruction of objects. 4. type of lock in Oracle

• Shared Lock (table lock) shared lock There are several modes
  • Row sharing mode: Do not run other user insert, delete, update operation, multiple users can simultaneously work on the table, can query. The syntax is as follows: Lock table XX in Share mode
  • Row sharing update mode: Allows multiple users to lock different rows in a table at the same time, and can execute DML statements, except for those rows that are locked.
• Exclusive lock (Row lock): Do not allow other users to insert, delete, modify the operation, can only query, while other users can not modify and lock the table
• Deadlock: The transaction waits for a resource, causing a deadlock.

5. Transaction ISOLATION LevelThe transaction isolation level defines the degree of isolation between transactions. The isolation level in 4 is defined in the SQL92 standard.

• Uncommitted read (READ UNCOMMITTED) fantasy reads, non-repeatable reads, and dirty reads are allowed.
• The read COMMITTED allows for a fantasy read, non-repeatable read, and no dirty reading allowed.
• Repeated reads (repeatable read) allow the illusion to read, not to allow non-repeatable reads and dirty reads.
• Serialization (serilizable) fantasy reads, non-repeatable reads, and dirty reads are not allowed.

By some phenomena, you can reflect the effect of setting the transaction isolation level:

• Fantasy Read (Phantom Read): Transaction T1 Reads a statement that specifies where condition, and returns the result set. At this point the transaction T2 inserts a new row of records that exactly satisfies the T1 's where condition. Then the T1 uses the same criteria to query again, the result set can see T2 inserted records, this new record is fantasy.
• Dirty read: Transaction T1 modified a piece of data, but not committed, the transaction T2 just read the modified data, at this time T1 the transaction rollback, the T2 read the data is dirty data, called dirty read.
• Non-repeatable reads: The transaction T1 reads a row of records, and the transaction T2 modifies the record that the T1 just read, and then T1 the query again, which is called non-repeatable reading, unlike the record that was read for the first time.

6. Transaction ISOLATION level for OracleOracle provides read committed and serializable in the SQL92 standard, as well as read-only for non-SQL92 standards.

• READ COMMITTED
  • This is the default transaction isolation level for Oracle.
  • Each statement in a transaction conforms to the statement-level read consistency.
  • No dirty reads are guaranteed, but non-repeatable reads and phantoms may occur.
• SERIALIZABLE
  • To put it simply, serializable is to make a transaction appear to be executed sequentially.
  • You can only see changes that were committed by other transactions before the start of this transaction, and changes made in this transaction (that is, changes made by other transactions after the start of the transaction, even if they were committed, this transaction is not visible)
  • Ensure that non-repeatable reads and Phantoms are not present.
  • The Serializable isolation level provides read consistency (transaction-level read consistency) provided by the read-only firm while allowing DML operations.
• Setting the isolation Level
  • Set isolation level for a transaction
    • SET TRANSACTION Isolation level READ COMMITTED;
    • SET TRANSACTION Isolation Level SERIALIZABLE;
    • SET TRANSACTION READ only;
  • to set the isolation level for a single session
    • ALTER SESSION SET TRANSACTION isolation level READ COMMITTED;
    • ALTER SESSION SET TRANSACTION isolation SERIALIZABLE;

7. Specific examples

•  
  • prepare sql 
  • SQL code   
    1. create table a (  
    2.  id int,  
    3.    name varchar2 (50) ,   
    4.    primary key (id )   
    5. );
  • Demo a T1 insert, T2 read Data
  li>
  •  t1 Commit the transaction, T2 can see the data
  • 
    
     
  • We see the effect of the transaction isolation level, and when the T1,T2 transaction executes, How does Oracle set the locks for these two transactions? Let's take a look at this operation
  • 
    
     
  • after T1 commits, T2 modifies table a successfully
  • 
    
     
• Demo 2 transactions, T1 Update table A's data, T2 delete data from table 2
  • The following will be submitted T1, T2 be executed
  • 
    
    
  • T1 Post-submission situation
  • 
    
    
• Demonstrating the isolation level of a transaction
  • T1 transaction Set isolation level to SERIALIZABLE,T2 as the default level
  • 
    
    
  • Some friends may ask, is not T2 caused by the submission, then we will T2 after the results are as follows
  • 
    
    
  • Let's see T3 's query results.
  • 
    
    
  • From the above results we can know that if the transaction sets the serialization isolation level, then during that transaction, other transactions will not be visible to the resource modifications referenced in that thing.
    
    
    Well, today's summary here, there will be a follow-up article

http://kingj.iteye.com/blog/1675011#bc2358875

Oracle Transactions (RPM)