前幾天“拜讀”《sqlserver2005高級(jí)程序設(shè)計(jì)》和《SQL Server 2008編程入門經(jīng)典(第3版)》這兩本翻譯后的中文版書籍。竟然發(fā)現(xiàn)目錄結(jié)構(gòu)大致一樣，其講解的內(nèi)容幾乎差不多。有抄襲的嫌疑?？吹健笆聞?wù)和鎖”那一張中，發(fā)現(xiàn)連舉的小例子、表格都一模一樣。哈哈。。。對(duì)這類書籍，真不想做太多評(píng)論了。國內(nèi)那些翻譯版的書籍嘛。說真的，大部分翻譯得有點(diǎn)生硬。而那些“原創(chuàng)著作”嘛。大多是相互抄襲，空談。就微軟技術(shù)體系而言，如果直接從MSDN或者聯(lián)機(jī)叢書中copy一下，再隨便貼幾頁的代碼，那樣就能出版銷售，那可能我也能著書了，因?yàn)槟谴_實(shí)沒啥水平。

當(dāng)然，也不乏精品之作，只是很少且很難找到罷了。好了，言歸正傳，開始說說事務(wù)和鎖，這大概是數(shù)據(jù)庫中比較難理解的東西了。

一、臟讀、不可重復(fù)讀、幻讀

（1）臟讀：臟讀就是指當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時(shí)，另外一個(gè)事務(wù)也訪問這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。

例如：

張三的工資為5000,事務(wù)A中把他的工資改為8000,但事務(wù)A尚未提交。

與此同時(shí)，

事務(wù)B正在讀取張三的工資，讀取到張三的工資為8000。

隨后，

事務(wù)A發(fā)生異常，而回滾了事務(wù)。張三的工資又回滾為5000。

最后，

事務(wù)B讀取到的張三工資為8000的數(shù)據(jù)即為臟數(shù)據(jù)，事務(wù)B做了一次臟讀。

（2）不可重復(fù)讀：是指在一個(gè)事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問該同一數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。

例如：

在事務(wù)A中，讀取到張三的工資為5000，操作沒有完成，事務(wù)還沒提交。

與此同時(shí)，

事務(wù)B把張三的工資改為8000，并提交了事務(wù)。

隨后，

在事務(wù)A中，再次讀取張三的工資，此時(shí)工資變?yōu)?000。在一個(gè)事務(wù)中前后兩次讀取的結(jié)果并不致，導(dǎo)致了不可重復(fù)讀。

（3）幻讀: 是指當(dāng)事務(wù)不是獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象，例如第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時(shí)，第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，以后就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。

例如:

目前工資為5000的員工有10人，事務(wù)A讀取所有工資為5000的人數(shù)為10人。

此時(shí)，

事務(wù)B插入一條工資也為5000的記錄。

這是，事務(wù)A再次讀取工資為5000的員工，記錄為11人。此時(shí)產(chǎn)生了幻讀。

不可重復(fù)讀的重點(diǎn)是修改:
同樣的條件，你讀取過的數(shù)據(jù)，再次讀取出來發(fā)現(xiàn)值不一樣了
幻讀的重點(diǎn)在于新增或者刪除：
同樣的條件，第 1 次和第 2 次讀出來的記錄數(shù)不一樣

二、獨(dú)占鎖、共享鎖、更新鎖，樂觀鎖、悲觀鎖

1、鎖的兩種分類方式

（1）從數(shù)據(jù)庫系統(tǒng)的角度來看，鎖分為以下三種類型：

•獨(dú)占鎖（Exclusive Lock）
獨(dú)占鎖鎖定的資源只允許進(jìn)行鎖定操作的程序使用，其它任何對(duì)它的操作均不會(huì)被接受。執(zhí)行數(shù)據(jù)更新命令，即INSERT、 UPDATE 或DELETE 命令時(shí)，SQL Server 會(huì)自動(dòng)使用獨(dú)占鎖。但當(dāng)對(duì)象上有其它鎖存在時(shí)，無法對(duì)其加獨(dú)占鎖。獨(dú)占鎖一直到事務(wù)結(jié)束才能被釋放。

•共享鎖（Shared Lock）
共享鎖鎖定的資源可以被其它用戶讀取，但其它用戶不能修改它。在SELECT 命令執(zhí)行時(shí)，SQL Server 通常會(huì)對(duì)對(duì)象進(jìn)行共享鎖鎖定。通常加共享鎖的數(shù)據(jù)頁被讀取完畢后，共享鎖就會(huì)立即被釋放。

