Microsoft SQL Server 2000 超級管理手冊(十九)-CSDN博客

本文链接：https://blog.csdn.net/gzwl/article/details/123814

19. 了解交易與交易鎖定

什麼是交易？

ACID 屬性

交易模式

交易復原

交易鎖定

封鎖與死結

鎖定提示

本章總結

本章將闡明交易和交易鎖定的基本法則，學習什麼是交易、Microsoft SQL Sever 2000對有效交易所要求的屬性、交易的起始與結束可指定使用哪些交易模式，以及如何認可及復原交易；還會看到執行交易時SQL Server所使用的鎖定類型和模式，以及「封鎖」（blocking）和「死結」（deadlocking）的概念；最後介紹您在交易中可能會使用到的一些資料表鎖定提示。

什麼是交易？
　

「交易（Transaction）」是以工作的單一邏輯單元（Logic Unit）來執行的一系列相關作業。交易使SQL Server確保一定水平的資料完整性和資料可回復性。每個資料庫都必須擁有所謂的「交易記錄檔（transaction log）」，用於保存對資料庫進行修改（插入、更新或刪除）的所有交易記錄。一旦發生了錯誤或系統故障，SQL Server便使用該交易記錄檔來回復資料。

交易的完整性，部分要靠程式設計人員來達成。程式設計人員必須知道何時開始交易、何時終止交易，以及以什麼順序來進行資料修改，以確保資料的邏輯一致性和有意義。現在您知道什麼是交易了，讓我們來看看有效交易要求的屬性。

ACID屬性
　

一個交易必須符合四個特定要求才有資格作為一個有效的交易，這些要求被稱為「ACID屬性」，ACID是「不可部分完成性（Atomicity）」、「一致性（Consistency）」、「隔離性（Isolation）」和「耐久性（Durability）」的英文縮寫。SQL Server提供機制來幫助確認一個交易是否符合每一個要求。

不可部分完成性
　

如果交易成功，那麼SQL Server保證交易中的所有資料修改都是集體性的完成；而當交易不成功時，就不作任何修改。換句話說，SQL Server確保了交易的不可部分完成性。交易必須作為一個不可分割的基本單元來執行，所以稱為不可部分完成性。要交易獲得成功，交易的每一個步驟（或陳述式）都必須成功。如果一個步驟失敗，整個交易就失敗了，而且從該交易的開始之後所作的修改都將會取消。SQL Server提供了交易管理機制，它自動執行確定交易成功或失敗的任務，而且如果失敗，它必定會取消所有的資料修改。

一致性
　

SQL Server還會確保交易的一致性。一致性意味著在交易完成後，所有的資料保持一致的狀態，這保證了資料的完整性，無論交易是成功還是失敗。在交易之前，資料庫必須處於一個一致的狀態－亦即資料維持其完整性，而且內在的結構，例如B-tree索引和雙連結清單（double linked lists），是正確無誤的。在交易發生後，資料庫也必須保持一致的狀態，如果交易成功就變為新的狀態，或是如果交易失敗，會保持與交易開始前的狀態一致。

一致性也是SQL Server提供的交易管理特性。如果您的資料一致，交易將保持邏輯一致和資料完整，SQL Server將確保交易後的資料一致性。當您在分散式環境中使用資料複寫時，能夠達到不同等級的一致性，其範圍從最後的交易收斂（或潛在的一致性）到即時交易一致性均包含在內。一致性的等級取決於您使用的複寫類型。如需獲得更多資訊，請參閱第26章到第28章。

隔離性
　

「隔離性」意指每一個交易產生的作用，會如同此交易是系統中唯一的一個交易；也就是說，一個交易所作的修改與任何其他同時發生的交易所作的修改是隔離的。在一個交易的變更得到認可之前，另一個交易並不會受到此交易變更的值所影響。如果交易失敗，它的修改不會有任何影響，因為變更都將被退回。SQL Server允許您決定交易的隔離等級。交易的隔離性動作取決於您指定的隔離等級。

說明

當交易完成後，它的所有修改成為資料庫的永久部分。當交易被退回，變更將被取消，資料庫看上去就像交易從未發生一樣。

隔離等級
　

SQL Server支援四種等級的隔離性。隔離等級是一種設定，用來決定允許交易接受不一致資料的等級－即一個交易與另一個交易隔離的程度。一個較高的隔離等級提高了資料的正確性，但是它減少了可並行的交易數目。另一方面，較低的隔離等級將允許更多交易並行，但卻降低了資料的準確性。您對SQL Server工作階段所指定的隔離等級，決定了工作階段期間所有SELECT陳述式的鎖定行為（除非您設定到另一隔離等級）。鎖定行為會在本章稍後的〈交易鎖定〉一節中討論。

