資料庫事務與隔離級別示例（oracle與sql server對比）

l  本示例文件演示SQL SERVER，ORACLE下不同事務隔離級別的區別，以及兩種資料庫本身的特點

l  為了模擬併發環境，SQL SERVER在SMO程式中開啟兩個查詢視窗即可。oracle可以用兩個sql *plus程式連線到相同資料庫來模擬

l  SQL SERVER、ORACLE中兩個併發使用者用事務1，事務2簡稱。

l  所有測試例子，都以最初測試表指令碼執行後狀態為基準。

l  在下列例子中，set transaction isolation level語句會改變會話的隔離級別，直到會話結束。故測試完畢需要改回預設級別。

l  最後，但並不是最不重要。以下的演示和相關解釋，都是基於易於理解的原則來的，實際的情況可能更復雜，但對開發人員來說，理解如此程度的簡化模型已經足夠了。

 

測試表指令碼：

SQL SERVER

CREATE TABLE [Customer](

    [CustID] [int] NOT NULL,

    [Fname] [nvarchar](20),

    [Lname] [nvarchar](20),

    [Address] [nvarchar](50),

    [City] [nvarchar](20),

    [State] [nchar](2) DEFAULT ('CA'),

    [Zip] [nchar](5) NOT NULL,

    [Phone] [nchar](10)

)

insert into customer values(1, 'Gary', 'Mckee', '111 Main', 'Palm Springs', 'CA', 94312, 7605551212)

insert into customer values(2, 'Tom', 'Smith', '609 Geogia', 'Fresno' 'JP', 33045, 5105551212)

insert into customer values(3, 'Jams', 'bond', 'ST Geogie 21', 'Washington', 'NY', 20331, 4405551864)

 

ORACLE

CREATE TABLE Customer(

    CustID int NOT NULL,

    Fname nvarchar2(20),

    Lname nvarchar2(20),

    Address nvarchar2(50),

    City nvarchar2(20),

    State nchar(2) DEFAULT 'CA',

    Zip nchar(5) NOT NULL,

    Phone nchar(10)

);

insert into customer values(1, 'Gary', 'Mckee', '111 Main', 'Palm Springs', 'CA', 94312, 7605551212);

insert into customer values(2, 'Tom', 'Smith', '609 Geogia', 'Fresno', 'JP', 33045, 5105551212);

insert into customer values(3, 'Jams', 'bond', 'ST Geogie 21', 'Washington', 'NY', 20331, 4405551864);

 

1。Sqlserver與oracle單條語句處理對比

SQL SERVER單條語句預設自動提交，即單條語句自動作為一個事務處理；而oracle的原則是儘量延後提交，除非遇到顯式提交命令或者DDL語句。

SQL SERVER

開啟事務1:

執行：select * from customer

可以看到表有3條記錄

執行：insert into customer values(4, 'Hello', 'world', 'paradise road 01', 'heaven', 'XY', 00001, 1234564321)

轉到事務2：

執行：select * from customer

可以看到事務1中剛插入的custid為4的記錄。

 

ORACLE

開啟事務1，執行：

select * from customer;

可以看到表有3條記錄，執行：

insert into customer values(4, 'Hello', 'world', 'paradise road 01', 'heaven', 'XY', 00001, 1234564321);

轉到事務2，執行：

select * from customer;

能看到的還是3條記錄，事務1中剛插入的一條記錄未自動提交，看不到。

轉到事務1，執行：

commit;

轉到事務2，執行：

select * from customer;

現在能看到4條記錄了。

 

 

2. 丟失更新

Sqlserver完全相容ANSI 92標準定義的4個隔離級別。它的預設隔離級別是提交讀（read committed），在該級別下，可能會有丟失更新的問題。Oracle的預設情形也一樣。故不再重複。

SQL SERVER

開啟事務1執行：

set transaction isolation level read committed

begin tran

select * from customer       --看到3條記錄

現在切換到事務2，此時事務1還未結束。在事務2中執行：

set transaction isolation level read committed

begin tran

select * from customer       --看到3條記錄，和事務1中相同

現在假設事務1事務繼續執行，修改資料並提交：

update customer set state = 'TK' where CustID = 3

commit

回到事務2，事務2根據先前查詢到的結果修改資料：

update customer set Zip = 99999 where state = 'NY'

commit

