Setting transaction attributes in ORACLE (TWO)

In ORACLE, set the transaction attribute (TWO) to set the transaction attribute (set the read/write attribute of the transaction, the read-only attribute of the transaction, and the isolation attribute of the transaction) is only a control mechanism for the current transaction. Common transaction setting attributes include: 1) set transaction read write; 2) set transaction read only; 3) set transaction isolation level [read commited | serializable] Note: the setting of the transaction attribute takes effect only for the current transaction. To set the attribute for the start transaction, you must set the attribute before executing the dml statement, that is, these statements must be the first statement. Before introducing various transaction settings, remember three concepts: Dirty read: when a transaction is queried, other transactions are also performing data operations on the same operation object, at this time, the data modified by another transaction but not committed will be read, which is dirty read. Non-repeated read: when a transaction reads data, the other transaction updates and modifies the object to be operated and submits the data. When this transaction queries the objects to be operated, it finds that the data has been modified. This is the case where repeated reads are not allowed. Phantom read: a transaction is querying data objects, and another transaction is also deleting and inserting this object. When this transaction is querying, it is found that the data object has been added to or deleted from the Meta Group. This is phantom reading. 1) set transaction read write; set the read/write attribute of the transaction. This is the default attribute setting of the database. It allows you to execute dml statements in the transaction to modify data. 2) set transaction read only; set the read-only attribute of the transaction. You can only perform the following operations for the current transaction, but cannot modify the data. How to end the transaction is a commit or rollback operation. The advantage of this setting is that the database can be frozen to a time point, and this transaction will not be affected by other transactions. That is to say, after the read-only attribute of the transaction is set, there will be no Phantom reads, repeated reads, or dirty reads in the skill service. Select (exclude for update) alter session alter system lock table set role eg:

          [sql] SQL> select * from emp;         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- --------- ---------- ---------- ----------        7369 SMITH      CLERK           7902 17-DEC-80        800                    20        7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30        7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30        7566 JONES      MANAGER         7839 02-APR-81       2975                    20        7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30        7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30        7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10        7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20        7839 KING       PRESIDENT            17-NOV-81       5000                    10        7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30        7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- --------- ---------- ---------- ----------        7900 JAMES      CLERK           7698 03-DEC-81        950                    30        7902 FORD       ANALYST         7566 03-DEC-81       3000                    20        7934 MILLER     CLERK           7782 23-JAN-82       1300                    10    14 rows selected.    SQL>   SQL>   SQL>   SQL>   SQL>   SQL>   SQL>   SQL>   SQL>   SQL> commit;    Commit complete.    SQL> set transaction read only;    Transaction set.    SQL> update emp set sal=8000 where empno=7934;  update emp set sal=8000 where empno=7934         *  ERROR at line 1:  ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction      SQL>   

 

3) set the isolation of the transaction. One is set transaction isolation level read commited, the data to be operated by this firm is all data that has been committed before the start of this transaction. DML operations can be performed during the transaction without dirty reads, however, there may be situations where repeated reads and Phantom reads are not allowed. It ensures the consistency of transaction statements.

