悲觀鎖定

資料來源


在实际的多用户并发访问的生产环境里边，我们经常要尽可能的保持数据的一致性。而其中最典型的例子就是我们从表里边读取数据，检查验证后对数据进行修改，然后写回到数据库中。在读取和写入的过程中，如果在多用户并发的环境里边，其他用户已经把你要修改的数据进行了修改是非常有可能发生的情况，这样就造成了数据的不一致性。解决这样的办法，SQL Server提出了乐观锁定和悲观锁定的概念，下边我以一个实例来说明如何使用乐观锁定和悲观锁定来解决这样的问题。

/* 建立测试表:Card,代表一个真实的卡库,供用户注册.用户要从里边选出一个未使用的卡，也就是F_Flag=0的卡，给用户注册：更新 F_Name,F_Time,F_Flag字段. 如果出现两个用户同时更新一张卡的情况，是不能容忍的，也就是我们所说的数据不一致行。*/

create table Card(F_CardNO varchar(20),F_Name varchar(20),F_Flag bit,F_Time datetime) Go insert Card(F_CardNo,F_Flag) select '1111-1111',0 insert Card(F_CardNo,F_Flag) select '1111-1112',0 insert Card(F_CardNo,F_Flag) select '1111-1113',0 insert Card(F_CardNo,F_Flag) select '1111-1114',0 insert Card(F_CardNo,F_Flag) select '1111-1115',0 insert Card(F_CardNo,F_Flag) select '1111-1116',0 insert Card(F_CardNo,F_Flag) select '1111-1117',0 insert Card(F_CardNo,F_Flag) select '1111-1118',0 insert Card(F_CardNo,F_Flag) select '1111-1119',0 insert Card(F_CardNo,F_Flag) select '1111-1110',0 Go

-- 下边是我们经常使用的更新方案如下:

declare @CardNo varchar(20) Begin Tran -- 选择一张未使用的卡 select top 1 @CardNo=F_CardNo from Card where F_Flag=0 -- 延迟50秒，模拟并发访问. waitfor delay '000:00:50' -- 把刚才选择出来的卡进行注册. update Card set F_Name=user, F_Time=getdate(), F_Flag=1 where F_CardNo=@CardNo commit

问题:如果我们在同一窗口执行同一段代码，但是去掉了waitfor delay子句.两边执行完毕后 我们发现尽管执行了两次注册，但是只注册了一张卡，也就是两个人注册了同一张卡.

悲观锁定解决方案

-- 我们只要对上边的代码做微小的改变就可以实现悲观的锁定.

declare @CardNo varchar(20) Begin Tran -- 选择一张未使用的卡 select top 1 @CardNo=F_CardNo from Card with (UPDLOCK) where F_Flag=0 -- 延迟50秒，模拟并发访问. waitfor delay '000:00:50' -- 把刚才选择出来的卡进行注册. update Card set F_Name=user, F_Time=getdate(), F_Flag=1 where F_CardNo=@CardNo commit

鎖定簡介

資料來源



ROWLOCK 讀取或修改資料時使用資料列層級鎖定。這些鎖定會依適當情況被取得及釋放。

PAGLOCK 讀取或修改資料時使用頁面層級鎖定。這些鎖定會依適當情況被取得及釋放。

TABLOCK 讀取或修改資料時使用資料表鎖定。此鎖定會被保留到陳述式結束為止。

DBLOCK 讀取或修改資料時使用資料庫鎖定。此鎖定會被保留到陳述式結束為止。

LOCKMODES

UPDLOCK 在讀取資料表時使用更新鎖定，而非共用鎖定，並使用保留鎖定直到陳述式或交易結束為止。UPDLOCK 可讓您不會在讀取資料時封鎖其他讀取器，且稍後更新資料時，也可確信自從您上次讀取之後，資料並未發生變更。

XLOCK 在讀取資料表時使用獨佔鎖定，而非共用鎖定，並使用保留鎖定直到陳述式或交易結束為止。

持續期間

HOLDLOCK 使用保留鎖定將鎖定保留至交易完成為止，而不會在所需資料表、資料列或資料頁已不再需要時立即釋放鎖定。

NOLOCK 不會發出任何鎖定。這是 SELECT 作業的預設值。此鎖定不適用於 INSERT、UPDATE 及 DELETE 陳述式。

範例:

begin tran begin tran select * from test1 with (xlock) where t1 = 1 '鎖定t1=1的資料

rollback

begin tran begin tran select * from test1 with (tablock,xlock) '鎖定資料表Test1

rollback

鎖定

資料來源

在 說明SQL Server的鎖定原理之前，首先來看一個基本的問題，為什麼要使用鎖定？我們可以從逆向思考的方式來回答這個問題，也就是不使用鎖定，會出現什麼問題， 特別是當多人同時存取資料時，若不使用鎖定，可能會發生lost update、dirty read、nonrepeatable read與phantoms read等問題。這也是為什麼要使用鎖定的主要原因。

