SQL Server和Oracle防止數據鎖定的比較
2024-08-29 13:32:33
供稿：網友
廖錚
2002-5-30 14:23:50
--------------------------------------------------------------------------------
數據庫并行訪問，也就是兩個或兩以上用戶同時訪問同一數據，這也是數據庫引擎如何設計和實現適度反應所面臨的最大問題。設計優良、性能卓越的數據庫引擎可以輕松地同時為成千上萬的用戶服務。而“底氣不足”的數據庫系統隨著更多的用戶同時訪問系統將大大降低其性能。最糟糕的情況下甚至可能導致系統的崩潰。
當然，并行訪問是任何數據庫解決方案都最為重視的問題了，為了解決并行訪問方面的問題各類數據庫系統提出了各種各樣的方案。 sql server和oracle兩大dbms也分別采用了不同的并行處理方法。它們之間的實質差別在哪里呢？
并行訪問的問題
并行訪問出現問題存在若干種情況。在最簡單的情形下，數量超過一個的用戶可能同時查詢同一數據。就這種情況而言數據庫的操作目標很簡單：盡可能地為用戶們提供快速的數據訪問。 這對我們現在常見的數據庫來說不成問題：sql server和 oracle 都采用了多線程機制，它們當然能夠一次處理多個請求。
不過，在用戶修改數據的情況下并行訪問問題就變得復雜起來了。顯然，數據庫通常只允許唯一用戶一次修改特定的數據。當某一用戶開始修改某塊數據時， sql server和 oracle 都能很快地鎖定數據，阻止其他用戶對這塊數據進行更新，直到修改該數據的第1位用戶完成其操作并提交交易（commit transaction）。但是，當某一位用戶正在修改某塊數據時假設另一位用戶又正想查詢該數據的信息時會發生什么情況呢？在這種情況下數據庫管理系統又該如何動作呢？oracle 和 sql server針對這一問題采取了不同的解決方案。
sql server方法
現在不妨假設有人開始修改sql server上存儲的數據，于是這塊數據立即被數據庫鎖定。數據鎖定操作阻塞其他任何訪問該數據的連接——連查詢操作都不會放過。于是，這塊被鎖定的數據只有在交易被提交或者回滾之后才能接受其他訪問操作。
下面用sql server隨帶的pubs示例數據庫做一個簡單示范。在query analyzer內打開兩個窗口。在第1個窗口中執行下列sql操作語句，更新pubs數據庫中某一圖書的價格：
use pubs
go
begin tran
update titles
set price = price * 1.05
where
title_id = 'bu2075'
由于代碼中并沒有執行commit語句，所以數據變動操作實際上還沒有最終完成。接下來，在另一個窗口里執行下列語句查詢titles數據表：
select title_id,title,price
from titles
order by title_id.
你什么結果也得不到。窗口底部的小地球圖標會轉個不停。盡管我在先前的操作中僅僅更新了一行，但是，select語句的執行對象卻恰好包含了其數據正被修改的一行。因此，上面的操作不會返回任何數據，除非回到第1個窗口提交交易或者回滾。
sql server的數據鎖定方案可能會降低系統的性能和效率。數據被鎖定的時間越長，或者鎖定的數據量越大，其他數據訪問用戶就越可能不得不等待其查詢語句的執行。因此，從程序員的角度來看，對sql server編程的時候應該盡量地把交易代碼設計得既小又快。
在sql server的最近版本中，微軟對sql server進行了某些修改，使其一次鎖定的數據量大大減少，這是數據庫設計中的一大重要改進。在6.5版及以前版本中，最少的數據鎖定量是一頁。哪怕你只在修改一行數據，而該行數據位于包含10行數據的一頁上，則整頁10行數據都會被鎖定。顯然，這么大的數據鎖定量增加了其他數據訪問連接不得不等待數據修正完成的概率。在sql server 7中，微軟引入了行鎖定技術，這樣，目前的sql server只鎖定實際正被改變的數據行。
sql server的解決方案聽起來很簡單，但實際上其幕后為提供足夠的系統高性能而采取了很多措施。例如，如果你在同時修改多行數據，sql server則會把數據鎖定范圍提升到頁級別乃至鎖定整個數據表，從而不必針對每一記錄跟蹤和維護各自的數據鎖。
oracle方法
下面我們再看看oracle數據庫是如何實施類似操作的。首先，我打開一個sqlplus實例執行下列查詢語句（這個例子可以在oracle 9i中示例中找到）。這個實例稱做查詢實例：
select first_name, last_name, salary
from hr.employees
where
department_id = 20;
代碼返回兩行數據，如下所示：
然后，再打開另一個sqlplus實例——更新實例來執行以下命令：
sql> update hr.employees
2 set salary = salary * 1.05
3 where
4 department_id = 20
5 /
代碼執行后回復消息稱兩行數據已被更新。
注意，以上代碼中并每有像在sql server示例那樣鍵入“begin tran”字樣的代碼。oracle 的sqlplus隱含啟用交易（你還可以模仿sql server的行為，設置“autocommit to on”自動地提交交易）。接下來我們在sqlplus更新實例中再執行同查詢實例一樣的select語句。
結果清楚地表明：michael和pat的薪水都增加了，然而這個時候我還沒有提交數據變更交易。
現在轉到第1個sqlplus查詢實例重新運行查詢，結果如下：
oracle不需要用戶等待數據更新實例中操作被提交，它徑直返回michael和pat的查詢信息，但實際上返回的是數據更新開始之前的數據視圖！
這時候，熟悉sql server的人可能會說了，在查詢中設置（nolock）不也能達到同樣的效果嗎？可是，對sql server而言，在數據映像之前是不能獲取數據的。指定（nolock）實際上只是得到了沒有提交的數據。oracle的方法則提供了數據的一致視圖，所有的信息都是針對交易的、基于存儲數據快照的。
如果在sqlplus的更新實例中提交更新交易在查詢實例中就能看到薪水數據發生變化。如果在查詢實例中重新運行先前的查詢語句，那么oracle將返回新的薪水數值。
存儲數據快照
說了半天，在給用戶顯示先前版本的數據同時，oracle是如何允許其他用戶修改數據的呢？其實，只要某一用戶啟動了一宗修改數據的交易，之前的數據映像就會被寫到一個特殊的存儲區域。這種“前映像”用來向任何查詢數據的用戶提供一致的數據庫視圖。這樣，當其他用戶在修改數據的時候，在以上的測試中我們就能看到尚未發生變更的薪金數據。
這個特殊的存儲區域在哪里呢？這個問題的答案就跟你正在使用的oracle版本有關了。在 oracle 8i及其以前版本中會為這一目的創建特殊的回滾段。然而，這種舉措會給數據庫管理員（dba）帶來管理和調整數據段的工作負擔。例如，dba必須確定為此需要的數據段的數量以及大小等。假如回滾段沒有正確配置，那么對交易而言它們就可能不得不排隊等待回滾段中出現必要的數據空間。
oracle 9i就不同了，這是oracle的最新版本，oracle實現了一種新特性，這就是所謂的undo表空間，它有效地消除了以上的管理復雜性。雖然回滾段仍然可以繼續使用，但是，dba現在可以選擇創建undo表空間的方式令oracle自己管理“前映像”的復雜空間分配。
oracle的這種方法對程序員具有重要意義。因為回滾空間不是無限的，所以，更新交易的數據快照會取代先前交易的映像。因此，如果必要的回滾段被其他交易的映像覆蓋的話。運行時間較長的查詢操作就可能產生“ snapshot too old”錯誤。
下面舉個可能發生的案例。假設在上午11:59的時候某位職員開始更新john doe帳務的交易。這宗交易在下午12:01被提交。同時，下午12:00某財務經理開始查詢所有的客戶帳務報表和當月收費總計。因為客戶很多，所以這一查詢操作很費了點時間，但是不論這次操作到底執行了多久，反正它檢索出的結果就是下午12:00數據庫中存在的數據。如果包含john doe帳務前映像的回滾空間在查詢執行到該客戶名字的時候被覆蓋則查詢返回錯誤消息。
oracle的解決方案當然更為合理，在抽象意義上提供了相比sql server更佳的數據一致性。在執行oracle查詢的時候無須擔心較長的查詢操作會鎖定重要的交易。但是，在兩種數據庫同時支持海量用戶的情況下也很難證明oracle是否就能真正實現具體條件下的數據一致性。
國內最大的酷站演示中心!