結果因為事務1已經修改了事務2的where條件資料，事務2未成功修改資料（其實準確的說應該算是幻象讀引起的更新失敗。不過若滿足條件的記錄數多的話，事務2的update可能更新比預期的數量少的記錄數，也可算“丟失”了部分本應完成的更新。個人認為只要明白實際上發生了什麼即可，不必過分追究字眼）。丟失更新還可能有別的情形，比如事務2也是

update customer set state = 'KO' where CustID = 3

兩個事務都結束後，事務2的結果反映到資料庫中，但事務1的更新丟失了，事務2也不知道自己覆蓋了事務1的更新。

 

 

3．髒讀演示

sqlserver的預設隔離級別是提交讀（read committed），當手工將其改為未提交讀時，事務可以讀取其它事務沒提交的資料；oracle由於自身特殊實現機制，可以理解為自身基礎的隔離級別就是可重複讀（與ANSI標準還是有區別的，後面例子會說明）。

SQL SERVER

開啟事務1，執行：

begin tran

select * from customer

    update customer set state = 'TN' where CustID = 3

轉到事務2，執行：

set transaction isolation level read uncommitted

begin tran

select * from customer

此時看到的資料是事務1已經更新但還未提交的（3號記錄state值TN）。而如果事務1發覺資料處理有誤，轉到事務1，進行回滾：

    Rollback

此時事務2如根據剛讀取的資料進一步處理，會造成錯誤。它讀取的資料並未更新到資料庫，是“髒”的。

 

ORACLE

ANSI定義未提交讀（read uncommitted）級別本意不是為了故意引入錯誤，而是提供一種可能的最大併發程度級別，即一個事務的資料更新不影響其它事務的讀取。Oracle從核心層面實現了更新資料不阻塞讀。可以說它提供未提交讀級別的相容，但沒有髒讀問題。（詳情參考對應PPT文件）故oracle沒有手工設定read uncommitted級別的語句。

 

 

4．不可重複讀

Sql server的預設級別沒有髒讀問題，但存在不可重複讀問題。Oracle預設級別也是提交讀，不過它因為自身特殊機制，在語句一級不存在不可重複讀問題。也就是說當執行時間較長的查詢時，查詢結果是與查詢開始時刻一致的（即使查詢過程中其它事務修改了要查詢的資料），而SQL SERVER就存在問題（sql server 2005新特性提供了可選的語句一級一致性支援，叫做行版本機制，實際上可以說是照著oracle的多版本來的，大體原理差不多）。

由於語句一級的事務一致性難以演示，下面例子是事務一級，提交讀隔離級別下發生的不可重複讀現象：

SQL SERVER

開啟事務1，執行：

set transaction isolation level read committed

begin tran

select * from customer where State = 'CA'

可以得到1條記錄，這個時候事務2中執行：

set transaction isolation level read committed

begin tran

update Customer set state = 'JP' where state = 'CA'

commit

事務2插入一條記錄並提交。回到事務1，事務1繼續執行，此時它再次相同的查詢，並藉此作進一步修改，卻發現讀取到的資料發生了變化。

select * from customer where State = 'CA'

--2次讀取不一致，之後的資料處理應該取消。否則不正確

update Customer set city = 'garden' where state = 'CA'

commit

讀取未能獲得記錄。也就是說在同一事務中兩次相同的查詢獲得了不同的結果，產生讀取不可重複現象。

 

ORACLE

儘管oracle在預設隔離級別下提供了語句級的事務讀一致性，但在事務級仍然是會出現不可重複讀現象。和sql server一樣，故不再重複。

 

 

5．幻像讀

當sqlserver的隔離級別設定為可重複讀（repeatable read），可以解決上面例子出現的問題。其內部是通過事務期間保持讀鎖來實現的。

SQL SERVER

開始事務1，修改事務級別為可重複讀，執行：

set transaction isolation level repeatable read

begin tran

select * from customer where State = 'CA'

和上例一樣得到1條記錄，這個時候事務2中執行：

set transaction isolation level repeatable read

begin tran

update Customer set state = 'JP' where state = 'CA'

commit

會發現事務2一直等待，並不結束。返回事務1，執行：

select * from customer where State = 'CA'       --2次讀取結果一致

update Customer set city = 'garden' where state = 'CA'

commit