讀取未認可 ：最低級別的隔離性。在這個等級，交易隔離僅僅足以確保被實體毀壞的資料不可讀取。
　
讀取認可 ：SQL Server的預設等級。在這個等級，只允許讀取已認可的資料（已認可資料指的是已經成為資料庫永久部分的資料）。
　
可重覆讀取 ：在這個等級，一個交易對同一資料列或數資料列的重覆讀取將得到同樣的結果。（直到該交易完成，否則沒有其他交易能修改資料。）

序列化（Serializable） ：最高級別的隔離性，交易彼此之間完全隔離。在這個等級，一個資料庫中同時執行多個交易獲得的結果與交易序列執行（即以一定的順序一次一個）是一樣的。
　
並行交易行為
　

為了更容易理解每一個隔離等級，首先看看當您執行並行交易時，可能會發生的三種行為。
Dirty讀取 ：即對尚未認可的資料進行讀取。Dirty read發生在一個交易修改資料，而第二個交易在第一個交易認可變更前讀取修改的資料時。如果第一個交易復原變更，第二個交易將檢索到資料庫中沒有的資料。
　
非可重覆讀取（Nonrepeatable read） ：重覆的讀取會獲得不一致的資料。非可重覆讀取發生在同一交易中對單一資料列進行多次讀取期間，另一個獨立的交易對該資料列資料進行了更新。因為第一個交易的重覆讀取會檢索到不同的資料，在這個交易中結果是不可重覆的。
　
幻像讀取（Phantom read） ：發生在交易試圖檢索一個不存在的資料列，但是在交易完成前另一個交易又插入了那一資料列。如果第一個交易再次尋找該資料列，它將會發現該資料列已經突然出現。這個新的資料列稱為「幻像資料列（phantom row）」。
　
表19-1列出了每個隔離等級允許的行為類型。如您所見，「讀取未認可」是限制最少的隔離等級，而「序列化」是限制最嚴格的隔離等級。如前所述，SQL Server的預設隔離等級是「讀取認可」。隨著隔離等級的提高，SQL Server將會用較長的時間週期，以掌控更加嚴格的限制鎖定。隔離等級影響了對SELECT陳述式的鎖定行為，它表示隔離性影響著讀取的資料所使用的鎖定模式。鎖定模式將在本章稍後的〈交易鎖定〉一節中討論。

表19-1隔離等級行為

隔離等級行為
Dirty讀取非可重覆讀取幻像讀取
讀取未認可 Yes Yes Yes
讀取認可 No Yes Yes
可重覆讀取 No No Yes
序列化 No No No

設定隔離等級
　

透過使用T-SQL陳述式或應用程式中的函數，您可以設定整個SQL Server使用者工作階段中使用的隔離等級，還能在查詢中指定鎖定提示以覆蓋該特定交易已設定的隔離等級。鎖定提示將在本章稍後的〈鎖定提示〉一節中討論。要使用T-SQL或在DB-LIB應用程式中設定隔離等級，請使用SET TRANSACTION ISOLATION LEVEL陳述式，並指定四個隔離等級中的一個。語法如下：
```
SET TRANSACTION ISOLATION LEVEL 
  READ UNCOMMITTED
  | READ COMMITTED
  | REPEATABLE READ
  | SERIALIZABLE
GO
```
相關資訊

對於其他類型的應用程式，如使用ODBC、ADO或是OLE-DB，請查閱線上叢書索引中的「ACID屬性」，並選擇「找到的主題」對話方塊中的「調整交易隔離等級」。

一旦您設定了隔離等級，在接下來的交易中，SQL Server工作階段將執行鎖定已確定該隔離等級。使用DBCC USEROPTIONS指令，可以發現SQL Server當前預設的是哪一個隔離等級。該指令的語法僅僅是個指令名稱：
```
DBCC USEROPTIONS
```
這個指令傳回的只是使用者已經設定或是當前處於啟動狀態的選項。如果您沒有設定隔離等級（任其為SQL Server預設等級），當您執行DBCC USEROPTIONS指令時，將看不到隔離等級；但是如果您指定了非預設的等級，就會看到隔離等級。例如，如果您執行下面的陳述式，隔離等級將顯示在DBCC USEROPTIONS的輸出中：
```
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DBCC USEROPTIONS
GO
DBCC USEROPTIONS
```
指令的傳回結果就像這樣：
```
Set Option                Value
-------------------------------------------
textsize                  64512
language                  us_english
dateformat                mdy
datefirst                 7
quoted_identifier         SET
arithabort                SET
ansi_null_dflt_on         SET
ansi_defaults             SET
ansi_warnings             SET
ansi_padding              SET
ansi_nulls                SET
concat_null_yields_null   SET
isolation_level           serializable

(13 row(s) affected)
```
您可以在資料表層級使用鎖定提示來覆蓋掉預設的隔離等級，但是您只能在必需且充分理解它如何影響您的交易的情況下才使用該技術。要想獲得更多關於可用的鎖定提示之細節，請參閱本章稍後的〈鎖定提示〉一節。