•更新鎖（Update Lock）
更新鎖是為了防止死鎖而設(shè)立的。當(dāng)SQL Server 準(zhǔn)備更新數(shù)據(jù)時(shí)，它首先對(duì)數(shù)據(jù)對(duì)象作更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以讀取。等到SQL Server 確定要進(jìn)行更新數(shù)據(jù)操作時(shí)，它會(huì)自動(dòng)將更新鎖換為獨(dú)占鎖。但當(dāng)對(duì)象上有其它鎖存在時(shí)，無法對(duì)其作更新鎖鎖定。 

（2）從程序員的角度看，鎖分為以下兩種類型：

•悲觀鎖（Pessimistic Lock）
悲觀鎖，正如其名，它指的是對(duì)數(shù)據(jù)被外界（包括本系統(tǒng)當(dāng)前的其他事務(wù)，以及來自外部系統(tǒng)的事務(wù)處理）修改持保守態(tài)度，因此在整個(gè)數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實(shí)現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機(jī)制（也只有數(shù)據(jù)庫層提供的鎖機(jī)制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在本系統(tǒng)中實(shí)現(xiàn)了加鎖機(jī)制，也無法保證外部系統(tǒng)不會(huì)修改數(shù)據(jù)）。 

•樂觀鎖（Optimistic Lock）
相對(duì)悲觀鎖而言，樂觀鎖機(jī)制采取了更加寬松的加鎖機(jī)制。悲觀鎖大多數(shù)情況下依靠數(shù)據(jù)庫的鎖機(jī)制實(shí)現(xiàn)，以保證操作最大程度的獨(dú)占性。但隨之而來的就是數(shù)據(jù)庫性能的大量開銷，特別是對(duì)長(zhǎng)事務(wù)而言，這樣的開銷往往無法承受。

而樂觀鎖機(jī)制在一定程度上解決了這個(gè)問題。樂觀鎖，大多是基于數(shù)據(jù)版本（ Version ）記錄機(jī)制實(shí)現(xiàn)。何謂數(shù)據(jù)版本？即為數(shù)據(jù)增加一個(gè)版本標(biāo)識(shí)，在基于數(shù)據(jù)庫表的版本解決方案中，一般是通過為數(shù)據(jù)庫表增加一個(gè) “version” 字段來實(shí)現(xiàn)。讀取出數(shù)據(jù)時(shí)，將此版本號(hào)一同讀出，之后更新時(shí)，對(duì)此版本號(hào)加一。此時(shí)，將提交數(shù)據(jù)的版本數(shù)據(jù)與數(shù)據(jù)庫表對(duì)應(yīng)記錄的當(dāng)前版本信息進(jìn)行比對(duì)，如果提交的數(shù)據(jù)版本號(hào)大于數(shù)據(jù)庫表當(dāng)前版本號(hào)，則予以更新，否則認(rèn)為是過期數(shù)據(jù)。

2、數(shù)據(jù)庫中如何使用鎖

首先從悲觀鎖開始說。在SqlServer等其余很多數(shù)據(jù)庫中，數(shù)據(jù)的鎖定通常采用頁級(jí)鎖的方式，也就是說對(duì)一張表內(nèi)的數(shù)據(jù)是一種串行化的更新插入機(jī)制，在任何時(shí)間同一張表只會(huì)插1條數(shù)據(jù)，別的想插入的數(shù)據(jù)要等到這一條數(shù)據(jù)插完以后才能依次插入。帶來的后果就是性能的降低，在多用戶并發(fā)訪問的時(shí)候，當(dāng)對(duì)一張表進(jìn)行頻繁操作時(shí)，會(huì)發(fā)現(xiàn)響應(yīng)效率很低，數(shù)據(jù)庫經(jīng)常處于一種假死狀態(tài)。而Oracle用的是行級(jí)鎖，只是對(duì)想鎖定的數(shù)據(jù)才進(jìn)行鎖定，其余的數(shù)據(jù)不相干，所以在對(duì)Oracle表中并發(fā)插數(shù)據(jù)的時(shí)候，基本上不會(huì)有任何影響。

注：對(duì)于悲觀鎖是針對(duì)并發(fā)的可能性比較大，而一般在我們的應(yīng)用中用樂觀鎖足以。

Oracle的悲觀鎖需要利用一條現(xiàn)有的連接，分成兩種方式，從SQL語句的區(qū)別來看，就是一種是for update，一種是for update nowait的形式。比如我們看一個(gè)例子。

首先建立測(cè)試用的數(shù)據(jù)庫表：

CREATE TABLE TEST(ID,NAME,LOCATION,VALUE,CONSTRAINT test_pk PRIMARY KEY(ID))AS SELECT deptno, dname, loc, 1 FROM scott.dept

這里我們利用了Oracle的Sample的scott用戶的表，把數(shù)據(jù)copy到我們的test表中。

（1）for update 形式介紹

然后我們看一下for update鎖定方式。我們執(zhí)行如下的select for update語句：

 select * from test where id = 10 for update