事務2成功結束後，再返回事務1，發現事務1也完成了。通過鎖機制阻塞其它事務的修改，保持了事務期間讀取的一致性。然而，如果是插入資料，則還是會出現問題：

開始事務1，修改事務級別為可重複讀，執行：

set transaction isolation level repeatable read

begin tran

select * from customer where State = 'CA'

得到1條記錄，這個時候事務2中執行：

set transaction isolation level repeatable read

begin tran

insert into customer values(4, 'hellow', 'world', 'paradise 001', 'garden', 'CA', 00000, 1119995555)

commit

發現事務2立刻提交併正常結束了。返回事務1，執行：

select * from customer where State = 'CA'

會發現得到了2條記錄。這種現象就叫做幻像讀。

 

ORACLE

由於自身特殊的機制，oracle沒有提供一致讀隔離級別的選項，想要獲得一致讀的效果，實際上需要將事務提升到序列化等級，即serializable。

 

 

6.序列化級別不同資料庫實現

在這個級別，可以認為事務中的資料無論何時都是一致的，此級別使它顯得好像沒有其它使用者在修改資料，資料庫在事務開始時候被“凍結”（至少，對於本事務涉及的資料如此）。然而在不同資料庫中，其實現機制也不同。

SQL SERVER

開始事務1，執行：

set transaction isolation level serializable

begin tran

select * from customer where State = 'CA'

會得到1條記錄，這時事務2開始執行：

set transaction isolation level serializable

begin tran

insert into customer values(4, 'hellow', 'world', 'paradise 001', 'garden', 'CA', 00000, 1119995555)

commit

會發現事務2掛起，它在等待事務1結束。回到事務1，繼續：

select * from customer where State = 'CA'

update Customer set city = 'garden' where state = 'CA'

commit

在片刻的等待以後，事務1得到類似以以下格式訊息：

訊息1205，級別13，狀態56，第1 行

事務(程式ID 51)與另一個程式被死鎖在鎖資源上，並且已被選作死鎖犧牲品。請重新執行該事務。

而事務2更新了資料並正常結束。這是因為兩個事務都設定成了序列化級別，當遇到衝突時候，sql server根據一定的規則選擇犧牲掉其中一個事務，來保證事務的序列性。上面的例子，如果將事務2的隔離級別改為提交讀，那麼事務2會等待事務1完成，之後自己正常完成（因為事務2沒有序列需求，不會有死鎖）。

 

ORACLE

在oracle中，通過多版本，可以在一定程度上避免死鎖。

開始事務1，執行：

set transaction isolation level serializable;

select * from customer where State = 'CA';  --set tran語句隱式開始事務

得到1條記錄，然後事務2開始執行：

set transaction isolation level serializable;

insert into customer values(4, 'hellow', 'world', 'paradise 001', 'garden', 'CA', 00000, 1119995555);

commit;

可以發現事務2立刻完成，沒有阻塞。回到事務1繼續：

select * from customer where State = 'CA';

update Customer set city = 'garden' where state = 'CA';

commit;

事務1中的第二次查詢和事務開始時刻一致，就好像事務2已經完成的提交不存在。事務最終正常更新完畢，並保持了“事務開始”時刻的資料一致性。

然而，如果事務1，2修改同樣的資料行，也會有錯誤，

開始事務1，執行：

set transaction isolation level serializable;

select * from customer where State = 'CA';  --set tran語句隱式開始事務

得到1條記錄，然後事務2開始執行：

set transaction isolation level serializable;

update customer set state = 'KO' where state = 'CA';

commit;

可以發現事務2立刻完成，沒有阻塞。回到事務1繼續：

select * from customer where State = 'CA';

update Customer set city = 'garden' where state = 'CA';

commit;

出現錯誤資訊：

第 1 行出現錯誤:

ORA-08177: 無法連續訪問此事務處理

總的來說，oracle利用多版本的方式實現序列化級別更少造成死鎖，除非兩個事務修改了相同的資料行，一般也不會造成衝突。

 

 

 

7．不同隔離級別的相互影響

前面的例子基本都是兩個相同隔離級別事務的情況。如果不同隔離級別的事務發生衝突，會有什麼不同嗎？實際上，對於一個事務來說，其它事務的隔離級別對它來說是未知的，更進一步，甚至資料庫中有沒有其它事務，有多少事務也不知道。影響事務訪問資料就兩方面因素：該事務自身的隔離級別，該事務要訪問的資料上面鎖的狀態。