耐久性
　

最後一個ACID屬性是耐久性。「耐久性」意味著一旦交易認可，交易的作用將在資料庫中永久保持，即使系統出現故障也不會遺失。SQL Server交易記錄檔和資料庫備份提供了耐久性。如果伺服器中的元件失敗、作業系統失敗或是SQL Server失敗，資料庫將在SQL Server重新啟動時自動回復。SQL Server使用交易記錄檔重新進行受系統崩潰所影響的已認可交易，並復原任何未確認的交易。

如果資料磁碟失敗，資料遺失或損壞，您可以透過資料庫備份和交易記錄檔備份來回復資料庫。如果您的備份計劃夠好，就有辦法讓您從每次的系統失敗中回復。不幸的是，如果您的備份磁碟失敗而遺失了需要用來回復系統的備份，您將無法再回復您的資料庫。要想獲得關於備份、回存資料庫以及交易記錄檔的細節，請參閱第32章和 33章。現在，您已經理解了交易的屬性，讓我們來看看如何開始和終止一個交易。

交易模式
　

交易能以三種模式中的任意一種開始：「自動認可（autocommit）」、「外顯（explicit）」或是「隱含（implicit）」。SQL Server的預設模式是自動認可。

自動認可模式
　

在「自動認可」模式中，每個T-SQL陳述式在完成後都將得到認可－這種模式不需要任何附加的陳述式來控制交易。換句話說，每個交易僅僅由一個T-SQL陳述式組成。當您使用互動式指令執行陳述式，如OSQL或SQL Server Query Analyzer的時候，自動認可模式是很有用的，因為您不必擔心每個交易的明確開始和結束。您知道每個陳述式將被SQL Server作為自己的交易來處理，並且在完成時可以立即被認可。SQL Server的每個連線都會使用自動認可模式，直到您使用 BEGIN TRANSACTION開始一個外顯（Explicit）交易，或是設定了隱含（implicit）模式。一旦外顯交易結束或關閉隱含模式，SQL Server將返回自動認可模式。

外顯模式
　

外顯模式常常用於撰寫應用程式、預存程序、觸發器（triggers）和指令檔中。當您執行一組陳述式來執行任務時，您可能需要決定交易從哪開始、到哪結束，以使整組陳述式成功或者整組的修改都被復原。當您明確指定交易的起始點和結束點時，您就已經在使用外顯模式，該交易就被稱為「外顯交易（explicit transaction）」。使用T-SQL陳述式或API函數，可以指定外顯交易。在這個部分，我們將只討論T-SQL方法；API函數超出了本書的範圍。

相關資訊

要想了解關於使用ADO和OLE-DB的外顯交易的資訊，請查閱線上叢書索引中的「外顯交易」部分，並選擇「找到的主題」對話方塊中的「外顯交易」。請注意，ODBC API不支援外顯交易，而僅支援隱含和自動認可交易。

真實世界使用外顯交易

讓我們來看看什麼情況下我們需要使用外顯交易開始和終止一個任務。假設我們有一個命名為Place_Order的預存程序，它用來處理使用者訂購項目的資料庫任務。該程序的步驟包括選擇使用者的當前帳號資訊、輸入新的訂購ID號碼和訂購的項目、計算含稅的訂購價格、更新客戶帳號餘額以反映總價格，以及檢查該項目是否有庫存。

讓我們來看看什麼情況下我們需要使用外顯交易開始和終止一個任務。假設我們有一個命名為Place_Order的預存程序，它用來處理使用者訂購項目的資料庫任務。該程序的步驟包括選擇使用者的當前帳號資訊、輸入新的訂購ID號碼和訂購的項目、計算含稅的訂購價格、更新客戶帳號餘額以反映總價格，以及檢查該項目是否有庫存。

我們希望這些步驟一起完成或是都不完成，以保持資料庫中資料的一致性。為了達到這一點，我們將處理這些任務的陳述式組合為一個外顯交易。如果我們不組合這些陳述式，我們可能得到不一致的資料。例如，如果在輸入新的訂購數已經執行之後而客戶餘額更新之前，用戶端到伺服器的網路連接被中斷，資料庫中客戶有新的訂單，而客戶的帳號卻沒有費用支出。SQL Server本身會盡可能快速地在每個陳述式完成後認可，在網路斷線時預存程序只完成一半。但是在一個外顯交易中如果定義了步驟，斷線時SQL Server將自動復原整個交易，用戶端可以稍候再連線並再次執行程序。要想了解更多的細節，請參閱本章稍後的〈復原交易〉一節。