通過這條檢索語句鎖定以后，再開另外一個(gè)sql*plus窗口進(jìn)行操作，再把上面這條sql語句執(zhí)行一便，你會(huì)發(fā)現(xiàn)sqlplus好像死在那里了，好像檢索不到數(shù)據(jù)的樣子，但是也不返回任何結(jié)果，就屬于卡在那里的感覺。這個(gè)時(shí)候是什么原因呢，就是一開始的第一個(gè)Session中的select for update語句把數(shù)據(jù)鎖定住了。由于這里鎖定的機(jī)制是wait的狀態(tài)(只要不表示nowait那就是wait)，所以第二個(gè)Session(也就是卡住的那個(gè)sql*plus)中當(dāng)前這個(gè)檢索就處于等待狀態(tài)。當(dāng)?shù)谝粋€(gè)session最后commit或者rollback之后，第二個(gè)session中的檢索結(jié)果就是自動(dòng)跳出來，并且也把數(shù)據(jù)鎖定住。

不過如果你第二個(gè)session中你的檢索語句如下所示：select * from test where id = 10，也就是沒有for update這種鎖定數(shù)據(jù)的語句的話，就不會(huì)造成阻塞了。

（2）for update nowait 形式介紹

另外一種情況，就是當(dāng)數(shù)據(jù)庫數(shù)據(jù)被鎖定的時(shí)候，也就是執(zhí)行剛才for update那條sql以后，我們?cè)诹硗庖粋€(gè)session中執(zhí)行for update nowait后又是什么樣呢。

比如如下的sql語句:

select * from test where id = 10 for update nowait

由于這條語句中是制定采用nowait方式來進(jìn)行檢索，所以當(dāng)發(fā)現(xiàn)數(shù)據(jù)被別的session鎖定中的時(shí)候，就會(huì)迅速返回ORA-00054錯(cuò)誤，內(nèi)容是資源正忙, 但指定以 NOWAIT 方式獲取資源。所以在程序中我們可以采用nowait方式迅速判斷當(dāng)前數(shù)據(jù)是否被鎖定中，如果鎖定中的話，就要采取相應(yīng)的業(yè)務(wù)措施進(jìn)行處理。

那這里另外一個(gè)問題，就是當(dāng)我們鎖定住數(shù)據(jù)的時(shí)候，我們對(duì)數(shù)據(jù)進(jìn)行更新和刪除的話會(huì)是什么樣呢。

比如同樣，我們讓第一個(gè)Session鎖定住id=10的那條數(shù)據(jù)，我們?cè)诘诙€(gè)session中執(zhí)行如下語句：

update test set value=2 where id = 10

這個(gè)時(shí)候我們發(fā)現(xiàn)update語句就好像select for update語句一樣也停住卡在這里，當(dāng)你第一個(gè)session放開鎖定以后update才能正常運(yùn)行。當(dāng)你update運(yùn)行后，數(shù)據(jù)又被你update 語句鎖定住了，這個(gè)時(shí)候只要你update后還沒有commit，別的session照樣不能對(duì)數(shù)據(jù)進(jìn)行鎖定更新等等。

總之，Oracle中的悲觀鎖就是利用Oracle的Connection對(duì)數(shù)據(jù)進(jìn)行鎖定。在Oracle中，用這種行級(jí)鎖帶來的性能損失是很小的，只是要注意程序邏輯，不要給你一不小心搞成死鎖了就好。而且由于數(shù)據(jù)的及時(shí)鎖定，在數(shù)據(jù)提交時(shí)候就不呼出現(xiàn)沖突，可以省去很多惱人的數(shù)據(jù)沖突處理。缺點(diǎn)就是你必須要始終有一條數(shù)據(jù)庫連接，就是說在整個(gè)鎖定到最后放開鎖的過程中，你的數(shù)據(jù)庫聯(lián)接要始終保持住。

與悲觀鎖相對(duì)的，我們有了樂觀鎖。樂觀鎖一開始也說了，就是一開始假設(shè)不會(huì)造成數(shù)據(jù)沖突，在最后提交的時(shí)候再進(jìn)行數(shù)據(jù)沖突檢測(cè)。

在樂觀鎖中，我們有3種常用的做法來實(shí)現(xiàn)：

•第一種就是在數(shù)據(jù)取得的時(shí)候把整個(gè)數(shù)據(jù)都copy到應(yīng)用中，在進(jìn)行提交的時(shí)候比對(duì)當(dāng)前數(shù)據(jù)庫中的數(shù)據(jù)和開始的時(shí)候更新前取得的數(shù)據(jù)。

當(dāng)發(fā)現(xiàn)兩個(gè)數(shù)據(jù)一模一樣以后，就表示沒有沖突可以提交，否則則是并發(fā)沖突，需要去用業(yè)務(wù)邏輯進(jìn)行解決。

