oracle事物總結(轉)

標籤：

關於Oracle事務的總結

1.什麼是事務，事務的特性是什嗎？

事務的任務便是使資料庫從一種狀態變換成為另一種狀態，這不同於檔案系統，它是資料庫所特用的。它的特性有四個：TOM總結為ACID即
原子性atomicity:語句級原子性，過程級原子性，事務級原子性
一致性consistency:狀態一致，同一事務中不會有兩種狀態
隔離性isolation:事務間是互相分離的互不影響(這裡可能也有自治事務)
持久性durability:事務提交了，那麼狀態就是永久的

對於語句級原子性，過程級原子性和事務級原子性可以查閱一下相關的資訊

 

2.Oracle中的事務語句
commit=commit work 提交
rollback=rollback work 復原
savepoint 事務的標記點，可以使一個事務在復原到不同的階段
set transaction  開始一個事務
rollback to savepoint 與savepoint對應
另外對於自治事務還有一個,下面會著重說一下關於自治事務
pragma autonomous_transaction 

3.關於完整性條件約束與事務的關係
完整性條件約束的模式有immediate,deferred等
文法：set constraint c_fk defereed
這對於串聯更新很有協助，如下面的tom在書中舉的例子：
SQL> create table p(pk int primary key);
表已建立。
SQL> create table c
  2  (fk constraint c_fk
  3  references p(pk)
  4  deferrable
  5  initially immediate
  6  )
  7  /
表已建立。
語句: set  constraint c_fk immediate;
      set  constraint c_fk deferred;
SQL> set constraint c_fk immediate;
約束條件已設定。
SQL> update p set pk=3;
update p set pk=3
*
ERROR 位於第 1 行:
ORA-02292: integrity constraint (FTITEM.C_FK) violated - child record found

SQL> set constraint c_fk deferred;
約束條件已設定。
SQL> update p set pk=3;
已更新 1 行。
SQL> update c set fk=3;
已更新 1 行。
SQL> commit;
提交完成。
SQL> set constraint c_fk immediate;
約束條件已設定。

4.在事務中兩個不好的方法
tom在書上提到了兩種不好的事務使用習慣，我在工作中也是經常犯的，主要是因為對於每種資料庫的認識不到位，聽好多朋友說資料庫你只要會用了一個其它的就可以了，經過這段時間的學習，其實我們所說的會只是說對一SQL語句等，而並不是理解，比如對於暫存資料表的用法，在sqlserver與oracle就不太一樣（我只用過這兩個資料庫），兩個不好的方法：
A：在迴圈中提交事務，這影響效能而且在快照(snapsot中也會有問題),還有一個是重新啟動(在before update on table的觸發器中會看到引用NEW,OLD會被觸發兩次)
B：使用自動認可事務，一定要手動控制事務的提交，因為自動認可會出現不必要的麻煩。

5.分散式交易
在oracle中會在一個事務中控制多個資料庫，保證各個資料庫中的資料完整性，主要通過dblink，看到這我想到了自己在工作中的問題：兩台伺服器不同的資料庫，我一直認為不能同時用一個事務來控制，所以在開發程式中（我用delphi開發的）我用兩個connection來進行聯結不同的資料庫，提交時分別提交，而且需要用狀態標識來進行事務是否正常，之前用sqlserver時也這樣操作，現在想想笨的要死，為什麼沒有用到事務的特性呢？究其原因是，自己對於資料庫的理解差到極點了：（
在oracle中的分散式交易的限制條件：
(1)只能在主伺服器中進行事務的開始，提交，復原等，其它伺服器會根據狀態來進行判斷，即主伺服器為協調各個資料庫的狀態一致從而使其它從資料庫達到狀態一致。(應該說是網站，分網站)
(2)在dblink(資料連結)上不能做提交
(3)在dblink(資料連結)上不能做DDL操作
(4)在dblink(資料連結)不能發出savepoint等操作,即不能發出任何事務性語句

這裡補充一下關於資料庫連結的建立刪除等文法：
建立資料連結：
方法1.create database link dblink_name connect to user_name identified by      
           password using ‘server name‘;
方法2.create database link dblink_name  connect to user_name identified by    
           password
           using ‘(DESCRIPTION =
                          (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
                           )
                          (CONNECT_DATA =
                               (SERVICE_NAME = orcl)
                          )
                        )‘;
刪除資料連結：
drop database link dblink_name;
使用資料連結：
select * from [email protected]_name;

6.自製事務

 幾天前在pub論壇中看到一個問題是在表A上建了一個觸發呂，當對錶進行相關操作時不論成功或失敗,都想通過觸發器提交一些資訊，（描述的有點不清，見諒)，當時有人說用自治事務，我還尋思什麼是自治事務呢？
自治事務：是獨立於主事務的一個子事務，它的提交與復原不影響主事務的操作（我的理解）
自治事務提供了一種用PL/SQL控制事務的新方法，可以用於：
1 頂層匿名塊
2 本地，獨立或打包的函數和過程
3 對像類型的方法
4 資料庫觸發器

自治事務預存程序：
SQL> create or replace procedure autonomous_insert
  2  as
  3  pragma autonomous_transaction;
  4  begin
  5  insert into t values(‘autonomous insert‘);
  6  commit;
  7  end;
  8  /

過程已建立。

pragma是一個編譯器指令，這是一種編輯器執行某種編譯選項的方法。

非自法事務預存程序：
SQL> create or replace procedure nonautonomous_insert
  2  as
  3  begin
  4    insert into t values(‘nonautonomous insert‘);
  5    commit;
  6  end;
  7  /

過程已建立。

SQL> begin
  2    insert into t values(‘anonymous block‘);
  3    nonautonomous_insert;
  4    rollback;
  5  end;
  6  /

PL/SQL 過程已成功完成。

SQL> select * from t;

MSG
-------------------------
anonymous block
nonautonomous insert

因為在nonautonomous_insert中有一個commit,所以rollback基本沒有可復原的操作。

SQL> begin
  2    insert into t values(‘anonymous block‘);
  3    autonomous_insert;
  4    rollback;
  5  end;
  6  /

PL/SQL 過程已成功完成。

SQL> select * from t;

MSG
-------------------------
autonomous insert

這是因為autonomous_insert是一個自治事務，獨立於匿名塊的事務，所以rollback不會影響到它。

如何使用一個自治事務來記錄表修改的資訊：建立五個audit表來記錄資訊
SQL> create table audit_tab
  2  (username varchar2(30) default user,
  3  timestamp date default sysdate,
  4  msg varchar2(4000)
  5  )
  6  /

表已建立。
在表emp中建立觸發器（這就可以實現最初的那個問題）
create or replace trigger emp_audit
before update on emp
for each row
declare
  pragma autonomous_transaction;
  l_cnt number;
begin
  select count(*) into l_cnt from dual
  where exists (select null from emp
                where empno=:new.empno
                start with mgr=(select empno
                                  from emp
                                 where ename=user)
                connect by prior empno=mgr);
  if (l_cnt=0)
  then
    insert into audit_tab(msg)
    values(‘attemp to update ‘||:new.empno);
    commit;
  raise_application_error(-20001,‘access denied‘);
  end if;
end;


總結：
1.事務應該儘可能的短，即避免不必要的擴大事務
2.根據需要事務足夠大
3.決定事務大小的關鍵是資料完整性。
4.能決定事務大小的唯一約束就是控制系統的商務規則，不是undo,不是鎖等。

oracle事物總結(轉)