如前面所述的例子，當您的任務由幾個步驟組成時，使用外顯交易是很有幫助的，因為無論您是否指定自己的ROLLBACK陳述式，當伺服器出錯時，如出現網路通訊中斷、資料庫或用戶端系統崩潰或死結，SQL Server都將自動復原您的交易。死結將在本章稍後的〈封鎖和死結〉一節中討論。開始交易使用的是BEGIN TRANSACTION陳述式。結束交易指定使用COMMIT TRANSACTION或是ROLLBACK TRANSACTION陳述式。您可以在BEGIN TRANSACTION陳述式中選擇性的為交易指定一個名稱，這樣就可以在COMMIT TRANSACTION或是ROLLBACK TRANSACTION陳述式中按名稱引用至該交易。這三個陳述式的語法如下所示：
```
BEGIN TRAN[SACTION] [tran_name | @tran_name_variable]

COMMIT [TRAN[SACTION] [tran_name | @tran_name_variable]]

ROLLBACK [TRAN[SACTION] [tran_name | @tran_name_variable
      | savepoint_name | @savepoint_name_variable]]
```
認可交易
　

上面曾提到過，一個已認可交易是所有的修改都已成為資料庫永久部分的交易。在交易被認可之前，一份有關交易修改的記錄與認可記錄會被寫入資料庫交易記錄檔。因此，已成為資料庫永久部分的修改將會位在下列兩個位置之一：修改要不是已確實寫到磁碟成為資料庫的永久部分，便是位於資料快取之中。如果系統發生錯誤，交易記錄檔可以藉此讓交易向前復原，交易將不會有任何遺失。

交易使用的所有資源，例如鎖定，都會隨著認可得到釋放。如果每條陳述式都成功，交易就會成功得到認可。下面是一個名為update_state的小型外顯交易，它將所有發行者的publishers資料表中state資料行為NULL值的都更新為XX：
```
USE pubs
GO
BEGIN TRAN update_state

UPDATE publishers SET state = 'XX'
WHERE state IS NULL
COMMIT TRAN update_state
GO
```
如果您執行該交易，您應該看到兩個資料行發生了變化。要將資料表恢復到原始狀態（就像發生了復原而不是認可），請執行下面的交易：
```
USE pubs
GO
BEGIN TRAN undo_update_state

UPDATE publishers SET state = NULL
WHERE state ='XX'
COMMIT TRAN undo_update_state
GO
```
您又可以看到有兩個資料行發生了變化。伴隨著COMMIT TRAN使用的交易名稱update_state和undo_update_state將被SQL Server忽略－它僅僅是幫助程式設計人員確定哪個交易將要被認可。SQL Server在COMMIT開始之前自動認可最新的未認可交易，無論是否指定交易名稱。

巢狀交易
　

SQL Server允許「巢狀交易（nested transactions）」或稱「交易中的交易」。使用巢狀交易時，您應當外顯地認可每個內層交易，那麼SQL Server才會知道交易已經完成，並在外層交易認可時，釋放交易使用的資源。如果資源被鎖定，別的使用者將不能存取那些資源。雖然每個交易都必須包含COMMIT，但要到最外層的交易被成功認可後，SQL Server才真正地認可內層的交易，並釋放內層與外層交易使用的資源。如果最外層的交易認可失敗，內層的交易也不會認可，而且所有的內外交易都將復原。如果最外層交易認可了，所有內層交易都將認可。換句話說，SQL Server基本上忽略了內層巢狀交易的COMMIT陳述式，換之以等待外層交易的最終認可或復原，以決定所有內層交易的完成狀況。（在底下的真實世界範例中會有更進一步的說明。）

此外在巢狀交易裡，如果外層或任何一個內層交易執行了ROLLBACK陳述式，所有的交易都將被復原。在ROLLBACK陳述式中使用內層交易名稱是無效的，如果您已使用，SQL Server將傳回錯誤訊息。您應在該陳述式中使用最外層的交易名稱，根本不使用名稱，或使用一個「儲存點（savepoint）」名稱（儲存點將在下一部分討論）。

真實世界使用巢狀交易

