[Oracle] transaction isolation level (implementation of Oracle)

[Oracle] transaction isolation level (implementation of Oracle) 1. Read committed (Default) the lowest isolation level of Oracle is Read committed, which has the following features: this is the default transaction isolation level of ORACLE. Each statement in a transaction follows the statement-level read consistency. It is guaranteed that no dirty reads are performed, but non-repeated reads and phantom may occur. Some may wonder why the lowest isolation level in Oracle is not Read uncommitted? Because Oracle has undo, it is born to be read and write not blocked, so there will be no dirty read in Oracle. Here, we will explain what is "Statement-level read consistency", which means that when a statement starts to be executed, it can see the impact on data before the transaction, you can also see the impact of committed transactions on data when the statement starts to be executed. See the following example: 1) Clear the data in the test table: SQL> delete from test; 1 row deleted. SQL> commit; Commit complete. 2) set the isolation level of session1 to Read committed: Session1> alter session set isolation_level = read committed; Session altered. 3) insert a piece of data in session1 (not submitted): Session1> insert into test values (1); 1 row created.4) query in session1: SQL> select * from test; ID ---------- 1 You can see the data inserted before this transaction in session1. 5) insert a piece of data in session2 and submit: Session2> insert into test values (2); 1 row created. session2> commit; Commit complete.6) query in session1: Session1> select * from test; ID ---------- 2 1 You can see the submitted data in session2 in session1. At this isolation level, you can view the data operation results of other transactions as long as other transactions are committed (other transactions start after the current transaction, therefore, it cannot block non-repeated and Phantom reads. 2. The next level of SerializableOracle is not Repeatable Read, but Serializable. It also has the following features: Simply put, serializable is to make the transaction appear to be executed in sequence one by one. Only the changes committed by other transactions before the start of the transaction and the changes made in the Event Service (transaction-level consistency) can be seen ). Ensure that no non-repeated read and Phantom will appear. The Serializable isolation level provides read consistency (transaction-level read consistency) provided by read-only transactions, and allows DML operations. The so-called "transaction-level consistency" refers to the isolation level through which the query results are determined at the start of the transaction. After the transaction is started, other transactions change the data, the query for this transaction has no effect. See the following example: 1) Clear the data in the test table: SQL> delete from test; 2 rows deleted. SQL> commit; Commit complete. 2) set the isolation level of session1 to Serializable: Session1> alter session set isolation_level = serializable; Session altered.3) insert data in session1 (not submitted ): session1> insert into test values (1); 1 row created.4) query in session1: Session1> select * from test; ID ---------- 1 You can see the data inserted before this transaction in session1. 5) insert a piece of data in session2 and submit: Session2> insert into test values (2); 1 row created. SQL> commit; Commit complete.6) query in session1: Session1> select * from test; ID ---------- 1 you cannot see the data inserted in session2 in session1. 3. Read Only follows the transaction-level Read consistency and can Only see the changes committed by other transactions before the start of the transaction. DML operations cannot be performed in the competent service. Read only is a subset of serializable. They both avoid non-repeated reading and phantom. The difference is that read-only is used, while DML can be used in serializable. Read only is similar to Serializable. The only difference is that it does not allow DML operations in the competent service. See the following example: 1) Clear the test table data: SQL> delete from test; 1 row deleted. SQL> commit; Commit complete. 2) SET the isolation level of session1 to Read Only: Session1> set transaction read only; Transaction SET. 3) Try to insert data in session1: Session1> insert into test values (1); insert into test values (1) * ERROR at line 1: ORA-01456: may not perform. insert/delete/update operation inside a READ ONLYtransac An error occurred while inserting the data. This level is rarely used.