更多精彩內容盡在

《Oracle 事務探索與例項》

一 資料庫版本

SYS@LEO1>select* from v$version;

BANNER

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

PL/SQLRelease 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS forLinux: Version 11.2.0.1.0 - Production

NLSRTLVersion 11.2.0.1.0 - Production

二 事務那些事兒

1.什麼是事務：事務是一組操作序列，這些操作要麼都完成，要麼都不完成，它是一個不可分割的操作單元。關係型資料庫最核心的價值體現。說白了為了完成一件事而做的n個步驟，這n個步驟是有前後順序的，必須按照A->B->C->D的邏輯順序來執行，事務是資料庫維護資料一致性的單位，例如多個會話同時讀取同一資料的問題。

關係型資料庫中，一個事務可以是一條SQL語句，一組SQL語句或整個程式。

場景：銀行轉賬結算購物訂票系統

非關係型資料庫中，一個事務可以是一個操作，但操作之間沒有關係，相互獨立的。

比如：推薦系統日誌分析網站搜尋

三 用例子說明事務的四個屬性和自動提交功能

事務的屬性ACID

原子性（Atomicity）：一個事務是一個不可分割的整體，事務中的操作要麼都完成，要麼都失敗。

例項

SYS@LEO1>conn leo1/leo1

Connected.

LEO1@LEO1>drop table leo1 purge; 清理環境

Table dropped.

LEO1@LEO1>create table leo1 (x number,ynumber); 建立表

Table created.

LEO1@LEO1>insert into leo1 values(1,1); 執行DML操作

1 row created.

LEO1@LEO1>update leo1 set x=2 where x=1;

1 row updated.

LEO1@LEO1>delete from leo1 where y=1;

1 row deleted.

LEO1@LEO1>commit;

Commit complete.

上面我們執行了三條sql語句，都屬於一個事務，當commit的時候表示事務完成，這三條sql全部生效，如果rollback這三條sql全部失敗，不存在有的完成有的失敗，因為它們是一個不可再分割的整體。

一致性（Consistency）：指資料庫的一種狀態的一致性，具體來說不可違反約束，不可違反規則，所謂的一致性就是一種人為規則，例如定義一個主鍵，插入2條一樣的資料就違反了一致性條件。

例項

LEO1@LEO1>drop table leo2 purge; 清理環境

Table dropped.

LEO1@LEO1>create table leo2 (x number,ynumber,constraint pk_leo2 primary key(x)); x列有主鍵

Table created.

LEO1@LEO1>insert into leo2 values(1,1);

1 row created.

LEO1@LEO1>insert into leo2 values(1,2); 當插入的值一樣時違反了一致性

insert into leo2 values(1,2)

*

ERROR at line 1:

ORA-00001: unique constraint (LEO1.PK_LEO2)violated

隔離性（isolation）：未提交的事務其他會話不可見。

例項

會話一

LEO1@LEO1>select * from leo2; 表裡只有1條記錄

X Y

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

1 1

LEO1@LEO1>insert into leo2 values(2,2); 新插入一條

1 row created.

LEO1@LEO1>select * from leo2; 此時表裡有2條記錄，但未有提交

X Y

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

1 1

2 2

會話二

[oracle@leonarding1 flashback_area]$sqlplus leo1/leo1 連線新會話

LEO1@LEO1>select * from leo2; 只能看到已提交事務的資料，未提交的事務看不到

X Y

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

1 1

永續性（Durability）：事務一旦提交就不可更改，永久有效。

例項

LEO1@LEO1>drop table leo3 purge;

Table dropped.

LEO1@LEO1>create table leo3 (x number,ynumber);

Table created.

LEO1@LEO1>insert into leo3 values(1,1);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from leo3;

X Y

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

1 1

LEO1@LEO1>rollback;

Rollback complete.

LEO1@LEO1>select * from leo3;

X Y

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

1 1

事務一旦commit，你再rollback也是無效的，已經插進去了。

事務是以第一個DML語句作為開始

以下面其中之一作為結束

(1) commit or rollback

(2) DDL or DCL

(3) 使用者session正常結束退出sqlplus

(4) 系統正常結束or終止

事務自動提交，我們可以設定oracle自動提交事務

例項

LEO1@LEO1>show autocommit; 預設是不啟動自動提交的

autocommit OFF

LEO1@LEO1>set autocommit on; 手工啟動

LEO1@LEO1>show autocommit;

autocommit IMMEDIATE

LEO1@LEO1>insert into leo3 values(2,2); 當插入資料的同時就提交

1 row created.

