oracle set transaction read only與dbms_transaction實現事務transaction控制

SQL> show user
User is "SYS"
 
SQL> set transaction read only;
 
Transaction set
 
SQL> insert into t_table values(3);
 
1 row inserted
 
SQL> commit;
 
Commit complete

---sys使用者 set transaction read only不生效
SQL> select * from t_table;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
 
SQL>
SQL>
SQL> ---換另一個普通使用者
SQL> conn
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as
 
SQL> create table t_transaction(a int);
 
Table created
 
SQL> set transaction read only;
 
Transaction set
 
SQL> insert into t_table values(3);
 
insert into t_table values(3)
 
ORA-00942: table or view does not exist

---僅在非sys使用者 set transaction read only才生效 
SQL> insert into t_transaction values(3);
 
insert into t_transaction values(3)
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction
 
SQL> delete from t_transaction;
 
delete from t_transaction
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction
 
SQL> update   t_transaction set a=3;
 
update   t_transaction set a=3
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction

--truncate為ddl語句不受set transaction read only影響
SQL> truncate table t_transaction;
 
Table truncated

 

SQL> show user
User is "tbl_bck"
---也可以用dbms_transaction包實現上述的只讀事務功能
SQL> exec dbms_transaction.read_only;
 
PL/SQL procedure successfully completed
 
SQL> insert into t_transaction values(8);
 
insert into t_transaction values(8)
 
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY transaction

--dbms_transaction.read_only|write只能用於事務開始而非結束
SQL> exec dbms_transaction.read_write;
 
begin dbms_transaction.read_write; end;
 
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 60
ORA-06512: at line 2
 
SQL>
SQL>
SQL> set transaction read write;
 
set transaction read write
 
ORA-01453: SET TRANSACTION must be first statement of transaction

---必須用dbms_transaction.commit或rollback方可結束一個只讀事務
SQL> exec dbms_transaction.commit;
 
PL/SQL procedure successfully completed
 
SQL> insert into t_transaction values(8);
 
1 row inserted
 
SQL> commit;
 
Commit complete

---dbms_transaction包另一個功能可以直接內嵌於儲存過程中,進行針對性的控制
SQL> create or replace procedure proc_transaction
  2  as
  3  v_a pls_integer;
  4  begin
  5  dbms_transaction.read_only;
  6  select count(1) into v_a from dual;
  7  dbms_transaction.commit;
  8  insert into t_transaction values(9);
  9  commit;
 10  end;
 11  /
 
Procedure created
 
SQL> exec proc_transaction;
 
PL/SQL procedure successfully completed
 
SQL> select * from t_transaction;
 
                                      A
---------------------------------------
                                      8
                                      9