oracle實驗記錄 （事務控制）

commit;
rollback;
savepoint;
rollback to savepoint;
set transaction;
set constraints;

set transaction read write(defalut)
set transaction read only不可以DML

SQL> set transaction name 'userxh';~~~~~~~事務命名

Transaction set.

SQL> insert into t1 values(1);

1 row created.
SQL> select name from v$transaction where name='userxh';

NAME
--------------------------------------------------------------------------------

userxh

SQL> set transaction read only;~~~~~~~~等上一個結束才可以設定新的
set transaction read only
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction

SQL> commit;~~~~~~~~~~~userxh事務以commit 結束(or rollback)

Commit complete.

SQL> set transaction  read only;~不可以DML

Transaction set.
SQL> insert into t1 values(1);
insert into t1 values(1)
*
ERROR at line 1:
ORA-01456: may not perform. insert/delete/update operation inside a READ ONLY
transaction
select ,alter system ,alter session可以
set transaction  read write(default)
SQL> commit;(提交後 下一個TRANSACTION 自動為read write)

Commit complete.

set transaction isolation level read commited(default) 提供一致讀
會出現不可重複讀,幻象讀(與第一讀資料不一樣,有新資料)
SQL> conn / as sysdba
Connected.

SQL> select * from xh.t1;第一次查

         A
----------
         1

SQL> select * from xh.t1; 第2次查

         A
----------
         2~~~~~~~~~~~不可重複讀
         3~~~~~~~~~~~~~~~~幻象讀
SQL> conn xh/a831115
Connected.
SQL> update t1 set a=2;

1 row updated.

SQL> insert into t1 values(3);

1 row created.

SQL> commit;

Commit complete.

set transaction isolation level  serializable
使用一個可序列化的事務
DML操作的資料是該事務 開始之前 已COMMIT的 ,並且改事務 看不到 別的事物對錶的更新 再次讀取時候肯定 與第一次一樣(如果自己沒改) 整個事務期間總可以返回相同的結果,

這樣在做報表時候有用
簡單點說就是 不允許 dirty read ,不允許 不可重複讀, 不允許幻想讀 ,是最高階別的隔離

SQL> conn xh/a831115
Connected.
SQL> select * from t1;

         A
----------
         2
         3

SQL> set transaction isolation level serializable;

Transaction set.
SQL> conn / as sysdba~~~~~~~~另一個session
Connected.

SQL> update xh.t1 set a=5 where a=2;

1 row updated.

SQL> commit;

Commit complete.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~xh session
SQL> select * from t1;~~~~~~~~~~~還是2,3 永遠讀取 設定這個事務之前的

         A
----------
         2
         3

ORA-08177: can't serialize access for this transaction(解釋一下這個)

SQL> set transaction isolation level serializable;

Transaction set.

SQL> show user
USER is "XH"

SQL> update t1 set a=888 where a=88;

1 row updated.

SQL> conn zz/a123
Connected.
SQL> show user
USER is "ZZ"
SQL> set transaction isolation level serializable;

Transaction set.

SQL> update xh.t1 set a=888 where a=88;  hange住了 這個是必須的 因為lock的原因

SQL> commit;xh

Commit complete.

SQL> update xh.t1 set a=888 where a=88;     zz
update xh.t1 set a=888 where a=88
          *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

SQL> update xh.t1 set a=888 where a=88;~~~~~~~XH 已commit ZZ再執行還是不行
update xh.t1 set a=888 where a=88
          *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

問題來的 ,若N多事務 又都更新了 同一行,那麼只有第一個執行的語句成功,其他都收到這個錯誤
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> set transaction isolation level serializable;

Transaction set.

SQL> show user
USER is "XH"

SQL> select * from t1;

         A
----------
         5
       888
SQL> update t1 set a=6  where a=5;

1 row updated.

SQL> commit;~~~~~~~馬上提交

Commit complete.
SQL> set transaction isolation level serializable;

Transaction set.

SQL> show user
USER is "ZZ"
SQL> select * from xh.t1;

         A
----------
         5
       888
SQL> update xh.t1 set a=6  where a=5; XH commit後才執行的
update xh.t1 set a=6  where a=5
          *
ERROR at line 1:
ORA-08177: can't serialize access for this transactio
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> conn xh/a831115~~~~~~~~~~~順序1
Connected.
SQL> update t1 set a=2;~~~順序4

15 rows updated.

SQL> commit;

Commit complete.
~~~~~~~~~~~~~~~~~~~~2個SESSION中
CONN ZZ/A123
SQL> set transaction isolation level serializable;~~順序2

Transaction set.

SQL> select count(*) from xh.t1;~~~~順序3(只查到該事務 開始之前 已COMMIT的)

  COUNT(*)
----------
        15

SQL> update xh.t1 set a=5;~~~~順序5
update xh.t1 set a=5
          *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

 

~~~~~~~~~~~DML只能修改 該事務開始前的已經COMMIT資料,oracle要判斷在此事務前,其它事務對這些資料行的修改已提交或未提交,判斷靠block中相關資訊(itl solts  能看出是

否提交過)
若想多儲存塊狀態資訊 就 alter table XX iNITRANS N
所以當一個serializable transaction修改資料時候 此資料被其它事務修改 commit過,那麼報錯

SYS使用者or sysdba connct join 不能有READ ONLY或SERIALIZABLE事務

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> set transaction isolation level serializable;
set transaction isolation level serializable
*
ERROR at line 1:
ORA-08178: illegal SERIALIZABLE clause specified for user INTERNAL

alter session set isolation_level=serializable;`~~or alter system  設定級別***************

set constraints設定延遲約束檢驗時機

語法:set constraints 約束名 ,all   immediate|deferred

SQL> alter table t1  add constraint t1c unique(a) deferrable ;

Table altered.

SQL> select * from t1;

         A
----------
         6
        66

SQL> insert into t1 values(66);
insert into t1 values(66)
*
ERROR at line 1:
ORA-00001: unique constraint (XH.T1C) violated

SQL> set  constraint t1c deferred;

Constraint set.

SQL> insert into t1 values(66);

1 row created.

SQL> commit;~~~~~~~~~~~~~~~~延遲到commit
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (XH.T1C) violated

SQL> set  constraint t1c immediate;

Constraint set.

SQL> insert into t1 values(66);
insert into t1 values(66)                立即
*
ERROR at line 1:
ORA-00001: unique constraint (XH.T1C) violated~~~~~~~~~~~~~~

 

SQL> alter table t1  add constraint t1c unique(a) deferrable initially deferred;~~,另外這樣也行

Table altered.

SQL> insert into t1 values(66);

1 row created.

SQL> commit
  2  ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (XH.T1C) violated

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-611009/，如需轉載，請註明出處，否則將追究法律責任。