Commit complete.

LEO1@LEO1>select * from leo3;

X Y

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

1 1

2 2

LEO1@LEO1>rollback; 此時回滾已無效

Rollback complete.

LEO1@LEO1>select * from leo3;

X Y

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

1 1

2 2

LEO1@LEO1>set autocommit off; 我們不建議開啟事務自動提交功能

四在Oracle下演示Nonrepeatable Read 和PhantomRead的例子，給出SQL演示過程。

Non-repeatable Read不可重複讀：在一個事務中，同樣的資料被2次讀取，得到不同的結果集

例項

Leo幫tiger一起買2張飛機表去肯亞看大象

LEO1@LEO1>drop table leo6 purge;

Table dropped.

LEO1@LEO1>create table leo6 (namevarchar2(10),ticket_type varchar2(20),price number);

Table created.

LEO1@LEO1>insert into leo6values('leo','plane_ticket',100);

1 row created.

LEO1@LEO1>insert into leo6values('tiger','plane_ticket',100);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from leo6;

NAME TICKET_TYPE PRICE

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

leo plane_ticket 100

tiger plane_ticket 100

飛機票的價格都是100$，2個人一共200$

Leo查詢的價格

LEO1@LEO1>select sum(price) both_pricefrom leo6;

BOTH_PRICE

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

200

在過行李安檢的時候，發現tiger行李超重，原來tiger非常有愛心，給大象帶了花生+毛豆，根據規定超重行李是要補交託運費的。

機場又加了100元託運費

LEO1@LEO1>update leo6 set price=200where name='tiger';

1 row updated.

LEO1@LEO1>select * from leo6;

NAME TICKET_TYPE PRICE

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

leo plane_ticket 100

tiger plane_ticket 200

LEO1@LEO1>commit;

Commit complete.

Leo再次查詢價格時，總價變成300了

LEO1@LEO1>select sum(price) both_pricefrom leo6;

BOTH_PRICE

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

300

不可重複讀在資料庫中是可能出現的，所以要注意一下，上面leo和機場是兩個獨立會話

Phantom Read幻讀：在一個事務中，同樣的sql被2次執行，得到不同的結果集。

例項

翻山越嶺跋山涉水終於來到了廣闊的非洲大草原真是大象遍地走春風吹又生，tiger餚有興致的數起了大象

Tiger 查詢的結果

LEO1@LEO1>drop table leo7 purge;

Table dropped.

LEO1@LEO1>create table leo7 (namevarchar2(10),num number);

Table created.

LEO1@LEO1>insert into leo7values('a',1);

1 row created.

LEO1@LEO1>insert into leo7values('b',1);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select count(*) from leo7; 第一天發現了2只

COUNT(*)

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

2

傍晚有一隻小象跑了過來

LEO1@LEO1>insert into leo7values('c',1);

1 row created.

LEO1@LEO1>commit;

Commit complete.

第二天tiger又查詢了一遍，這次發現了3只

LEO1@LEO1>select count(*) from leo7;

COUNT(*)

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

3

小結：上述兩個生動的例子展現了Non-repeatableRead不可重複讀和Phantom Read幻讀的過程，大家一定要好好體會里面的含義，理解這兩種不同的資料讀取方式。

五在Oracle設計一個場景，會導致Non-repeatableRead，然後選擇一種事務隔離等級來避免它的發生，給出SQL演示過程。

場景：tiger在遊玩非洲大草原之後，選擇從海路回家，在走到索馬利亞海岸的時候，有幸被海盜光顧，請他上岸吃海鮮，這次海盜拿出了不勞而獲的敬業精神，贖金10000$，三天之內交換人質，一手交錢，一手交貨。由於歷來海盜的誠信記錄並不是很光彩照人，我們模擬了一下可能的突發事件。

首先海盜提出條件，贖金10000$，還為此特意做了一個排行榜

LEO1@LEO1>create table leo8 (namevarchar2(20),cost int); 排行榜清單

Table created.

LEO1@LEO1>insert into leo8values('tiger',10000); 插入tiger資訊

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from leo8 whererownum<=1; 我們看到tiger排在第一位

NAME COST

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

tiger 10000

上面的資訊是我們自己查詢的情況，突然海盜得知tiger是中國知名的IT專家，國家的財富和人才。本著生意人的精明頭腦，高價值的人當然換取的條件也水漲船高，海盜偷偷修改了贖金數，變成20000$

海盜

LEO1@LEO1>update leo8 set cost=20000where cost=10000;

1 row updated.

LEO1@LEO1>commit;

Commit complete.

