sqlserver中的事務和鎖詳細解析

2024-08-31 01:01:33

字體：大中小

來源：轉載

供稿：網友

前幾天“拜讀”《sqlserver2005高級程序設計》和《SQL Server 2008編程入門經典(第3版)》這兩本翻譯后的中文版書籍。竟然發現目錄結構大致一樣，其講解的內容幾乎差不多。有抄襲的嫌疑。看到“事務和鎖”那一張中，發現連舉的小例子、表格都一模一樣。哈哈。。。對這類書籍，真不想做太多評論了。國內那些翻譯版的書籍嘛。說真的，大部分翻譯得有點生硬。而那些“原創著作”嘛。大多是相互抄襲，空談。就微軟技術體系而言，如果直接從MSDN或者聯機叢書中copy一下，再隨便貼幾頁的代碼，那樣就能出版銷售，那可能我也能著書了，因為那確實沒啥水平。

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

一、臟讀、不可重復讀、幻讀

（1）臟讀：臟讀就是指當一個事務正在訪問數據，并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然后使用了這個數據。

例如：

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

與此同時，

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

隨后，

事務A發生異常，而回滾了事務。張三的工資又回滾為5000。

最后，

事務B讀取到的張三工資為8000的數據即為臟數據，事務B做了一次臟讀。

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

例如：

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

與此同時，

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

隨后，

在事務A中，再次讀取張三的工資，此時工資變為8000。在一個事務中前后兩次讀取的結果并不致，導致了不可重復讀。

（3）幻讀: 是指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的數據進行了修改，這種修改涉及到表中的全部數據行。同時，第二個事務也修改這個表中的數據，這種修改是向表中插入一行新數據。那么，以后就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行，就好象發生了幻覺一樣。

例如:

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

此時，

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

這是，事務A再次讀取工資為5000的員工，記錄為11人。此時產生了幻讀。

不可重復讀的重點是修改:
同樣的條件，你讀取過的數據，再次讀取出來發現值不一樣了
幻讀的重點在于新增或者刪除：
同樣的條件，第 1 次和第 2 次讀出來的記錄數不一樣

二、獨占鎖、共享鎖、更新鎖，樂觀鎖、悲觀鎖

1、鎖的兩種分類方式

（1）從數據庫系統的角度來看，鎖分為以下三種類型：

•獨占鎖（Exclusive Lock）
獨占鎖鎖定的資源只允許進行鎖定操作的程序使用，其它任何對它的操作均不會被接受。執行數據更新命令，即INSERT、 UPDATE 或DELETE 命令時，SQL Server 會自動使用獨占鎖。但當對象上有其它鎖存在時，無法對其加獨占鎖。獨占鎖一直到事務結束才能被釋放。

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

•更新鎖（Update Lock）
更新鎖是為了防止死鎖而設立的。當SQL Server 準備更新數據時，它首先對數據對象作更新鎖鎖定，這樣數據將不能被修改，但可以讀取。等到SQL Server 確定要進行更新數據操作時，它會自動將更新鎖換為獨占鎖。但當對象上有其它鎖存在時，無法對其作更新鎖鎖定。

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

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

•樂觀鎖（Optimistic Lock）
相對悲觀鎖而言，樂觀鎖機制采取了更加寬松的加鎖機制。悲觀鎖大多數情況下依靠數據庫的鎖機制實現，以保證操作最大程度的獨占性。但隨之而來的就是數據庫性能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受。

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

2、數據庫中如何使用鎖

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

注：對于悲觀鎖是針對并發的可能性比較大，而一般在我們的應用中用樂觀鎖足以。

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

首先建立測試用的數據庫表：

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用戶的表，把數據copy到我們的test表中。

（1）for update 形式介紹

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

select * from test where id = 10 for update

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

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

（2）for update nowait 形式介紹

另外一種情況，就是當數據庫數據被鎖定的時候，也就是執行剛才for update那條sql以后，我們在另外一個session中執行for update nowait后又是什么樣呢。

比如如下的sql語句:

select * from test where id = 10 for update nowait

由于這條語句中是制定采用nowait方式來進行檢索，所以當發現數據被別的session鎖定中的時候，就會迅速返回ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源。所以在程序中我們可以采用nowait方式迅速判斷當前數據是否被鎖定中，如果鎖定中的話，就要采取相應的業務措施進行處理。

那這里另外一個問題，就是當我們鎖定住數據的時候，我們對數據進行更新和刪除的話會是什么樣呢。

比如同樣，我們讓第一個Session鎖定住id=10的那條數據，我們在第二個session中執行如下語句：

update test set value=2 where id = 10

這個時候我們發現update語句就好像select for update語句一樣也停住卡在這里，當你第一個session放開鎖定以后update才能正常運行。當你update運行后，數據又被你update 語句鎖定住了，這個時候只要你update后還沒有commit，別的session照樣不能對數據進行鎖定更新等等。

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

與悲觀鎖相對的，我們有了樂觀鎖。樂觀鎖一開始也說了，就是一開始假設不會造成數據沖突，在最后提交的時候再進行數據沖突檢測。

在樂觀鎖中，我們有3種常用的做法來實現：

•第一種就是在數據取得的時候把整個數據都copy到應用中，在進行提交的時候比對當前數據庫中的數據和開始的時候更新前取得的數據。

當發現兩個數據一模一樣以后，就表示沒有沖突可以提交，否則則是并發沖突，需要去用業務邏輯進行解決。

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

采用版本戳的話，首先需要在你有樂觀鎖的數據庫table上建立一個新的column，比如為number型，當你數據每更新一次的時候，版本數就會往上增加1。

比如同樣有2個session同樣對某條數據進行操作。兩者都取到當前的數據的版本號為1，當第一個session進行數據更新后，在提交的時候查看到當前數據的版本還為1，和自己一開始取到的版本相同。就正式提交，然后把版本號增加1，這個時候當前數據的版本為2。當第二個session也更新了數據提交的時候，發現數據庫中版本為2，和一開始這個session取到的版本號不一致，就知道別人更新過此條數據，這個時候再進行業務處理，比如整個Transaction都Rollback等等操作。

在用版本戳的時候，可以在應用程序側使用版本戳的驗證，也可以在數據庫側采用Trigger(觸發器)來進行驗證。不過數據庫的Trigger的性能開銷還是比較的大，所以能在應用側進行驗證的話還是推薦不用 Trigger。

•第三種做法和第二種做法有點類似，就是也新增一個Table的Column，不過這次這個column是采用timestamp型，存儲數據最后更新的時間。

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

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

三、事務五種隔離級別

Isolation 屬性一共支持五種事務設置，具體介紹如下：

（1）DEFAULT

使用數據庫設置的隔離級別（默認），由DBA 默認的設置來決定隔離級別。

（2）READ_UNCOMMITTED

這是事務最低的隔離級別，它充許別外一個事務可以看到這個事務未提交的數據。

會出現臟讀、不可重復讀、幻讀（隔離級別最低，并發性能高）。

（3）READ_COMMITTED

保證一個事務修改的數據提交后才能被另外一個事務讀取。另外一個事務不能讀取該事務未提交的數據。

可以避免臟讀，但會出現不可重復讀、幻讀問題（鎖定正在讀取的行）。

（4）REPEATABLE_READ