[SQL] oracle @ oracle-R2: ~> Sqlplus "/as sysdba" SQL * Plus: Release 11.2.0.3.0 Production on Wed Apr 3 13:17:58 2013 Copyright (c) 1982,201 1, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn rhys/root Connected. SQL> set transaction isolation level read committed; Transactio N set. SQL> select * from emp; empno ename job mgr hiredate sal comm ---------- --------- ---------- ------------ ---------- DEPTNO ---------- 7499 allen salesman 7698 20-FEB-81 1600 300 7521 30 7698 ward salesman 1250 500 30 7566 jones manager 7839 Jun 2975 20 EMPNO ename job mgr hiredate sal comm ----------------------------------------------------------- --------- DEPTNO ---------- 7654 martin salesman 7698 28-SEP-81 1250 1400 30 7698 blake manager 7839 01-MAY-81 2850 30 7782 clark manager 7839 09-JUN-81 2450 10 empno ename job mgr hiredate sal comm --------------------------------------- --------- ---------- DEPTNO ---------- 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08 -SEP-81 1500 0 30 empno ename job mgr hiredate sal comm ---------- --------- ---------- ------------ ---------- DEPTNO ---------- 7876 adams clerk 7788 23-MAY-87 1100 20 7900 james clerk 7698 03-DEC-81 950 30 7902 ford analyst 7566 03-DEC-81 3000 20 empno ename job mgr hiredate sal comm ---------- --------- ---------- ------------ DEPTNO ---------- 7934 miller clerk 7782 23-JAN-82 2100 10 13 rows selected. SQL> modify one piece of data in another transaction and submit [SQL] SQL> update emp set sal = 1000 where empno = 7934; [SQL] SQL> COMMIT; the query result is as follows: [SQL] 1 * select * from emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- --------- ------------ ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FE B-81 1250 500 30 7566 jones manager 7839 02-APR-81 2975 20 7654 martin salesman 7698 28-SEP-81 1250 1400 30 7698 blake manager 7839 01-MAY-81 2850 30 7782 clark manager 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08-SEP-81 1500 0 30 7876 adams clerk 7788 23-MAY-87 1100 20 7900 james clerk 7698 03-DEC-81 950 30 EMP No ename job mgr hiredate sal comm deptno ---------- --------- ---------- ------------ ---------- 7902 ford analyst 7566 03-DEC-81 3000 20 20 7934 miller clerk 7782 23-JAN-82 1000 10 13 rows selected. SQL> in second, the transaction isolation is: set transaction isolation level serializable. This attribute ensures that all data before the dml operation of the transaction is committed. It controls the consistency of transactions in the transaction. After the local isolation is enabled, there will be no Phantom reads, dirty reads, and non-repeated reads in the event service. This attribute is used multiple times in applications that change data in applications and reports. Eg: [SQL] <span style = "font-size: 14px;"> SQL> set transaction isolation level serialiable; set transaction isolation level serialiable * ERROR at line 1: ORA-02179: valid options: isolation level {SERIALIZABLE | read committed} SQL> set transaction isolation level serializable; Transaction set. SQL> select * from emp; empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- ------------ ---------- 7499 allen salesman 7698 20-FEB-81 1600 300 7521 30 7698 ward salesman 1250 500 7566 7839 martin salesman 7698 28-SEP-81 1250 1400 30 7698 blake manager 7839 01-MAY-81 2850 30 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08-SEP-81 1500 0 30 7876 ADAMS Clerc 7788 23-MAY-87 1100 20 7900 JAMES Clerc 7698 03-DEC-81 950 30 7902 ford analyst 7566 Jun 3000 20 empno ename job mgr hiredate sal comm deptno ------------------------------------------------------------------------------ 7934 miller clerk 7782 23-JAN-82 1000 10 12 rows selected. execute the following operations in another window: SQL> select * from emp; empno ename job mgr hiredate sal comm deptno ---------- -------------- ----------- ---------- 7521 ward salesman 7698 Jun 1250 500 7566 jones manager 7839 2975 30 7654 blake manager 7839 01-MAY-81 2850 30 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08-SEP-81 1500 0 30 7876 adams clerk 7788 23-MAY-87 1100 20 7900 james clerk 7698 03-DEC-81 950 30 7902 ford analyst 7566 03-DEC-81 3000 20 7934 miller clerk 7782 23-JAN-82 1000 10 11 rows selected. SQL> update emp set sal = 8888 where empno = 7934; 1 row updated. SQL> commit; Commit complete. SQL> select * from emp; empno ename job mgr hiredate sal comm deptno ---------- -------------- ----------- ---------- 7521 ward salesman 7698 Jun 1250 500 7566 jones manager 7839 2975 30 7654 blake manager 7839 01-MAY-81 2850 30 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08-SEP-81 1500 0 30 7876 adams clerk 7788 23-MAY-87 1100 20 7900 james clerk 7698 03-DEC-81 950 30 7902 ford analyst 7566 03-DEC-81 3000 20 7934 miller clerk 7782 23-JAN-82 8888 10 11 rows selected. SQL> the query results in this transaction window are as follows: SQL> r 1 * select * from emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- --------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 limit 1250 500 30 7566 jones manager 7839 02-APR-81 2975 20 7654 martin salesman 7698 28-SEP-81 1250 1400 30 7698 blake manager 7839 01-MAY-81 2850 30 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08-SEP-81 1500 0 30 7876 adams clerk 7788 23-MAY-87 1100 20 7900 james clerk 7698 03-DEC-81 950 30 7902 ford analyst 7566 03-DEC-81 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- --------- ---------- ------------ 7934 miller clerk 7782 23-JAN-82 1000 10 12 rows selected.