SQL Server數據表提示NOLOCK和READPAST

對資料庫中的資料進行讀操作或修改時，資料庫引擎使用專門的控制類型來保持資料庫的完整性，稱為鎖機制。鎖機制通過確保包含在一個事務中的資料庫記錄在該事務提交之前不能被其他事務修改來保證資料庫的一致性。

　　 在設計資料庫應用時，你應該記住各種不同類型的鎖及事務發生的不同隔離級別。通常情況下，SQL Server默認方式能夠很好地完成你要使用的功能，不過，有些時候利用SQL語句在資料表上手工添加關於鎖是如何應用的提示資訊將是十分有用的。


　　 本文主要介紹了兩種資料表提示：NOLOCK和READPAST。我們將建立一個資料表用作例子中的查詢資料表。執行列表A中的腳本建立一個SalesHistory資料表並添加一些資料。

　　 列表A:

IF OBJECT_ID('SalesHistory')>0     

DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory]
(     
      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,     
      [Product] [char](150) NULL,     
            [SaleDate] [datetime] NULL,     
            [SalePrice] [money] NULL
)

GO

DECLARE @i SMALLINT

SET @i = 1


WHILE (@i <=100)

BEGIN                 
      INSERT INTO SalesHistory              
      (Product, SaleDate, SalePrice)                 
      VALUES     
      ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))     


      INSERT INTO SalesHistory               
      (Product, SaleDate, SalePrice)     
      VALUES            
      ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                 


      INSERT INTO SalesHistory                 
      (Product, SaleDate, SalePrice)         
      VALUES            
      ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                 


      SET @i = @i + 1

END
GO

　　 NOLOCK

　　 該資料表提示，也稱為READUNCOMMITTED，只能用於SELECT語句。NOLOCK表明沒有對資料表添加共用鎖以阻止其他事務對資料表資料的修改。

　　 該語句的好處是它可以使資料庫引擎不用在處理查詢中的上鎖問題，可以提高併發性並改善資料庫性能，因為資料庫引擎不用在維護共用鎖的使用問題。存在的問題是因為該語句不能處理要讀取的資料表的所有鎖，所以一些“髒資料”或未被提交的資料潛在的可能被讀取。


　　 如果某個事務被滾回，那麼應用了NOLOCK連接的資料讀取操作將可以讀取未提交的資料。這種類型的讀取導致處理的不一致性會帶來很多問題。這是你使用NOLOCK時應該瞭解的技巧。

　　 作為一個負面影響，NOLOCK查詢還可能帶來讀取“幻影”資料或讀取在一個資料庫讀取事務中可以獲得的但在另一個事務中可能被滾回的資料的風險。（我將在本系列文章的第二部分對這個負面影響進行詳細說明。）

　　 下面的例子展示了NOLOCK如何工作以及髒資料讀取是如何產生的。在下面的腳本中，我用一個事務在SalesHistory資料表中插入一條記錄。


BEGIN TRANSACTION
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', GETDATE(), 500)

　　 這個事務仍舊是開放的，這意味著仍可以對插入資料表的記錄上鎖以阻止其他操作。在一個新的查詢視窗中，運行下面的腳本，該腳本使用NOLOCK資料表提示返回SalesHistory資料表中的記錄數。


SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

　　 返回記錄數值為301。因為對SalesHistory資料表插入記錄的事務還沒有提交，所以我們可以撤銷它。我通過使用下面的語句將事務滾回：

ROLLBACK TRANSACTION

　　 該語句從SalesHistory資料表中刪除前面插入的記錄。現在我們運行前面運行的同樣的SELECT語句。

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

　　 這次返回記錄數的值為300。我第一次查詢讀記錄的事務還沒有提交，這就是一個髒資料讀取。


　　 READPAST

　　 這是一個比NOLOCK較少使用的資料表提示。這個提示指明資料庫引擎返回結果時忽略加鎖的行或資料頁。

　　 這個資料表提示的優點和NOLOCK一樣，在處理查詢時不會發生阻塞。此外，讀髒資料並不會出現在READPASTA中，因為不會返回鎖定的記錄。這個語句的缺點是，因為不返回鎖定的記錄，所以很難確定結果集或修改語句是否包含所有必須的記錄。在你的應用中可能需要添加一些邏輯來確保最終包含所有必須的記錄。


　　 READPAST資料表提示的例子和NOLOCK的例子類似。我將使用一個事務來更新SalesHistory資料表中的一個記錄。

BEGIN TRANSACTION
UPDATE TOP(1) SalesHistory
SET SalePrice = SalePrice + 1

　　 因為我沒有提交或回滾這個事務，所以添加在更新記錄上的鎖仍舊有效。在一個新的查詢編輯視窗中，運行下面的腳本，該腳本對SalesHistory資料表使用READPAST統計表中的記錄數。


SELECT COUNT(*)
FROM SalesHistory WITH(READPAST)

　　 最初SalesHistory資料表中包含300條記錄，UPDATE語句正鎖定表中一條記錄，所以上面使用READPAST的腳本返回結果為299條記錄，這說明我要更新的記錄被鎖定，所以被REASPAST提示忽略。