•第二種樂觀鎖的做法就是采用版本戳，這個(gè)在Hibernate中得到了使用。

采用版本戳的話，首先需要在你有樂觀鎖的數(shù)據(jù)庫table上建立一個(gè)新的column，比如為number型，當(dāng)你數(shù)據(jù)每更新一次的時(shí)候，版本數(shù)就會(huì)往上增加1。

比如同樣有2個(gè)session同樣對(duì)某條數(shù)據(jù)進(jìn)行操作。兩者都取到當(dāng)前的數(shù)據(jù)的版本號(hào)為1，當(dāng)?shù)谝粋€(gè)session進(jìn)行數(shù)據(jù)更新后，在提交的時(shí)候查看到當(dāng)前數(shù)據(jù)的版本還為1，和自己一開始取到的版本相同。就正式提交，然后把版本號(hào)增加1，這個(gè)時(shí)候當(dāng)前數(shù)據(jù)的版本為2。當(dāng)?shù)诙€(gè)session也更新了數(shù)據(jù)提交的時(shí)候，發(fā)現(xiàn)數(shù)據(jù)庫中版本為2，和一開始這個(gè)session取到的版本號(hào)不一致，就知道別人更新過此條數(shù)據(jù)，這個(gè)時(shí)候再進(jìn)行業(yè)務(wù)處理，比如整個(gè)Transaction都Rollback等等操作。

在用版本戳的時(shí)候，可以在應(yīng)用程序側(cè)使用版本戳的驗(yàn)證，也可以在數(shù)據(jù)庫側(cè)采用Trigger(觸發(fā)器)來進(jìn)行驗(yàn)證。不過數(shù)據(jù)庫的Trigger的性能開銷還是比較的大，所以能在應(yīng)用側(cè)進(jìn)行驗(yàn)證的話還是推薦不用 Trigger。

•第三種做法和第二種做法有點(diǎn)類似，就是也新增一個(gè)Table的Column，不過這次這個(gè)column是采用timestamp型，存儲(chǔ)數(shù)據(jù)最后更新的時(shí)間。

在Oracle9i以后可以采用新的數(shù)據(jù)類型，也就是timestamp with time zone類型來做時(shí)間戳。這種Timestamp的數(shù)據(jù)精度在Oracle的時(shí)間類型中是最高的，精確到微秒(還沒與到納秒的級(jí)別)，一般來說，加上數(shù)據(jù)庫處理時(shí)間和人的思考動(dòng)作時(shí)間，微秒級(jí)別是非常非常夠了，其實(shí)只要精確到毫秒甚至秒都應(yīng)該沒有什么問題。

和剛才的版本戳類似，也是在更新提交的時(shí)候檢查當(dāng)前數(shù)據(jù)庫中數(shù)據(jù)的時(shí)間戳和自己更新前取到的時(shí)間戳進(jìn)行對(duì)比，如果一致則OK，否則就是版本沖突。如果不想把代碼寫在程序中或者由于別的原因無法把代碼寫在現(xiàn)有的程序中，也可以把這個(gè)時(shí)間戳樂觀鎖邏輯寫在Trigger或者存儲(chǔ)過程中。

三、事務(wù)五種隔離級(jí)別

Isolation 屬性一共支持五種事務(wù)設(shè)置，具體介紹如下：

（1）DEFAULT

使用數(shù)據(jù)庫設(shè)置的隔離級(jí)別（默認(rèn)），由DBA 默認(rèn)的設(shè)置來決定隔離級(jí)別。

（2）READ_UNCOMMITTED

這是事務(wù)最低的隔離級(jí)別，它充許別外一個(gè)事務(wù)可以看到這個(gè)事務(wù)未提交的數(shù)據(jù)。

會(huì)出現(xiàn)臟讀、不可重復(fù)讀、幻讀 （隔離級(jí)別最低，并發(fā)性能高）。

（3）READ_COMMITTED

保證一個(gè)事務(wù)修改的數(shù)據(jù)提交后才能被另外一個(gè)事務(wù)讀取。另外一個(gè)事務(wù)不能讀取該事務(wù)未提交的數(shù)據(jù)。

可以避免臟讀，但會(huì)出現(xiàn)不可重復(fù)讀、幻讀問題（鎖定正在讀取的行）。

（4）REPEATABLE_READ

可以防止臟讀、不可重復(fù)讀，但會(huì)出幻讀（鎖定所讀取的所有行）。

（5）SERIALIZABLE

這是花費(fèi)最高代價(jià)但是最可靠的事務(wù)隔離級(jí)別，事務(wù)被處理為順序執(zhí)行。

保證所有的情況不會(huì)發(fā)生（鎖表）。