MSSQL與Oracle數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別與鎖機(jī)制對(duì)比

一,事務(wù)的4個(gè)基本特征

Atomic（原子性）：
事務(wù)中包含的操作被看做一個(gè)邏輯單元，這個(gè)邏輯單元中的操作要
么全部成功，要么全部失敗。

Consistency（一致性）：
只有合法的數(shù)據(jù)可以被寫入數(shù)據(jù)庫(kù)，否則事務(wù)應(yīng)該將其回滾到最初
狀態(tài)。

Isolation（隔離性）：
事務(wù)允許多個(gè)用戶對(duì)同一個(gè)數(shù)據(jù)進(jìn)行并發(fā)訪問，而不破壞數(shù)據(jù)的正
確性和完整性。同時(shí)，并行事務(wù)的修改必須與其他并行事務(wù)的修改
相互獨(dú)立。

Durability（持久性）：
事務(wù)結(jié)束后，事務(wù)處理的結(jié)果必須能夠得到固化。

以上屬于廢話

二,為什么需要對(duì)事務(wù)并發(fā)控制

如果不對(duì)事務(wù)進(jìn)行并發(fā)控制，我們看看數(shù)據(jù)庫(kù)并發(fā)操作是會(huì)有那些異常情形

Lost update：
兩個(gè)事務(wù)都同時(shí)更新一行數(shù)據(jù)，但是第二個(gè)事務(wù)卻中途失敗退出，
導(dǎo)致對(duì)數(shù)據(jù)的兩個(gè)修改都失效了。

Dirty Reads：
一個(gè)事務(wù)開始讀取了某行數(shù)據(jù)，但是另外一個(gè)事務(wù)已經(jīng)更新了此數(shù)
據(jù)但沒有能夠及時(shí)提交。這是相當(dāng)危險(xiǎn)的，因?yàn)楹芸赡芩械牟僮?
都被回滾。

Non-repeatable Reads：
一個(gè)事務(wù)對(duì)同一行數(shù)據(jù)重復(fù)讀取兩次，但是卻得到了不同的結(jié)果。

Second lost updates problem：
無法重復(fù)讀取的特例。有兩個(gè)并發(fā)事務(wù)同時(shí)讀取同一行數(shù)據(jù)，然后其
中一個(gè)對(duì)它進(jìn)行修改提交，而另一個(gè)也進(jìn)行了修改提交。這就會(huì)造成
第一次寫操作失效。

Phantom Reads：
事務(wù)在操作過程中進(jìn)行兩次查詢，第二次查詢的結(jié)果包含了第一次查
詢中未出現(xiàn)的數(shù)據(jù)（這里并不要求兩次查詢的SQL語(yǔ)句相同）。這是
因?yàn)樵趦纱尾樵冞^程中有另外一個(gè)事務(wù)插入數(shù)據(jù)造成的。

三, 數(shù)據(jù)庫(kù)的隔離級(jí)別

為了兼顧并發(fā)效率和異常控制，在標(biāo)準(zhǔn)SQL規(guī)范中，定義了4個(gè)事務(wù)隔
離級(jí)別，（ORACLE和SQLSERER對(duì)標(biāo)準(zhǔn)隔離級(jí)別有不同的實(shí)現(xiàn) ）

Read Uncommitted：
直譯就是"讀未提交",意思就是即使一個(gè)更新語(yǔ)句沒有提交,但是別
的事務(wù)可以讀到這個(gè)改變.這是很不安全的.

Read Committed：
直譯就是"讀提交",意思就是語(yǔ)句提交以后即執(zhí)行了COMMIT以后
別的事務(wù)就能讀到這個(gè)改變.

Repeatable Read：
直譯就是"可以重復(fù)讀",這是說在同一個(gè)事務(wù)里面先后執(zhí)行同一個(gè)
查詢語(yǔ)句的時(shí)候,得到的結(jié)果是一樣的.

Serializable:
直譯就是"序列化",意思是說這個(gè)事務(wù)執(zhí)行的時(shí)候不允許別的事務(wù)
并發(fā)執(zhí)行.

四，隔離級(jí)別對(duì)并發(fā)的控制

下表是各隔離級(jí)別對(duì)各種異常的控制能力。
LU DR NRR SLU PR
RU Y Y Y Y Y
RC N N Y Y Y
RR N N N N Y
S N N N N N

(注：LU：丟失更新；DR：臟讀；NRR：非重復(fù)讀；SLU：二類丟失更新；PR：幻像讀)

順便舉一小例。

MS_SQL:

--事務(wù)一
set transaction isolation level serializable
begin tran
insert into test values('xxx')

--事務(wù)二
set transaction isolation level read committed
begin tran
select * from test

--事務(wù)三
set transaction isolation level read uncommitted
begin tran
select * from test

在查詢分析器中執(zhí)行事務(wù)一后，分別執(zhí)行事務(wù)二，和三。結(jié)果是事務(wù)二會(huì)等待，而事務(wù)三則會(huì)執(zhí)行。

ORACLE：

--事務(wù)一
set transaction isolation level serializable;
insert into test values('xxx');
select * from test;

--事務(wù)二
set transaction isolation level read committed--ORACLE默認(rèn)級(jí)別
select * from test

執(zhí)行事務(wù)一后，執(zhí)行事務(wù)二。結(jié)果是事務(wù)二只讀出原有的數(shù)據(jù)，無視事務(wù)一的插入操作。

讀者是否發(fā)現(xiàn)MS_SQL和ORACLE對(duì)并發(fā)控制的處理有所不同呢？

五,鎖

下表是鎖的兼容或沖突情形。
現(xiàn)有 S U X
請(qǐng)求
S Y Y N
U Y N N
X N N N

現(xiàn)有 S U X
申請(qǐng)    
S  Y Y N
U  Y N N
X  N N N

oracle:
 

六，注意點(diǎn)
 
一般處理并發(fā)問題時(shí)的步驟：
1、開啟事務(wù)。
2、申請(qǐng)寫權(quán)限，也就是給對(duì)象(表或記錄)加鎖。
3、假如失敗，則結(jié)束事務(wù)，過一會(huì)重試。
4、假如成功，也就是給對(duì)象加鎖成功，防止其他用戶再用同樣的方式打開。
5、進(jìn)行編輯操作。
6、寫入所進(jìn)行的編輯結(jié)果。
7、假如寫入成功，則提交事務(wù),完成操作。
8、假如寫入失敗，則回滾事務(wù)，取消提交。
9、(7.8)兩步操作已釋放了鎖定的對(duì)象，恢復(fù)到操作前的狀態(tài)。
 
對(duì)多表的操作最好一起取得鎖,或則保證處理順序;個(gè)人感覺還是前者好,雖然效率低一些

七，附
查看鎖
ORACLE：

select object_name,session_id,os_user_name,oracle_username,process,locked_mode,status
from v$locked_object l, all_objects a
where l.object_id=a.object_id;

MS_SQL：

EXEC SP_LOCK