【開發篇plsql】plsql事務處理

4,事務處理

Oracle是基於事務的，oracle以使用者事務來確保資料的完整性。一個事務就是將一系列的資料操縱的sql語句作為一個邏輯單元，邏輯單元裡面的單個操作要麼全做，要麼全部不做，以保證資料的完整性。

事務結束時將產生資料庫的更改，oracle使用undo來記錄所有的更改，如果程式在事務未完成之前發生了錯誤，oracle將會檢測到這個錯誤，並回滾當前的事務，撤銷從事務開始時的更改。儲存點（savepoint）標記了當前事務處理的一個狀態點，結合使用rollback，savepoint可以撤銷部分事務，使用set transaction可以設定事務的屬性，比如讀寫訪問和隔離級別。

事務隔離級別

SQL92定義了四種事務隔離級別(transaction isolation level)，主要是為了在併發事務執行時阻止下列現象發生：

髒讀：事務讀取了被其他事務寫入但未提交的資料。

不可重複讀：一個事務再次讀取其之前曾經讀取過的資料時，發現資料已被其他已提交的事務修改或刪除。

幻象讀（phantom read）：事務按照之前的條件重新查詢時，返回的結果集中包含其他已提交事務插入的滿足條件的新資料。

提供的四種隔離級別分別不同程度的對這三種現象做了限制：

隔離級別	髒讀	不可重複讀	幻象讀
未提交讀取 (read uncommitted)	允許	允許	允許
已提交讀取 (read committed)	不允許	允許	允許
可重複讀取 (repeatable read)	不允許	不允許	允許
序列化 (serializable)	不允許	不允許	不允許

Oracle支援三種事務隔離級別，分別是

已提交讀取，序列化，只讀(read only)，預設的事務隔離級別是已提交讀取。

Oracle事務隔離級別	髒讀	不可重複讀	幻象讀	事務內使用 DML運算元據
Read committed 已提交讀取	不允許	允許	允許	允許
Serializable 序列化	不允許	不允許	不允許	允許
Read only 只讀	不允許	不允許	不允許	不允許

Oracle允許在事務開始時使用語句來設定事務的級別，以下三個語句分別設定oracle的三種事務隔離級別：

set transaction isolation level read committed;

set transaction isolation level serializable;

set transaction read only;

Plsql事務處理的語句有下列一些：

Commit, rollback, savepoint, rollback to savepoint, set transaction ,lock table 等。

例如：

Set transaction read only

這個語句建立一個事務級的讀一致性，事務內所有的查詢所見的都是事務開始之前的改變，只讀事務對於表有修改的許多查詢報表來說比較有用。這個語句不適用於SYS使用者，意味著，即使SYS使用者設定事務為只讀，查詢也會返回事務期間的更改。

測試：

Scott使用者下：

Session1：

SQL> set transaction read only;

Transaction set

SQL> select sum(sal) from scott.emp ;

SUM(SAL)

----------

28124.2

Session2：

SQL> update scott.emp a set a.sal = a.sal + 1.1;

14 rows updated

SQL> commit;

Commit complete

Session1：

SQL> select sum(sal) from scott.emp ;

SUM(SAL)

----------

28124.2

SQL> commit;

Commit complete

SQL> select sum(sal) from scott.emp ;

SUM(SAL)

----------

28139.6

很容易看出set transaction read only的效果，如果實在sys下面，情況則是這樣的：

Session1：

SQL> set transaction read only;

Transaction set

SQL> select sum(sal) from scott.emp ;

SUM(SAL)

----------

28139.6

Session2：

SQL> update scott.emp a set a.sal = a.sal + 1.1;

14 rows updated

SQL> commit;

Commit complete

SQL>

Session1：

SQL> select sum(sal) from scott.emp ;

SUM(SAL)

----------

28155

在plsql中使用set transaction read only需要在事務開始之前提交或者回滾，結束後也一般需要提交或者回滾例如：

declare

v_sum_sal scott.emp.sal%type;

begin

rollback work;

set transaction read only;

select sum(sal) into v_sum_sal from scott.emp;

dbms_output.put_line(v_sum_sal);

commit;

end;

/