以下是使用預存程序的巢狀交易的例子。預存程序包括一個外顯交易，且此預存程序是從另一個外顯交易中被呼叫。因此，預存程序中的交易成為內層巢狀交易。下面的程式碼顯示了用於建立預存程序的陳述式（為了方便起見，該範例使用了PRINT陳述式而不是真實資料修改陳述式）和呼叫預存程序的交易。
```
USE MyDB
GO
CREATE PROCEDURE Place_Order  --Creates the stored procedure
AS
BEGIN TRAN place_order_tran
PRINT 'SQL Statements that perform order tasks go here'
COMMIT TRAN place_order_tran
GO

BEGIN TRAN Order_tran   --Begins the outer transaction
PRINT 'Place an order'          
EXEC Place_Order        --Calls the stored procedure, which 
                        --begins the inner transaction 
COMMIT TRAN Order_tran  --Commits the inner and outer 
GO			--transactions
```
執行這些程式碼後，您將會看到輸出兩個PRINT陳述式。Place_order_tran交易必須在預存程序中有一個COMMIT陳述式來標記交易的結束，但要到Order_tran交易認可時，才會真正地認可。Place_order_tran是認可還是復原，完全取決Order_tran是否認可。

儘管遇到COMMIT陳述式時SQL Server實際上沒有認可內層交易，但它為每個遇到的COMMIT陳述式更新@@TRANCOUNT系統變數。該變數保持了每個使用者連接的啟動交易數的追蹤資訊。當現在沒有啟動的交易時，@@TRANCOUNT是0。隨著每個交易的開始（使用BEGIN TRAN），@@TRANCOUNT值一個一個遞增，隨著每個交易的認可，@@TRANCOUNT值一個一個遞降。當@@TRANCOUNT達到0，認可最外層的交易。如果在外部或內層交易的某個地方執行了ROLLBACK陳述式，@@TRANCOUNT設定為0。請記住，您應該認可每一個內層交易，那麼@@TRANCOUNT就能夠正確的下降。您可以檢測@@TRANCOUNT的值來確定現在是否存在交易活動。使用SELECT @@TRANCOUNT陳述式，就可以看到@@TRANCOUNT的值。

真實世界使用 @@TRANCOUNT

以下是一個@@TRANCOUNT的例子。假設您有一個由兩個交易組成的巢狀交易，分別是一個內層交易和一個外層交易，就如上個例子一般。在兩個交易都開始之後但在任一交易認可之前，@@TRANCOUNT的值是2。由於@@TRANCOUNT現為非0值，所以最外層的交易不能認可。當內層交易認可後，@@TRANCOUNT值下降到1。如果您對最外層交易執行COMMIT陳述式，@@TRANCOUNT下降到0，外層交易將得到認可。

下面的程式碼與上一個例子相仿，不過我們在其中加進擷取@@TRANCOUNT值的陳述式：
```
USE MyDB
GO
DROP PROCEDURE Place_Order
GO
CREATE PROCEDURE Place_Order  --Creats the stored procedure.
AS
BEGIN TRAN place_order_tran   --TRANCOUNT is incremented.
PRINT 'SQL Statements that perform order tasks go here'
SELECT @@TRANCOUNT as TRANCOUNT_2
COMMIT TRAN place_order_tran  --TRANCOUNT is decremented.
GO

SELECT @@TRANCOUNT as TRANCOUNT_initial
BEGIN TRAN Order_tran         --TRANCOUNT is incremented.
PRINT 'Place an order'
SELECT @@TRANCOUNT as TRANCOUNT_1
EXEC Place_Order       --Calls the stored procedure,
                       --which begins the inner transaction.
SELECT @@TRANCOUNT as TRANCOUNT_3
COMMIT TRAN Order_tran        --TRANCOUNT is decremented.
SELECT @@TRANCOUNT as TRANCOUNT_4
GO
```
如果您執行這些陳述式，您將看到一系列的@@TRANCOUNT值，先後為0、1、2、1、0。

說明

對於使用BEGIN TRAN的外顯交易，您必須外顯地認可每個交易。當您使用巢狀交易時，SQL Server會到所有的內層交易都得到認可後，才能夠認可最外層交易。

隱含模式
　

在「隱含模式（implicit mode）」中，當使用某些特定T-SQL陳述式時，交易會自動開始並持續到以COMMIT或ROLLBACK陳述式外顯地結束交易。如果結束陳述式沒有指定，交易將在使用者斷線時復原。下面的陳述式將在隱含模式裡開始新交易：
ALTER TABLE
　
CREATE
　
DELETE
　
DROP
　
FETCH
　
GRANT
　
INSERT
　
OPEN
　
REVOKE
　
SELECT
　
TRUNCATE TABLE