當我們交錢的時候發現tiger升值了，哎呦我去~ 這不打劫嘛！

LEO1@LEO1>select * from leo8 whererownum<=1;

NAME COST

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

tiger 20000

為了防止這種事情發生，我們可以使用Oracle的Read-only事務隔離等級和Serializable事務隔離等級來避免這種事情發生，這兩種事務隔離等級非常相似，因此我們選擇較常用的Serializable來給大家演示。

Read-only事務隔離等級：只能看到事務開始時所有提交的改變，自身不允許DML操作。

Serializable事務隔離等級：只能看到事務開始時所有提交的改變和自身的改變。

當海盜告知我們贖金是10000$的時候，我們先開出了清單

LEO1@LEO1>drop table leo8 purge;

Table dropped.

LEO1@LEO1>create table leo8 (namevarchar2(20),cost int); tiger清單

Table created.

LEO1@LEO1>insert into leo8values('tiger',10000);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>set transaction isolationlevel serializable; 我們設定好了隔離等級

Transaction set.

LEO1@LEO1>select * from leo8 whererownum<=1; 沒錯是10000$

NAME COST

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

tiger 10000

海盜又故伎重演，想把價格提高到20000$，這是另一個會話

LEO1@LEO1>update leo8 set cost=20000where cost=10000; 已經更新

1 row updated.

LEO1@LEO1>commit;

Commit complete.

到了較贖金的時候，我們又查詢了一次

LEO1@LEO1>select * from leo8 whererownum<=1;

NAME COST

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

tiger 10000

good，完美封殺了海盜邪惡企圖，資料沒有變化，但並不是說資料沒有被修改，由於啟動事務隔離策略，其他事務所做的修改我們是看不到的。Serializable只對當前事務有效，不對另外事務有效。

小結：Oracle中所有事務隔離等級都是依賴undo實現的。什麼時候使用Serializable隔離等級好呢，當修改操作比較短的事務，事務中存在多條sql需要資料從事務開始時就保持一致，這樣的場景適合。

六 用示例比較Oracle的Read committed 和Serializable 事務隔離等級的區別，給出SQL演示過程。

Read committed讀已提交事務隔離等級：Oracle 預設隔離等級，支援不可重複讀和幻讀。這兩種資料讀寫方式在第四題中已經完全闡述了，這裡不在重複。

Serializable 事務隔離等級：只能看到事務開始時所有提交的改變和自身的改變，不支援不可重複讀和幻讀，事務開始是什麼樣子，結果就是什麼樣子，其他使用者影響不了這個事務。

會話A

LEO1@LEO1>create table leo9(x int,yint); 建立表

Table created.

LEO1@LEO1>insert into leo9values(100,100); 插入一行

1 row created.

LEO1@LEO1>commit; 提交

Commit complete.

LEO1@LEO1>set transaction isolationlevel serializable; 啟動Serializable 事務隔離等級

Transaction set.

LEO1@LEO1>select * from leo9;

X Y

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

100 100

會話B，我們用另一個會話不管怎麼修改leo9表，都不會影響會話A事務

LEO1@LEO1>insert into leo9values(200,200);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>insert into leo9values(300,300);

1 row created.

LEO1@LEO1>commit;

Commit complete.

會話A，還是一條資料

LEO1@LEO1>select * from leo9;

X Y

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

100 100

會話A自己修改的內容在事務中是生效的

LEO1@LEO1>insert into leo9values(400,400); 我們插入一條記錄

1 row created.

LEO1@LEO1>select * from leo9; 在本事務中是可以看到的

X Y

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

400 400

100 100

LEO1@LEO1>commit; 一旦事務結束，Serializable 事務隔離等級就會失效

Commit complete.

會話A，又可以看到所有的記錄了

LEO1@LEO1>select * from leo9;

X Y

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

400 400

100 100

200 200

300 300

兩種事務隔離等級的比較

Read committed V.S Serializable Transactions

支援SQL92標準支援SQL92標準

讀取物化檢視維護語句級一致性維護事務級一致性

事務一致性支援語句級支援事務級

行級鎖支援支援

讀鎖定寫不支援不支援

寫鎖定讀不支援不支援

非行級鎖不支援不支援

行級鎖支援支援

事務鎖等待支援支援

小結：Serializable事務隔離等級作用域是一個事務，超出這個事務就無效了。

事務屬性 Phantom Read Non-repeatableRead Read committed Serializable

2013.5.24
北京&spring
分享技術~成就夢想
Blog：

【原創】 Oracle 事務探索與例項(一)

相關文章