SQL SERVER

開始事務1，執行：

set transaction isolation level serializable

begin tran

select * from customer where State = 'CA'

事務1的查詢獲得1條記錄，轉到事務2，執行：

set transaction isolation level read uncommitted

begin tran

select * from customer

事務2查詢獲得3條記錄，回到事務1，執行：

update Customer set city = 'garden' where state = 'CA'

切換到事務2，執行：

select * from customer

update customer set state = 'KO' where state = 'CA'

commit;

因為事務2隔離級別為未提交讀，因此事務1中剛作的修改可以立刻從查詢看到，即使事務1還未結束。進一步的update因為事務1對記錄加了獨佔鎖，因此事務2掛起。回到事務1將其提交：

Commit

事務1正常結束，獨佔鎖釋放，從而讓事務2得以繼續修改資料，並最終完成。

 

ORACLE

Oracle資料庫的隔離級別設定語句只有read committed和serializable（read only暫不討論），加上其特殊鎖機制，不同隔離級別事務間的影響除了上例（例6）中兩個都為serializable的情況，其它都可視為互不阻塞。

 

8．頁鎖與行鎖（限sql server）

Sql server的鎖可以到行一級。然而它又存在自動的鎖定擴大，鎖定轉換。因此存在一些意想不到的情況。下面是演示：

開始事務1，執行：

set transaction isolation level read committed

begin tran

select * from customer where State = 'CA'

update Customer set city = 'garden' where state = 'CA'

理論上來說，在提交讀級別下，上面的update語句只是在state值為CA的資料行上加了獨佔鎖，表中其它資料應該可以被其它事務更新，然而，如下事務2開始：

set transaction isolation level read committed

begin tran

select * from customer

update customer set state = 'KO' where state = 'JP'

commit

發現事務2陷入阻塞狀態。儘管它們更新的不是同一條記錄。回到事務1，執行：

Commit

事務1結束後事務2才繼續執行至結束。

如果我們在表上加入索引，如下：

CREATE NONCLUSTERED INDEX [idx_state] ON [dbo].[Customer] ( [State])

再重複上面的步驟，會發現阻塞不再存在。

PS：這種現象應該和資料庫預設加鎖引數/機制有關，應該可以調整，但目前手中沒有進一步資料。故僅羅列了現象。

 

ORACLE

Oracle在資料一級只有一種資料行上的鎖，因此不存在sql server中這些問題。

 

9．Set transaction語句的作用週期

前面所有的例子，都是在會話視窗中進行的演示。一旦使用了set transaction語句，會影響整個會話。除非再顯式改變隔離級別，否則將保持到會話結束。例如：

開始事務1，假設會話一開始處於SQL SERVER的預設隔離級別（read committed）：

begin tran

select * from customer where State = 'CA'

select * from sys.dm_tran_locks

系統檢視sys.dm_tran_locks可以檢視當前的加鎖情況，到目前位置，只有資料庫級的鎖。繼續執行：

set transaction isolation level repeatable read

select * from customer where State = 'CA'

select * from sys.dm_tran_locks

commit

接下來的語句改變了隔離級別到可重複讀，接下來的查詢，會看到行級鎖的記錄。在上面事務提交後，執行：

begin tran

select * from customer where State = 'CA'

select * from sys.dm_tran_locks

commit

仍然會從檢視sys.dm_tran_locks看到行級鎖記錄。整個會話期間都受到影響。

 

但是，如果呼叫儲存過程，函式，則過程/函式中事務隔離級別的改變並不會對呼叫環境造成影響。可以通過以下例子說明，首先，建立一個儲存過程指令碼：

CREATE PROCEDURE [dbo].[test_tran_level]

AS

BEGIN

    BEGIN TRAN

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    SELECT * FROM CUSTOMER

    UPDATE CUSTOMER SET STATE = 'SS' WHERE CustID = 3

    SELECT * FROM sys.dm_tran_locks

    COMMIT

END

然後，在會話視窗呼叫該過程，會話視窗當前隔離級別為預設的提交讀：

Exec test_tran_level

執行的結果可以看到讀取鎖資訊，再在會話中執行：

begin tran

select * from customer where State = 'CA'

select * from sys.dm_tran_locks

commit

檢視sys.dm_tran_locks並未有讀鎖的記錄，說明事務隔離級別仍然是提交讀。

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