隔離等級	行為
Dirty讀取	非可重覆讀取	幻像讀取
讀取未認可	Yes	Yes	Yes
讀取認可	No	Yes	Yes
可重覆讀取	No	No	Yes
序列化 No	No	No

UPDATE
　

當這些陳述式之一用於開始一個隱含交易時，交易就將繼續直到明確的指定結束，即使在該交易中執行這些陳述式中的另一個。在交易明確認可或復原之後，下次再執行這些陳述式之一，又會開始一個新的交易。該程序將持續到隱含模式被關閉為止。

要想設定隱含交易模式為ON，您可以使用下面的T-SQL指令：

SET IMPLICIT_TRANSACTIONS {ON | OFF}

ON啟動該模式，OFF關閉該模式。當隱含模式關閉後，接下來就會使用自動認可模式。

當您執行的指令檔將會修改到那些需要在交易中保護的資料時，隱含模式是很有用的。您可以在指令檔開始時開啟隱含模式，執行必要的修改，然後在結束時關閉該模式。為了避免並行性問題，在資料修改之後和瀏覽資料之前關閉隱含模式。因為如果一次認可之後的下一個陳述式是SELECT陳述式，在隱含模式中就會開始一個新的交易，而且直到SELECT認可後資源才會得到釋放。

交易復原
　

有兩種方式可以令交易復原：一是由SQL Server自動復原，另一則是手動地程式復原。在特定情況下，SQL Server會為您復原交易。不過要使您的程式能有邏輯的一致性，您必須在必要時呼叫ROLLBACK陳述式。讓我們看一下這兩個方法的更多細節。

自動復原
　

一如本章之前所提到的，如果交易因為嚴重的錯誤而失敗，例如交易已開始執行卻發生網路連線中斷的情況，或是用戶端應用程式或電腦出現錯誤，SQL Server將會自動地復原交易。復原將會回復交易造成的所有修改，並釋放交易用到的所有資源。

如果一個「執行時期（run-time）」陳述式引發錯誤，例如條件約束或規則衝突，預設情況下SQL Server自動復原的僅僅是出錯的陳述式。要變更這種行為，您可以使用SET XACT_ABORT陳述式。啟動該選項指示SQL Server自動復原執行時期錯誤事件中的交易。該技術有時很有用，例如，當因違反了某一外來鍵條件約束而造成您的交易中某一陳述式失敗；而且因為該陳述式失敗，您不希望別的陳述式得以通過時，可以設定XACT_ABORT。在預設情況下，XACT_ABORT設為OFF。

在伺服器重新回復的期間，SQL Server也會使用自動復原。舉例來說，如果交易正在執行時，發生了足以讓系統重置（reboot）的嚴重故障，當SQL Server重新啟動時，它會自動復原（automatic recovery）。資料庫自動復原（automatic recovery）包括讀取交易記錄檔以對尚未寫入磁碟的交易重新進行認可，並對錯誤發生時遺失（尚未被認可）的交易進行復原（roll back）。

程式復原
　

使用ROLLBACK敘述，您可以明確地指定交易中復原的發生點。ROLLBACK陳述式終結交易並恢復交易所作的所有變更。如果您在交易中間引發復原，交易的剩餘部分將被忽略。舉個例子，如果交易是一個整體的預存程序，而ROLLBACK在這個預存程序中發生，那麼預存程序將會復原，而且系統將繼續處理批次檔中的下一個陳述式。

如果您想根據SELECT敘述傳回的資料列數來決定是否復原交易，那麼應該使用@@ROWCOUNT系統變數，@@ROWCOUNT含有查詢傳回的資料列數或受更新、刪除所影響的資料列數。如果指定的資料列數並不符合，但您只是單純地需要找出指定狀況下某一列或數列是否存在，可將IF EXISTS陳述式與SELECT陳述式一起使用。此陳述式並不會傳回任何資料列，只會傳回「TRUE」或「FALSE」。如果結果是「TRUE」，接下來的陳述式將會執行；如果傳回「FALSE」，接下來的陳述式將不會執行。IF EXISTS陳述式也可使用ELSE子句。

以下是一個使用IF EXISTS...ELSE子句的例子，假設下面的交易按兩種版稅稅率（16% 和15%）更新了roysched資料表中的版稅總數，但是如果被更新的兩種版稅稅率都不存在，就不會執行任何UPDATE命令。交易使用ROLLBACK來確認這個結果。

BEGIN TRAN update_royalty       --Begin the transaction.
USE pubs
IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, 
roysched
WHERE titles.title_id = roysched.title_id
AND roysched.royalty = 16)
UPDATE roysched SET royalty = 17 WHERE royalty = 16  --13 rows exist.
ELSE
ROLLBACK TRAN update_royalty    --ROLLBACK is not executed.

IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, 
roysched 
WHERE titles.title_id = roysched.title_id 
AND roysched.royalty = 15)      --No rows exist.
BEGIN
UPDATE roysched SET royalty = 20 WHERE royalty = 15
COMMIT TRAN update_royalty
END
ELSE                            --ROLLBACK is executed.
ROLLBACK TRAN update_royalty
GO

在這個交易中，第一個IF EXISTS (SELECT...) 陳述式找到了一些存在的資料列，因此執行了第一個UPDATE命令（顯示出13個資料列有影響）。第二個SELECT敘述傳回了0資料列，因此沒有執行第二個UPDATE命令，而是執行了交易復原ROLLBACK TRANupdate_royalty。由於ROLLBACK將所有的修改回復至交易開始狀態，第一個UPDATE也被復原。如果您再次執行SELECT敘述，您看到的仍是稅率設為16的13列。將稅率設為17的更新，因ROLLBACK陳述式而被復原。

說明

在這個交易中使用了一些新的關鍵字：IF、ELSE、BEGIN和END。這些關鍵字將在第20章詳細討論。

交易認可後就不能再復原。（請記住，直到外層交易認可後，內層交易才真正得到認可。）單一交易的情況下，必須在COMMIT之前呼叫ROLLBACK才會出現外顯復原。巢狀交易情況下，一旦最外層交易認可（因此內層交易也得到認可），就不能再復原任何交易。我們已經提到過，您不能只復原內層交易，而是必須復原整個交易。因此，如果您在ROLLBACK敘述中包含了交易名稱，要確定使用最外層交易名稱以避免混淆，否則SQL Server將會傳來一個錯誤訊息。您可使用「儲存點（savepoint）」，保留一些修改，以避免復原整個交易。

儲存點
　

您可以透過使用「儲存點（savepoint）」來避免復原整個交易，儲存點可以從交易中的某一點而不是從交易的開始點復原。從開始到儲存點的所有修改將保持有效並且不會被復原，但在儲存點之後至ROLLBACK陳述式之間執行的陳述式將被復原。在ROLLBACK之後的陳述式將繼續執行。如果沒有指定儲存點而復原交易時，所有的修改將返回到交易的開始狀態。要注意當交易復原至儲存點時，SQL Server並不會釋放已鎖定的資源，被所定的資源要到交易認可或整個交易都復原時，才會被釋放出來。

要指定儲存點，可用以下陳述式：

SAVE TRAN[SACTION] {savepoint_name | @savepoint_name_variable}

儲存點在交易中的位置應該是您想要復原到的位置。要想復原到儲存點，請使用有儲存點名稱的ROLLBACK TRAN陳述式，如下所示：

ROLLBACK TRAN savepoint_name

在ROLLBACK陳述式之後，您可以使用更多的T-SQL敘述來繼續交易。在第一個ROLLBACK陳述式之後，要記得必須包含至少一個COMMIT敘述或另一個ROLLBACK敘述，如此才能完成整個交易。

相關資訊

要想了解關於儲存點的更多資訊以及更佳的使用範例，請查閱線上叢書索引中的「儲存點」，並選擇「找到的主題」對話方塊中的「Save Transaction」。

交易鎖定
　

SQL Server使用一個被稱為「鎖定（lock）」的物件來防止多個使用者同時對資料庫作修改，並防止使用者讀取被其他使用者變更的資料。鎖定有助於確保交易和資料的邏輯完整性。鎖定由SQL Server軟體在內部管理，並且可以每個使用者連線為基礎來獲得。當使用者獲得（或擁有）對資源的鎖定時，鎖定就象徵著使用者對該資源擁有使用權。可以被使用者鎖定的資源包括資料列、資料頁、一個「範圍（extent）」（八個連續的頁面）、資料表或整個資料庫。例如，如果使用者持有對某個資料分頁的鎖定，另一個使用者就不能對同一分頁執行操作，否則會影響擁有鎖定的使用者的作業。因此，使用者不能更新當前被另一使用者鎖定並正在讀取的資料分頁。如果鎖定已被某一使用者持有，另一使用者就不能獲得與其衝突之鎖定。例如，兩個使用者不能同時都擁有更新同一分頁的鎖定。同一個鎖定不能被超過一個使用者所使用。

按照使用者動作，SQL Server的鎖定管理可以自動獲得和釋放鎖定。DBA或程式設計人員不需要執行任何動作來管理鎖定。然而，當在資料庫上執行特定查詢或修改時，您可以使用程式化提示來指示SQL Server要獲得鎖定的種類；這些將在本章稍後的〈鎖定提示〉一節中討論。