事實上，這是由於使用set transaction read only以後，就會有一個事務產生：

SQL> set transaction read only;

Transaction set

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC from v$transaction a;

XIDUSN UBAREC UBABLK USED_UBLK USED_UREC

---------- ---------- ---------- ---------- ----------

2 0 0 1 1

SQL> commit;

Commit complete

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC from v$transaction a;

XIDUSN UBAREC UBABLK USED_UBLK USED_UREC

---------- ---------- ---------- ---------- ----------

Set transaction read write

再來看看：

Savepoint ,rollback to savepoint,commit

下面這段程式碼很容易看出這幾個語句的使用方式和效果

SQL> set serveroutput on;

SQL>

SQL> declare

2 v_sal scott.emp.sal%type;

3

4 begin

5 savepoint sp_1;

6 update scott.emp a set a.sal = a.sal + 1 where a.empno = 7369;

7 select sal into v_sal from scott.emp a where a.empno = 7369;

8 dbms_output.put_line(v_sal);

9 savepoint sp_2;

10 update scott.emp a set a.sal = a.sal + 1 where a.empno = 7369;

11 rollback to sp_2;

12 commit;

13 select sal into v_sal from scott.emp a where a.empno = 7369;

14 dbms_output.put_line(v_sal);

15 end;

16 /

810.6

PL/SQL procedure successfully completed

再看看lock table的幾種方式:

Lock table in

ROW SHARE

ROW EXCLUSIVE

SHARE UPDATE

SHARE

SHARE ROW EXCLUSIVE

EXCLUSIVE

[nowait]

可以簡單看看各種型別鎖定的情況，結合nowait引數來看看lmode和ctime的變化。

示例share mode和row exclusive mode:

SQL> lock table scott.emp in share mode nowait;

Table(s) locked

SQL> select sid from v$mystat a where rownum = 1;

SID

----------

150

SQL> select * from v$lock a where a.SID = 150;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

41DC66F4 41DC670C 150 TM 51564 0 4 0 45 0

41E10938 41E1095C 150 TX 458775 3256 6 0 45 0

SQL> rollback;

Rollback complete

SQL> lock table scott.emp in row exclusive mode nowait;

Table(s) locked

SQL> select * from v$lock a where a.SID = 150;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

41DC66F4 41DC670C 150 TM 51564 0 3 0 6 0

41E10938 41E1095C 150 TX 65558 3276 6 0 6 0

SQL> commit;

Commit complete

自治事務

看看在oracle中一個事務呼叫另外一個事務的情況：

事務A：

Begin

DML;

事務B

End;

事務B：

Begin

Commit;

End;

事務A對事務B中的操作可見，如果在事務B充commit，會將事務A中的DML也commit掉，自治事務就是來解決這樣的問題的，使得事務B中的提交或回滾對A不可見。

使用一個例子來對比一下自治事務和普通的事務的不同：

SQL> create or replace procedure pro_test_tran is

2 begin

3 commit;

4 end;

5 /

Procedure created

使用pragma autonomous_transaction;來標識使用自治事務

SQL>

SQL> create or replace procedure pro_test_auto is

2 pragma autonomous_transaction;

3 begin

4 commit;

5 end;

6 /

Procedure created

SQL> select a.sal from scott.emp a where a.empno = 7369;

SAL

------------

812.60

SQL> begin

2 update scott.emp a set a.sal = a.sal + .01 where a.empno = 7369;

3 pro_test_auto;

4 rollback;

5 end;

6 /

PL/SQL procedure successfully completed

呼叫了pro_test_auto以後並沒有將父事務中的dml提交掉：

SQL> select a.sal from scott.emp a where a.empno = 7369;

SAL

------------

812.60

SQL>

SQL> begin

2 update scott.emp a set a.sal = a.sal + .01 where a.empno = 7369;

3 pro_test_tran;

4 rollback;

5 end;

6 /

PL/SQL procedure successfully completed

呼叫了pro_test_ tran將父事務中的dml提交掉了：

SQL> select a.sal from scott.emp a where a.empno = 7369;

SAL

------------

812.61

SQL>

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

【開發篇plsql】plsql事務處理

相關文章