談 到鎖定，就不得不談到交易處理(transaction)。所謂交易處理是指在一個工作單元中，執行的一系列資料操作。ATM跨行轉帳就是一個很典型的例 子，這個工作單元中包含從轉出帳戶扣款與將金額存入轉入帳號兩個操作，這兩個操作必須有一致的結果，不是全部成功，確認最終狀態；就是全部失敗，回復原始 狀態，沒有部分成功，部分失敗的情況，只有轉出沒有轉入或只有轉入沒有轉出都是不允許的。也就是交易處理具有不可分割性(Atomicity)、一致性 (Consistency)、隔離性(Isolation)以及持久性(Durability)等特性。而鎖定機制便是為了實現交易的隔離性，好像此時資 料庫系統是專屬你一個人的。

鎖定的範圍
在SQL Server可以鎖定哪些資源？依據鎖定的範圍可以分成RID 列級鎖定、Key 索引鎖定、Page 頁級鎖定、Extent 擴展鎖定(每8個Page為1個extent，用於SQL Server儲存空間的分配)、Table 表級鎖定，以及Database 資料庫級鎖定。

鎖 定的範圍越小，耗用的資源越大；鎖定的範圍越大，耗用的資源越小。列級鎖定可以增加同時線上(concurrent)數量，但卻增加資源花費；頁級或表級 鎖定則正好相反。SQL Server會衡量目前的concurrent數量與系統資源，自動管理鎖定的範圍，同時為了獲得最佳的鎖定效能，SQL Server提供一種動態鎖定的機制，也就適當列級鎖定耗用的資源達到某個程度時，SQL Server會自動將列級鎖定升級成頁級或表級鎖定。

鎖定的類型
SQL Server鎖定可以分成基本鎖定與特殊鎖定兩大類。基本鎖定又可分成共用鎖定(shared lock)與獨占鎖定(exclusive lock) 。一般而言，當Select資料時會使用共用鎖定，Insert、Update和Delete資料時會使用獨佔鎖定。特殊鎖定又可分成意圖鎖定 (Intent lock)、更新鎖定(Update lock)、綱要鎖定(Schema lock)與大量新鎖定(Bulk update lock)。接下來，對常用的鎖定類型，簡單說明如下：

共用鎖定
當讀取資料時，SQL Server會使用共用鎖定，即使尚未結束，其他交易也可以獲得共用鎖定，也就是說多個交易可以同時讀取table、page、key和row。

獨佔鎖定
當修改資料時，SQL Server會使用獨佔鎖定。在交易結束之前，其他交易的鎖定請求都會被拒絕。一個資源只能有一個獨佔鎖定，當一個交易對某個資源進行獨占鎖定時，其他交易無法讀取該資源，由此可知，這種鎖定會限制了同時線上數量。

更新鎖定是共用鎖定與獨占鎖定的混合體。在修改之前會使用共用鎖定，其他交易可以讀取被鎖定的資料，但不可以修改。一旦開始修改時，就變成了獨占鎖定，其他交易無法讀取和更新被鎖定的資料，直到交易結束。

一個鎖定可以與另一個鎖定同時使用，這種特性稱為鎖定的相容性。相反的，若一個交易正在操作而鎖定資料，另一個交易必須等前一個交易結束後，才能繼續，則稱為不相容。相容性越好，表示支援的同時線上數量越大。哪些鎖定式相容的？哪些鎖定又是不相容的？一般的原則如下：
共用鎖定可以與獨占鎖定之外的其他鎖定相容。多個交易可以在相同資料上獲得共用鎖定，但沒有一個交易可以在已經獲得共用鎖定的資料上獲得獨佔鎖定。
獨占鎖定不可以其他鎖定相容。
更新鎖定只能與共用鎖定相容。

我們可以使用交易的隔離級別來設定鎖定的策略，以防止前面提到多人存取所可能出現的問題。SQL Server提供下列4種交易隔離級別，其特性簡單說明如下。
read uncommitted 會出現dirty read、non-repeatable read與phantoms等問題。
read committed 會出現non-repeatable read與phantoms等問題。
repeatable read 會出現phantoms的問題。
serializable 無

若 未指定SQL Server預設使用read committed。在SQL Server 2005，提供了另外兩個基於資料版本的隔離級別，snapshot與Read Committed using Statement-level Snapshot(RCSI)，使得讀取與更新的操作不會互相影響，加快交易的執行速度。

除 了隔離級別的鎖定策略之外，您也可以使用鎖定提示來控制資料表的鎖定行為，以及覆蓋目前交易的隔離級別的鎖定策略。當隔離級別的鎖定策略與鎖定提示相衝突 時， SQL Server是採用級別越小越優先的處理原則。例如，使用serializable隔離級別，若在交易中使用with (nolock)讀取資料，SQL Server會允許其他交易讀取資料。可用的表級鎖定提示摘要如下：
holdlock：當交易開始之後，交易結束之前，不允許其他交易讀取被holdlock的資料。
nolock：不使用任何的鎖定。
使用rowlock、paglock，tablock與tablockx來指定資料表的鎖定類型和大小。例如使用tablockx表示當讀取資料列時，在資料列所在的資料表上使用獨占索定。
readpast：忽略鎖定的資料列
updlock：當讀取資料使用更新鎖定，而不是預設的共用鎖定。當交易尚未結束前，不允許其他交易更新被updlock的資料，可避免non-repeatable read。

您可以根據個別的需要來組合這些鎖定提示，以獲得更加靈活、更有彈性的應用。例如，with (paglock, holdlock)表示在資料列所在的資料頁上使用hold lock。