在這個部分，我們將看到幾種不同層級的鎖定以及鎖定模式。但首先，讓我們先了解一些增進SQL Server效能的鎖定管理功能。

鎖定管理功能
　

SQL Server支援資料列層級的鎖定－即在資料分頁或索引分頁的一列上獲得鎖定的能力，資料列層級鎖定是SQL Server中可獲得鎖定的最小資料點層級，這個較低層級的鎖定提供了許多Online Transaction Processing（OLTP）應用程式更大的並行能力。當您在資料表和索引中執行資料列插入、更新和刪除時，資料列層級鎖定特別有用。

除了資料列層級鎖定特性外，SQL Server讓鎖定設定的管理更為簡化。您不再需要手動設定鎖定配置參數來確定SQL Server可用之鎖定的數目。在預設上，如果需要更多的鎖定，SQL Server可以動態分配更多，直到SQL Server記憶體的上限。如果鎖定已經分配但是不再使用，SQL Server將取消對它們的分配。SQL Server還可以針對資源最佳化選擇獲得鎖定的類型，通常資料列層級鎖定用於插入、更新和刪除；分頁層級鎖定則用於資料表掃描。下一節將介紹更多鎖定層級的細節。

相關資訊

鎖定設定選項的更多資訊請參閱第30章。

鎖定層級
　

鎖定可以按資源的數目取得；資源的類型決定鎖定的資料點層級。表19-2列出了SQL Server能夠鎖定的資源，按鎖定資料點的層級從小到大排序。

表19-2 可鎖定的資源

資源	鎖定類型	說明
RID（ROW ID）	資料列層級	用來鎖定資料表內的單一資料列。
索引鍵	資料列層級	用來鎖定索引內的單一資料列。
分頁	資料頁層級	用來鎖定資料表或索引內的單一8KB分頁。
範圍	範圍層級	用來鎖定一個範圍（extent），八個資料頁或索引頁的連續群組。
資料表	資料表層級	用來鎖定整個資料表。
資料庫	資料庫層級	用來鎖定整個資料庫。

隨著資料點層級的遞增，並行性就下降。例如，鎖定整個資料表將封鎖其他使用者存取該資料表。不過由於使用的鎖定較少，系統消耗也會下降。隨著資料點層級的遞減－例如在分頁層級或資料列層級鎖定－並行性就上升，因為允許更多的使用者同時存取資料表中不同的分頁或資料列。這樣，系統消耗也會上升，因為許多資料列或分頁被個別存取時將需要更多的鎖定。

SQL Server自動選擇適合該任務的鎖定類型，以最小化鎖定的系統消耗。SQL Server也依照每個交易要鎖定的資源來自動決定鎖定模式；下面將討論這些模式。

鎖定模式
　

「鎖定模式（lock mode）」指定了並行使用者（或並行交易）可以如何使用資源。在這些模式的任一模式中，可以獲得每種類型的鎖定。可用的鎖定模式有六種：「共用（Shared）」、「更新（Update）」、「獨占（Exclusive）」、「意圖（Intent）」、「結構描述（Schema）」和「大量更新（Bulk Update）」。

共用
　

共用鎖定模式用於唯讀作業，例如使用SELECT陳述式時執行的操作。該模式允許並行交易同時讀取同一資源，但並不允許任何交易修改該資源。一旦讀取完成，共用鎖定就會被釋放，除非隔離等級被設為可重覆讀取或更高等級，或是一個覆蓋該行為的鎖定提示已被指定於交易裡。

更新
　

當資源更新時使用更新鎖定模式。某一時刻對某一資源只能有一個交易可以獲得更新鎖定。如果交易確實作了修改（例如，找到符合搜尋條件的資料列並進而修改），更新鎖定就轉為獨占鎖定（下面將說明）；否則，轉為共用鎖定。

獨占
　

獨占鎖定用於修改資料的操作，如更新、插入和刪除。當交易對某一資源持有獨占鎖定，沒有其他交易可以讀取或修改該資源。這種鎖定模式防止並行使用者同時更新同一資料，因為這樣可能會造成無效資料。

意圖
　

意圖鎖定模式用於建立一個鎖定階層架構。例如，資料表層級的意圖鎖定說明SQL Server意圖在該資料表中取得一個分頁、幾個分頁或幾個資料列的鎖定。如果交易需要取得某一資源的獨占鎖定，SQL Server首先會檢查以確定那些資源上是否存在意圖鎖定。如果持有意圖鎖定的交易正在等待那些資源，第二個交易就不能獲得獨占鎖定。如果沒有交易持有意圖鎖定且正在等待那些資源，交易才能取得該資源的獨占鎖定。意圖鎖定模式有三種：