一 關(guān)于鎖的基礎(chǔ)知識(shí)

(一). 為什么要引入鎖

當(dāng)多個(gè)用戶同時(shí)對(duì)數(shù)據(jù)庫的并發(fā)操作時(shí)會(huì)帶來以下數(shù)據(jù)不一致的問題:

◆丟失更新

A,B兩個(gè)用戶讀同一數(shù)據(jù)并進(jìn)行修改,其中一個(gè)用戶的修改結(jié)果破壞了另一個(gè)修改的結(jié)果,比如訂票系統(tǒng)

◆臟讀

A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因?yàn)槟承┰蛉∠藢?duì)數(shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時(shí)B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致

◆不可重復(fù)讀

A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時(shí)A用戶再讀取數(shù)據(jù)時(shí)發(fā)現(xiàn)前后兩次的值不一致

并發(fā)控制的主要方法是封鎖,鎖就是在一段時(shí)間內(nèi)禁止用戶做某些操作以避免產(chǎn)生數(shù)據(jù)不一致

(二) 鎖的分類

◆鎖的類別有兩種分法：

1. 從數(shù)據(jù)庫系統(tǒng)的角度來看：分為獨(dú)占鎖（即排它鎖），共享鎖和更新鎖

MS-SQL Server 使用以下資源鎖模式。

鎖模式 描述

共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如 SELECT 語句。

更新 (U) 用于可更新的資源中。防止當(dāng)多個(gè)會(huì)話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見形式的死鎖。

排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會(huì)同時(shí)同一資源進(jìn)行多重更新。

意向鎖 用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

架構(gòu)鎖 在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型為：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。

大容量更新 (BU) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時(shí)使用。

◆共享鎖

共享 (S) 鎖允許并發(fā)事務(wù)讀取 (SELECT) 一個(gè)資源。資源上存在共享 (S) 鎖時(shí)，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享 (S) 鎖，除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享 (S) 鎖。

◆更新鎖

更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它 (X) 鎖。如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排它 (X) 鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排它鎖與其它事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個(gè)事務(wù)試圖獲取排它 (X) 鎖以進(jìn)行更新。由于兩個(gè)事務(wù)都要轉(zhuǎn)換為排它 (X) 鎖，并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。

若要避免這種潛在的死鎖問題，請(qǐng)使用更新 (U) 鎖。一次只有一個(gè)事務(wù)可以獲得資源的更新 (U) 鎖。如果事務(wù)修改資源，則更新 (U) 鎖轉(zhuǎn)換為排它 (X) 鎖。否則，鎖轉(zhuǎn)換為共享鎖。

◆排它鎖

排它 (X) 鎖可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問。其它事務(wù)不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)

◆意向鎖

意向鎖表示 SQL Server 需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級(jí)的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享 (S) 鎖。在表級(jí)設(shè)置意向鎖可防止另一個(gè)事務(wù)隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因?yàn)?SQL Server 僅在表級(jí)檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個(gè)表。

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

鎖模式 描述

意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的部分（而不是全部）底層資源。

意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務(wù)的意向是修改層次結(jié)構(gòu)中的部分（而不是全部）底層資源。IX 是 IS 的超集。

與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發(fā) IS 鎖。例如，表的 SIX 鎖在表上放置一個(gè) SIX 鎖（允許并發(fā) IS 鎖），在當(dāng)前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個(gè)資源在一段時(shí)間內(nèi)只能有一個(gè) SIX 鎖，以防止其它事務(wù)對(duì)資源進(jìn)行更新，但是其它事務(wù)可以通過獲取表級(jí)的 IS 鎖來讀取層次結(jié)構(gòu)中的底層資源。

◆獨(dú)占鎖：

只允許進(jìn)行鎖定操作的程序使用，其他任何對(duì)他的操作均不會(huì)被接受。執(zhí)行數(shù)據(jù)更新命令時(shí)，SQL Server會(huì)自動(dòng)使用獨(dú)占鎖。當(dāng)對(duì)象上有其他鎖存在時(shí)，無法對(duì)其加獨(dú)占鎖。

共享鎖：共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶無法修改它，在執(zhí)行Select時(shí)，SQL Server會(huì)對(duì)對(duì)象加共享鎖。

◆更新鎖：

當(dāng)SQL Server準(zhǔn)備更新數(shù)據(jù)時(shí)，它首先對(duì)數(shù)據(jù)對(duì)象作更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以讀取。等到SQL Server確定要進(jìn)行更新數(shù)據(jù)操作時(shí)，他會(huì)自動(dòng)將更新鎖換為獨(dú)占鎖，當(dāng)對(duì)象上有其他鎖存在時(shí)，無法對(duì)其加更新鎖。

2. 從程序員的角度看：分為樂觀鎖和悲觀鎖。

◆樂觀鎖：完全依靠數(shù)據(jù)庫來管理鎖的工作。

◆悲觀鎖：程序員自己管理數(shù)據(jù)或?qū)ο笊系逆i處理。

MS-SQLSERVER 使用鎖在多個(gè)同時(shí)在數(shù)據(jù)庫內(nèi)執(zhí)行修改的用戶間實(shí)現(xiàn)悲觀并發(fā)控制

三 鎖的粒度

鎖粒度是被封鎖目標(biāo)的大小,封鎖粒度小則并發(fā)性高,但開銷大,封鎖粒度大則并發(fā)性低但開銷小

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或數(shù)據(jù)庫獲取鎖

資源 描述

RID 行標(biāo)識(shí)符。用于單獨(dú)鎖定表中的一行。

鍵 索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍。

頁 8 千字節(jié) (KB) 的數(shù)據(jù)頁或索引頁。

擴(kuò)展盤區(qū) 相鄰的八個(gè)數(shù)據(jù)頁或索引頁構(gòu)成的一組。

表 包括所有數(shù)據(jù)和索引在內(nèi)的整個(gè)表。

DB 數(shù)據(jù)庫。

SQL Server 提供以下的鎖級(jí)別:

DATABASE  -- 無論何時(shí)當(dāng)一個(gè)SQL Server 進(jìn)程正在使用除master以外的數(shù)據(jù)庫時(shí)，Lock Manager為該進(jìn)程授予數(shù)據(jù)庫級(jí)的鎖。數(shù)據(jù)庫級(jí)的鎖總是共享鎖，用于跟蹤何時(shí)數(shù)據(jù)庫在使用中，以防其他進(jìn)程刪除該數(shù)據(jù)庫，將數(shù)據(jù)庫置為脫機(jī)，或者恢復(fù)數(shù)據(jù)庫。注意，由于master和tempdb數(shù)據(jù)庫不能被刪除或置為脫機(jī)，所以不需要在它們之上加鎖。
FILE -- 文件級(jí)的鎖用于鎖定數(shù)據(jù)庫文件。
EXTENT -- Extent鎖用于鎖定extents,通常僅在空間分配和重新分配的時(shí)候使用。一個(gè)extent由8個(gè)連續(xù)的數(shù)據(jù)頁或索引頁組成。Extent鎖可以是共享鎖也可以是獨(dú)占鎖。
ALLOCATION_UNIT -- 使用在數(shù)據(jù)庫分配單元上。
TABLE -- 這種級(jí)別的鎖將鎖定整個(gè)表，包括數(shù)據(jù)和索引。何時(shí)將獲得表級(jí)鎖的例子包括在Serializable隔離級(jí)別下從包含大量數(shù)據(jù)的表中選取所有的行，以及在表上執(zhí)行不帶過濾條件的update或delete。
Heap or B-Tree (HOBT) -- 用于堆數(shù)據(jù)頁，或者索引的二叉樹結(jié)構(gòu)。
PAGE -- 使用頁級(jí)鎖，由8KB數(shù)據(jù)或者索引信息組成的整個(gè)頁被鎖定。當(dāng)需要讀取一頁的所有行或者需要執(zhí)行頁級(jí)別的維護(hù)如頁拆分后更新頁指針時(shí)，將會(huì)獲取頁級(jí)鎖。
Row ID (RID) -- 使用RID鎖，頁內(nèi)的單一行被鎖定。無論何時(shí)當(dāng)提供最大化的資源并發(fā)性訪問是有效并且可能時(shí)，將獲得RID鎖。
KEY -- SQL Server使用兩種類型的Key鎖。其中一個(gè)的使用取決于當(dāng)前會(huì)話的鎖隔離級(jí)別。對(duì)于運(yùn)行于Read Committed 或者 Repeatable Read 隔離模式下的事務(wù)，SQL Server 鎖定與被訪問的行相關(guān)聯(lián)的的實(shí)際索引key。（如果是表的聚集索引，數(shù)據(jù)行位于索引的葉級(jí)。行上在這些你看到的是Key鎖而不是行級(jí)鎖。）若在Serializable隔離模式下，通過鎖定一定范圍的key值從而不允許新的行插入到該范圍內(nèi)，SQL Server防止了“幻讀”。這些鎖因而被稱作“key-range lock”。
METADATA -- 用于鎖定系統(tǒng)目錄信息（元數(shù)據(jù)）。
APPLICATION -- 允許用戶定義他們自己的鎖，指定資源名稱、鎖模式、所有者、timeout間隔。

四 SQL Server 鎖類型(與粒度相對(duì)應(yīng))

1． HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語句執(zhí)行完立即釋放所添加的鎖?！　?nbsp;
2． NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語句。　　 
3． PAGLOCK：指定添加頁鎖（否則通?？赡芴砑颖礞i）。　 
4． READCOMMITTED用與運(yùn)行在提交讀隔離級(jí)別的事務(wù)相同的鎖語義執(zhí)行掃描。默認(rèn)情況下，SQL Server 2000 在此隔離級(jí)別上操作。 
5． READPAST: 跳過已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應(yīng)用于READ COMMITTED隔離性級(jí)別下事務(wù)操作中的SELECT語句操作?！　?br> 6． READUNCOMMITTED：等同于NOLOCK?！　?nbsp;
7． REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別?！?nbsp;
8． ROWLOCK：使用行級(jí)鎖，而不使用粒度更粗的頁級(jí)鎖和表級(jí)鎖。　　 
9． SERIALIZABLE：用與運(yùn)行在可串行讀隔離級(jí)別的事務(wù)相同的鎖語義執(zhí)行掃描。等同于 HOLDLOCK?！?nbsp;
10． TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁面級(jí)的鎖，SQL Server在該語句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束?！　?br> 11． TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數(shù)據(jù)，直到這個(gè)語句或整個(gè)事務(wù)結(jié)束。　 
12． UPDLOCK ：指定在讀表中數(shù)據(jù)時(shí)設(shè)置更新 鎖（update lock）而不是設(shè)置共享鎖，該鎖一直保持到這個(gè)語句或整個(gè)事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶先讀取數(shù)據(jù)（而且不阻塞其他用戶讀數(shù)據(jù)），并且保證在后來再更新數(shù)據(jù)時(shí)，這一段時(shí)間內(nèi)這些數(shù)據(jù)沒有被其他用戶修改。

五 鎖定時(shí)間的長短

鎖保持的時(shí)間長度為保護(hù)所請(qǐng)求級(jí)別上的資源所需的時(shí)間長度。

用于保護(hù)讀取操作的共享鎖的保持時(shí)間取決于事務(wù)隔離級(jí)別。采用 READ COMMITTED 的默認(rèn)事務(wù)隔離級(jí)別時(shí)，只在讀取頁的期間內(nèi)控制共享鎖。在掃描中，直到在掃描內(nèi)的下一頁上獲取鎖時(shí)才釋放鎖。如果指定 HOLDLOCK 提示或者將事務(wù)隔離級(jí)別設(shè)置為 REPEATABLE READ 或 SERIALIZABLE，則直到事務(wù)結(jié)束才釋放鎖。

根據(jù)為游標(biāo)設(shè)置的并發(fā)選項(xiàng)，游標(biāo)可以獲取共享模式的滾動(dòng)鎖以保護(hù)提取。當(dāng)需要滾動(dòng)鎖時(shí)，直到下一次提取或關(guān)閉游標(biāo)（以先發(fā)生者為準(zhǔn)）時(shí)才釋放滾動(dòng)鎖。但是，如果指定 HOLDLOCK，則直到事務(wù)結(jié)束才釋放滾動(dòng)鎖。

用于保護(hù)更新的排它鎖將直到事務(wù)結(jié)束才釋放。

如果一個(gè)連接試圖獲取一個(gè)鎖，而該鎖與另一個(gè)連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到：

將沖突鎖釋放而且連接獲取了所請(qǐng)求的鎖。

連接的超時(shí)間隔已到期。默認(rèn)情況下沒有超時(shí)間隔，但是一些應(yīng)用程序設(shè)置超時(shí)間隔以防止無限期等待 

六 SQL Server 中鎖的自定義

◆處理死鎖和設(shè)置死鎖優(yōu)先級(jí)

死鎖就是多個(gè)用戶申請(qǐng)不同封鎖,由于申請(qǐng)者均擁有一部分封鎖權(quán)而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時(shí)會(huì)話的反應(yīng)方式。如果兩個(gè)進(jìn)程都鎖定數(shù)據(jù)，并且直到其它進(jìn)程釋放自己的鎖時(shí)，每個(gè)進(jìn)程才能釋放自己的鎖，即發(fā)生死鎖情況。

◆2 處理超時(shí)和設(shè)置鎖超時(shí)持續(xù)時(shí)間。

@@LOCK_TIMEOUT 返回當(dāng)前會(huì)話的當(dāng)前鎖超時(shí)設(shè)置，單位為毫秒

SET LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時(shí)間。當(dāng)語句等待的時(shí)間大于 LOCK_TIMEOUT 設(shè)置時(shí)，系統(tǒng)將自動(dòng)取消阻塞的語句，并給應(yīng)用程序返回"已超過了鎖請(qǐng)求超時(shí)時(shí)段"的 1222 號(hào)錯(cuò)誤信息

示例

下例將鎖超時(shí)期限設(shè)置為 1,800 毫秒。

SET LOCK_TIMEOUT 1800

◆設(shè)置事務(wù)隔離級(jí)別。

◆對(duì) SELECT、INSERT、UPDATE 和 DELETE 語句使用表級(jí)鎖定提示。

◆配置索引的鎖定粒度

可以使用 sp_indexoption 系統(tǒng)存儲(chǔ)過程來設(shè)置用于索引的鎖定粒度

七 查看鎖的信息

1 執(zhí)行 EXEC SP_LOCK 報(bào)告有關(guān)鎖的信息

2 查詢分析器中按Ctrl+2可以看到鎖的信息

八 使用注意事項(xiàng)

如何避免死鎖，最小化鎖競(jìng)爭(zhēng)

1 使用事務(wù)時(shí)，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)，事務(wù)持有鎖的時(shí)間越短，鎖競(jìng)爭(zhēng)發(fā)生的機(jī)會(huì)就越少；將不是事務(wù)所管理的工作單元鎖必需的命令移出事務(wù)。；

2 設(shè)置死鎖超時(shí)參數(shù)為合理范圍，如：3分鐘-10分種；超過時(shí)間，自動(dòng)放棄本次操作，避免進(jìn)程懸掛；

3 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；

4 .對(duì)所有的腳本和SP都要仔細(xì)測(cè)試，在正是版本之前。

5 所有的SP都要有錯(cuò)誤處理（通過@error）

6 一般不要修改SQL SERVER事務(wù)的默認(rèn)級(jí)別。不推薦強(qiáng)行加鎖

7 將組成事務(wù)的語句作為一個(gè)的單獨(dú)的批命令處理，以消除 BEGIN TRAN 和 COMMIT  TRAN 語句之間的網(wǎng)絡(luò)延遲造成的不必要的延遲。

8 考慮完全地使用存儲(chǔ)過程編寫事務(wù)代碼。典型地，存儲(chǔ)過程比批命令運(yùn)行更快。

9 在游標(biāo)中盡可早地Commit更新。因?yàn)橛螛?biāo)處理比面向集合的處理慢得多，因此導(dǎo)致鎖被持有的時(shí)間更久。

10 使用每個(gè)進(jìn)程所需的最低級(jí)別的鎖隔離。比如說，如果臟讀是可接受的并且不要求結(jié)果必須精確，那么可以考慮使用事務(wù)隔離級(jí)別0（Read Uncommitted）,僅在絕對(duì)必要時(shí)才使用Repeatable Read or Serializable隔離級(jí)別。

11 在 BEGIN TRAN 和 COMMIT TRAN 語句之間，絕不允許用戶交互，因?yàn)檫@樣做可能鎖被持有無限期的時(shí)間。

九 幾個(gè)有關(guān)鎖的問題

1 如何鎖一個(gè)表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定數(shù)據(jù)庫的一個(gè)表

SELECT * FROM table WITH (HOLDLOCK)

加鎖語句：

sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ；
加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖

◆排它鎖

新建兩個(gè)連接,在第一個(gè)連接中執(zhí)行以下語句

begin tran 
update table1 set A='aa' where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 
--在第二個(gè)連接中執(zhí)行以下語句 
begin tran 
select * from table1 where B='b2' 
commit tran

若同時(shí)執(zhí)行上述兩個(gè)語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒

◆共享鎖

在第一個(gè)連接中執(zhí)行以下語句

begin tran 
select * from table1 holdlock  --holdlock人為加鎖 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran

◆共享鎖

在第一個(gè)連接中執(zhí)行以下語句

begin tran 
select * from table1 holdlock --holdlock人為加鎖 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran

在第二個(gè)連接中執(zhí)行以下語句

begin tran 
select A,C from table1 where B='b2' 
update table1 set A='aa' where B='b2' 
commit tran

若同時(shí)執(zhí)行上述兩個(gè)語句，則第二個(gè)連接中的select查詢可以執(zhí)行

而update必須等待第一個(gè)事務(wù)釋放共享鎖轉(zhuǎn)為排它鎖后才能執(zhí)行 即要等待30秒

◆死鎖

--在第一個(gè)連接中執(zhí)行以下語句 
begin tran 
update table1  set A='aa' where B='b2' 
waitfor delay '00:00:30' 
update table2  set D='d5'  where E='e1' 
commit tran
 
--在第二個(gè)連接中執(zhí)行以下語句

begin tran 
update table2  set D='d5'  where E='e1' 
waitfor delay '00:00:10' 
update table1  set A='aa'  where B='b2' 
commit tran

同時(shí)執(zhí)行，系統(tǒng)會(huì)檢測(cè)出死鎖，并中止進(jìn)程

十 應(yīng)用程序鎖:

應(yīng)用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖

處理應(yīng)用程序鎖的兩個(gè)過程

sp_getapplock 鎖定應(yīng)用程序資源

sp_releaseapplock 為應(yīng)用程序資源解鎖

注意: 鎖定數(shù)據(jù)庫的一個(gè)表的區(qū)別

SELECT * FROM table WITH (HOLDLOCK) 其他事務(wù)可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務(wù)不能讀取表,更新和刪除

交讀事務(wù)使用行版本控制。
使用快照隔離。
使用綁定連接。

二 鎖的分析及應(yīng)用系列

1 用SqlServer Profile來查看分析鎖的信息

　　這個(gè)工具我想大家都明白，它的監(jiān)視能力真的是無所不能。。。鎖的痙攣狀態(tài)也全在它的掌握之中。

1. 首先我做一個(gè)Person表，Name字段設(shè)定4000字節(jié)，這樣一個(gè)數(shù)據(jù)頁可以容納2條數(shù)據(jù)，如下圖：

DROP TABLE dbo.Person
CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'aaaaa')
--插入6條，生成3個(gè)數(shù)據(jù)頁
INSERT INTO dbo.Person DEFAULT VALUES
go 6

2. 下面我們看看數(shù)據(jù)在數(shù)據(jù)頁的分布情況。

3. 然后我們開啟Profile，在“事件選擇”的Events中選擇”Lock:Acquired“和”Lock:Released“ ，然后運(yùn)行，如下圖：

使用測(cè)試數(shù)據(jù)

1. 首先我執(zhí)行一個(gè)簡(jiǎn)單的 SELECT * FROM dbo.Person，看看表/數(shù)據(jù)頁/記錄的加鎖情況。

從圖中可以看到，select執(zhí)行的大概步驟如下：

第一步：給表（Object）加上IS（意向共享鎖）。

第二步：先給1:78號(hào)數(shù)據(jù)頁加IS鎖，掃描78號(hào)數(shù)據(jù)頁，然后釋放IS鎖。

第三步：同樣的道理掃描之后的數(shù)據(jù)頁。

第四步：最后釋放表的IS鎖，結(jié)束整個(gè)鎖流程。

看完上面的一系列的Lock:Acquired 和 Lock:Released的話，你有沒有發(fā)現(xiàn)一個(gè)問題，不是說好給記錄（RID）加上S鎖么？？？這里沒加，是因?yàn)橐孢M(jìn)入78號(hào)數(shù)據(jù)頁的時(shí)候，未發(fā)現(xiàn)它存在IU鎖或者IX鎖。。。所以。。。這個(gè)屬于鎖的組合，后續(xù)會(huì)說。

2. 接下來用UPDATE dbo.Person SET NAME='bbbbb' WHERE ID=3來看看update的整個(gè)過程，乍一看，Profile捕獲到的記錄還是比較多的，下面具體看圖：

 第一步： 給表(Object)加上IX鎖，

 第二步： 給數(shù)據(jù)頁(1:78)數(shù)據(jù)頁分配IU鎖。然后開始逐一掃描78號(hào)數(shù)據(jù)頁的RID記錄，進(jìn)入前就Acquired，退出后就Released，當(dāng)掃描完78號(hào)數(shù)據(jù)頁的所有RID后，再釋放78                  號(hào)數(shù)據(jù)頁的IU鎖，進(jìn)入下一個(gè)數(shù)據(jù)頁。。。

 第三步： 我們發(fā)現(xiàn)ID=3是在89號(hào)數(shù)據(jù)頁上，當(dāng)引擎掃到該RID之后，我們觀察到89號(hào)的數(shù)據(jù)頁由IU鎖變成了IX鎖，并且把1:89:0（slot為0的記錄）由U鎖變成X鎖，變成X鎖                       后，就排斥了其他所有的鎖，這時(shí)候就可以進(jìn)行Update操作了。

 第四步： 后面就繼續(xù)90號(hào)數(shù)據(jù)頁，步驟類似，第二步和第三步。

不知道細(xì)心的你有沒有發(fā)現(xiàn)，在Released Object之前我們才釋放1:89:0的X鎖，然后釋放89號(hào)數(shù)據(jù)頁的IX鎖，這說明什么？？？說明這個(gè)Update是貫穿于這個(gè)事務(wù)的，不像Select操作中，掃完一個(gè)數(shù)據(jù)頁就釋放一個(gè)數(shù)據(jù)頁。

3. 最后再看一個(gè)DELETE FROM dbo.Person WHERE ID=3 的操作。

　　大概掃了一下上面的圖，或許你感覺和Update操作大差不差，會(huì)掃描數(shù)據(jù)頁中的每個(gè)記錄并加上U鎖。當(dāng)在1:89:0槽位中找到了目標(biāo)記錄后，然后將U鎖轉(zhuǎn)化為X鎖，具體可以參考Update。

2 深入的探討鎖機(jī)制

　　上一篇我只是做了一個(gè)堆表讓大家初步的認(rèn)識(shí)到鎖的痙攣狀態(tài)，但是在現(xiàn)實(shí)世界上并沒有這么簡(jiǎn)單的事情，起碼我的表不會(huì)沒有索引對(duì)吧，，，還有就是我的表一定會(huì)有很多的連接過來，10:1的讀寫，很多碼農(nóng)可能都會(huì)遇到類似神乎其神的死鎖，卡住，讀不出來，插不進(jìn)入等等神仙的事情導(dǎo)致性能低下，這篇我們一起來探討下。

 

一： 當(dāng)select遇到性能低下的update會(huì)怎么樣？

1. 還是使用原始的person表，插入6條數(shù)據(jù)，由于是4000字節(jié)，所以兩條數(shù)據(jù)就是一個(gè)數(shù)據(jù)頁，如下圖：

1 DROP TABLE dbo.Person
2 CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'aaaaa')
3 --插入6條數(shù)據(jù)，剛好3個(gè)數(shù)據(jù)頁
4 INSERT INTO dbo.Person DEFAULT VALUES
5 go 6

 

2. 為了模擬性能低下的Update操作，我們開個(gè)顯式事務(wù)來更新ID=4的記錄，并且用profile看一下，如下圖：

1 BEGIN TRAN
2 UPDATE dbo.Person SET NAME='bbbbb' WHERE id=4

3. 然后我們開下另一個(gè)會(huì)話連接，讀取ID=6的記錄會(huì)是怎樣？好奇嗎？

1 SELECT * FROM Person WHERE ID=6

從上面流程你是否看到，當(dāng)掃描到89號(hào)數(shù)據(jù)頁的slot1槽位的時(shí)候卡住了。。。我想你應(yīng)該知道update正好已經(jīng)給這條記錄加上了X鎖。。。如果你夠細(xì)心，你還會(huì)發(fā)現(xiàn)，給S鎖附加記錄的條件是在當(dāng)引擎發(fā)現(xiàn)記錄所在的數(shù)據(jù)頁已經(jīng)附加上了IX鎖的情況下，才給該號(hào)數(shù)據(jù)頁下的每條記錄附加S鎖，對(duì)吧。。。好了，既然在Profile上面看不到了，我還是有其他辦法來判斷到底select語句現(xiàn)在處于什么狀態(tài)。

 

4. 使用sys.dm_tran_locks來看當(dāng)前各個(gè)連接持有鎖的狀態(tài)。

1 SELECT  l.request_session_id,
2         DB_NAME(l.resource_database_id),OBJECT_NAME(p.object_id),
3         l.resource_description,l.request_type,
4         l.request_status,request_mode 
5 FROM sys.dm_tran_locks AS l
6 LEFT JOIN sys.partitions AS p
7 ON l.resource_associated_entity_id=p.hobt_id

仔細(xì)觀察上圖可以看到，當(dāng)前有51和52號(hào)會(huì)話，51號(hào)在1:89:1槽位上使用了X鎖并且沒有釋放，52號(hào)此時(shí)也進(jìn)入了1:89:1中，并且想給該RowID附加S鎖，但是你也知道S和X鎖是排斥的，所以很無奈的一直保持等待狀態(tài)。

 

二：使用索引或許可以幫你逃過一劫

　　當(dāng)你看完上面的講敘，是不是有點(diǎn)害怕？？？要是在生產(chǎn)環(huán)境下出現(xiàn)了這種情況，那我們是不是死的很慘？？？那接下來使用索引是不是真的可以幫我們躲過一劫呢？下面跟我一起看一看。

1. 新建索引index

1 -- 在ID列上建一個(gè)index
2 CREATE INDEX idx_person ON dbo.Person(ID)

2. 然后我們看下數(shù)據(jù)頁的分布情況，可以看到下圖中78,89,90是表數(shù)據(jù)頁，93號(hào)為索引數(shù)據(jù)頁。

1 DBCC TRACEON(2588,3604)
2 DBCC IND(Ctrip,Person,-1)

 

3. 麻蛋的，繼續(xù)執(zhí)行上面的那個(gè)慢update

BEGIN TRAN
UPDATE dbo.Person SET NAME='bbbbb' WHERE id=4

 

4. 激動(dòng)人心的時(shí)刻來了，由于數(shù)據(jù)太少，所以我這里強(qiáng)制讓引擎執(zhí)行我創(chuàng)建的索引，看看結(jié)果怎樣？

居然沒卡?。?？？現(xiàn)在是不是有一股強(qiáng)烈的好奇心來了，狗狗狗。。。馬上開啟profile，看看到底都發(fā)生了什么？

仔細(xì)看完這個(gè)圖，是不是覺得很有意思呢？？？具體步驟如下：

第一步：給表（Object）加上IS鎖。

第二步：因?yàn)橐咚饕?，給93號(hào)索引數(shù)據(jù)頁加上IS鎖。

第三步：找到93號(hào)索引數(shù)據(jù)頁的目標(biāo)key，給這個(gè)key加上S鎖，有人可能就會(huì)問了。。。這個(gè)key不就是6嘛，為什么這個(gè)key=(61005a25560e)，你要是太好奇我可以告                   訴你，年輕人說話不要太屌，每行索引記錄都有一個(gè)散列值，這個(gè)值就是根據(jù)索引的幾個(gè)字段散列出來的，好處就是防止你的索引長度過大，導(dǎo)致鎖這個(gè)記錄的                   時(shí)候太耗費(fèi)鎖空間了。。。。如果你還是不太相信的話，我用DBCC給你看一看。　　　　　　

            

第四步：根據(jù)這個(gè)key直接跳到存放記錄的90號(hào)數(shù)據(jù)頁中，萬幸的是update的記錄剛好不在90號(hào)數(shù)據(jù)頁中。。。。就這樣躲過一劫了。。。然后select順利的讀取到了該                  讀的記錄，最后釋放相關(guān)的IS鎖。

3 nolock引發(fā)的三級(jí)事件的一些思考

1 DROP TABLE dbo.Person
2 CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'xxxxx')
3 INSERT INTO dbo.Person DEFAULT VALUES
4 go 6

1 BEGIN TRAN
2 UPDATE dbo.Person SET NAME='aaaaa' WHERE ID=6
3 
4 BEGIN TRAN
5 UPDATE dbo.Person WITH(PAGLOCK) SET NAME='bbbbb' WHERE ID=4

1 SELECT * FROM dbo.Person(HOLDLOCK)
2 UPDATE dbo.Person WITH(HOLDLOCK) SET NAME='bbbbb' WHERE ID=4

三 SQL Server 鎖機(jī)制 悲觀鎖 樂觀鎖 實(shí)測(cè)解析

BEGIN TRAN
SELECT * FROM Table WITH(UPDLOCK) 
--或者 SELECT * FROM Table WITH(TABLOCKX, READPAST) 具體情況而定。
UPDATE ....
COMMIT TRAN

插入鎖：

------------------A連接 Insert Lock-------------------
BEGIN TRAN
INSERT INTO dbo.UserInfo
        ( Name, Age, Mobile, AddTime, Type )
VALUES  ( 'eee', -- Name - varchar(50)
          2, -- Age - int
          '555', -- Mobile - char(11)
          GETDATE(), -- AddTime - datetime
          0  -- Type - int
          )
          
SELECT resource_type, request_mode,COUNT(*)  FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY resource_type,request_mode
--ROLLBACK TRAN

------------------------B連接 Insert Lock------------------------
INSERT INTO dbo.UserInfo
        ( Name, Age, Mobile, AddTime, Type )
VALUES  ( 'fff', -- Name - varchar(50)
          2, -- Age - int
          '123', -- Mobile - char(11)
          GETDATE(), -- AddTime - datetime
          1  -- Type - int
          ) --可以執(zhí)行插入
          
SELECT * FROM dbo.UserInfo --需要等待解鎖
SELECT * FROM dbo.UserInfo WHERE Age=1 --需要等待解鎖
SELECT * FROM dbo.UserInfo WHERE Id=3 --可以執(zhí)行查詢（根據(jù)主鍵可以）
SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(在一個(gè)事務(wù)中，有更新字段但還沒有提交，此時(shí)就會(huì)查處臟數(shù)據(jù))
SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Age=1 --可以執(zhí)行查詢
UPDATE dbo.UserInfo SET Type=5 WHERE Name='fff'  --需要等待解鎖
DELETE FROM dbo.UserInfo WHERE Name='fff' --需要等待解鎖

更新鎖：

-----------------------A連接 Update Lock-----------------------
BEGIN TRAN
UPDATE dbo.UserInfo SET Name = 'eee' WHERE Age = 2

SELECT resource_type, request_mode,COUNT(*)  FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY resource_type,request_mode

--ROLLBACK TRAN

------------------------B連接 Update Lock------------------------
INSERT INTO dbo.UserInfo
        ( Name, Age, Mobile, AddTime, Type )
VALUES  ( 'ppp', -- Name - varchar(50)
          15, -- Age - int
          '666', -- Mobile - char(11)
          GETDATE(), -- AddTime - datetime
          9  -- Type - int
          ) --可以執(zhí)行插入
SELECT * FROM dbo.UserInfo --需要等待解鎖
SELECT * FROM dbo.UserInfo WHERE Name='ppp' --需要等待解鎖
SELECT * FROM dbo.UserInfo WHERE Id=3 --可以執(zhí)行查詢（根據(jù)主鍵可以）
SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(在一個(gè)事務(wù)中，有更新字段但還沒有提交，此時(shí)就會(huì)查處臟數(shù)據(jù))
SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'ppp' --可以執(zhí)行查詢
UPDATE dbo.UserInfo SET Age=8 WHERE Name='ccc' --需要等待解鎖
DELETE dbo.UserInfo WHERE Age = 5 --需要等待解鎖

主鍵鎖：

------------------------A連接 Key Lock--------------------
BEGIN TRAN
UPDATE dbo.UserInfo SET Name='hhh' WHERE Id=3 --以主鍵為條件

SELECT resource_type, request_mode,COUNT(*)  FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY resource_type,request_mode

--ROLLBACK TRAN

------------------------B連接 Key Lock----------------------
INSERT INTO dbo.UserInfo
        ( Name, Age, Mobile, AddTime, Type )
VALUES  ( 'kkk', -- Name - varchar(50)
          18, -- Age - int
          '234', -- Mobile - char(11)
          GETDATE(), -- AddTime - datetime
          7  -- Type - int
          ) --可以執(zhí)行插入
SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(在一個(gè)事務(wù)中，有更新字段但還沒有提交，此時(shí)就會(huì)查處臟數(shù)據(jù))
SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'kkk' --可以執(zhí)行查詢

-----//全表查詢及操作正在處理的行
SELECT * FROM dbo.UserInfo --需要等待解鎖
SELECT * FROM dbo.UserInfo WHERE Id=3 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可）
UPDATE dbo.UserInfo SET Name='mmm' WHERE Id=3 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可）
DELETE dbo.UserInfo WHERE Id=3 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可）
-----//使用非主鍵為條件的操作
SELECT * FROM dbo.UserInfo WHERE Name='aaa' --需要等待解鎖（非主鍵不可）
UPDATE dbo.UserInfo SET Name='ooo' WHERE Name='aaa' --需要等待解鎖（非主鍵不可）
DELETE dbo.UserInfo WHERE Name='aaa' --需要等待解鎖（非主鍵不可）
-----//使用主鍵為條件的操作
SELECT * FROM dbo.UserInfo WHERE id=1 --可以執(zhí)行查詢（根據(jù)主鍵可以）
UPDATE dbo.UserInfo SET Name='yyy' WHERE Id=1 --可以執(zhí)行更新（根據(jù)主鍵可以）
DELETE dbo.UserInfo WHERE Id=1 --可以執(zhí)行刪除（根據(jù)主鍵可以）

索引鎖：

------------------------A連接 Index Lock--------------------
DROP INDEX dbo.UserInfo.Index_UserInfo_Name
CREATE INDEX Index_UserInfo_Name ON dbo.UserInfo(Name)

BEGIN TRAN
UPDATE dbo.UserInfo SET age=66 WHERE Name='ddd' --使用name索引列為條件

SELECT resource_type, request_mode,COUNT(*)  FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY resource_type,request_mode

--ROLLBACK TRAN

----------------------B連接 Index Lock-------------------
INSERT INTO dbo.UserInfo
        ( Name, Age, Mobile, AddTime, Type )
VALUES  ( 'iii', -- Name - varchar(50)
          20, -- Age - int
          '235235235', -- Mobile - char(11)
          GETDATE(), -- AddTime - datetime
          12  -- Type - int
          ) --可以執(zhí)行插入
SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(在一個(gè)事物中，有更新字段但還沒有提交，此時(shí)就會(huì)查處臟數(shù)據(jù))
SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'kkk' --可以執(zhí)行查詢

-----//全表查詢及操作正在處理的行
SELECT * FROM dbo.UserInfo --需要等待解鎖
SELECT * FROM dbo.UserInfo WHERE Id=4 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可）
UPDATE dbo.UserInfo SET Name='mmm' WHERE Id=4 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可）
DELETE dbo.UserInfo WHERE Id=4 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可）
-----//使用非主鍵非索引為條件的操作
SELECT * FROM dbo.UserInfo WHERE Age=5 --需要等待解鎖（非主鍵不可）
UPDATE dbo.UserInfo SET Name='ooo' WHERE Age=5 --需要等待解鎖（非主鍵不可）
DELETE dbo.UserInfo WHERE Age=5 --需要等待解鎖（非主鍵不可）
-----//使用主鍵為條件的操作
SELECT * FROM dbo.UserInfo WHERE Id=1 --可以執(zhí)行更新（根據(jù)主鍵可以）
UPDATE dbo.UserInfo SET Name='yyy' WHERE Id=1 --可以執(zhí)行更新（根據(jù)主鍵可以）
DELETE dbo.UserInfo WHERE Id=1 --可以執(zhí)行刪除（根據(jù)主鍵可以）
-----//使用索引為條件的操作
SELECT * FROM dbo.UserInfo WHERE Name='aaa' --需要等待解鎖（非主鍵不可）
UPDATE dbo.UserInfo SET Name='ooo' WHERE Name='aaa' --可以執(zhí)行更新（根據(jù)索引可以）
DELETE dbo.UserInfo WHERE Name='aaa' --可以執(zhí)行刪除（根據(jù)索引可以）

悲觀鎖（更新鎖-人工手動(dòng)設(shè)置上鎖）：

------------------------A連接 Update Lock(悲觀鎖)---------------------
BEGIN TRAN
SELECT * FROM dbo.UserInfo WITH(UPDLOCK) WHERE Id=2

SELECT resource_type, request_mode,COUNT(*)  FROM sys.dm_tran_locks
WHERE request_session_id=@@SPID
GROUP BY resource_type,request_mode

--COMMIT TRAN
--ROLLBACK TRAN

---------------------------B連接 Update Lock(悲觀鎖)-------------------------
SELECT * FROM dbo.UserInfo --可以執(zhí)行查詢
SELECT * FROM dbo.UserInfo WHERE id=2 --可以執(zhí)行查詢
SELECT * FROM dbo.UserInfo WHERE Name='ooo' --可以執(zhí)行查詢

UPDATE dbo.UserInfo SET Age=3 WHERE id=1 --可以執(zhí)行更新（根據(jù)主鍵可以）
UPDATE dbo.UserInfo SET Age=3 WHERE Name='ccc' --需要等待解鎖（非主鍵不可）

DELETE dbo.UserInfo WHERE id=1 --可以執(zhí)行更新（根據(jù)主鍵可以）
DELETE dbo.UserInfo WHERE name='ccc' --需要等待解鎖（非主鍵不可）

樂觀鎖（人工通過邏輯在數(shù)據(jù)庫中模擬鎖）

--------------------------A客戶端連接 Lock(樂觀鎖)------------------------
--DROP TABLE Coupon
-----------------創(chuàng)建優(yōu)惠券表-----------------
CREATE TABLE Coupon
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Number VARCHAR(50) NOT NULL,
    [User] VARCHAR(50),
    UseTime DATETIME,
    IsFlag BIT DEFAULT(0) NOT NULL,
    CreateTime DATETIME DEFAULT(GETDATE()) NOT NULL
)
INSERT INTO dbo.Coupon(Number) VALUES ( '10000001')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000002')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000003')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000004')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000005')
INSERT INTO dbo.Coupon(Number) VALUES ( '10000006')

--SELECT * FROM dbo.Coupon WITH(NOLOCK) --查詢數(shù)據(jù)
--UPDATE Coupon SET [User]=NULL, UseTime=NULL, IsFlag=0 --還原數(shù)據(jù)

-----------------1、模擬高并發(fā)普通更新-----------------
DECLARE @User VARCHAR(50)    --模擬要使用優(yōu)惠券的用戶
DECLARE @TempId INT            --模擬抽選出來的要使用的優(yōu)惠券
SET @User='a'
BEGIN TRAN
SELECT @TempId=Id FROM dbo.Coupon WHERE IsFlag=0    --高并發(fā)時(shí)此語句有可能另外一個(gè)該事務(wù)已取出的Id
--WAITFOR DELAY '00:00:05'    --改用此方式要開兩個(gè)SQL Management客戶端
UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId
COMMIT TRAN
--ROLLBACK TRAN

-----------------2、悲觀鎖解決方案-----------------
DECLARE @User VARCHAR(50)    --模擬要使用優(yōu)惠券的用戶
DECLARE @TempId INT            --模擬抽選出來的要使用的優(yōu)惠券
SET @User='a'
BEGIN TRAN
SELECT @TempId=Id FROM dbo.Coupon WITH(UPDLOCK) WHERE IsFlag=0    --高并發(fā)時(shí)此語句會(huì)鎖定取出的Id數(shù)據(jù)行
--WAITFOR DELAY '00:00:05'    --改用此方式要開兩個(gè)SQL Management客戶端
UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId
COMMIT TRAN
--ROLLBACK TRAN

-----------------3、樂觀鎖解決方案-----------------
ALTER TABLE dbo.Coupon ADD RowVer ROWVERSION NOT NULL --增加數(shù)據(jù)行版本戳類型字段（微軟新推薦數(shù)據(jù)字段，該字段每張表只能有一個(gè)，會(huì)在創(chuàng)建行或更新行時(shí)自動(dòng)進(jìn)行修改無需人為干涉，該字段不能建立索引及主鍵因?yàn)闀?huì)頻繁修改）


DECLARE @User VARCHAR(50)    --模擬要使用優(yōu)惠券的用戶
DECLARE @TempId INT            --模擬抽選出來的要使用的優(yōu)惠券
DECLARE @RowVer BINARY(8)    --抽選出來的優(yōu)惠券的版本（ROWVERSION數(shù)據(jù)類型存儲(chǔ)大小為8字節(jié)）
SET @User='a'

BEGIN TRY
    BEGIN TRAN
    SELECT @TempId=Id, @RowVer=RowVer FROM dbo.Coupon WHERE IsFlag=0    --取出可用的Id及對(duì)應(yīng)的版本戳
    --WAITFOR DELAY '00:00:05'    --改用此方式要開兩個(gè)SQL Management客戶端
    UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId AND RowVer=@RowVer
    IF(@@ROWCOUNT > 0)
        BEGIN
            PRINT('修改成功')
            COMMIT TRAN
        END
    ELSE
        BEGIN
            PRINT('該數(shù)據(jù)已被其他用戶修改')
            ROLLBACK TRAN
        END
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

--------------------------B客戶端連接 Lock(樂觀鎖)------------------------
--此測(cè)試需要開兩個(gè)SQL Management Studio客戶端，在A客戶端使用WAITFOR DELAY來模擬并發(fā)占用，在B客戶端執(zhí)行與A客戶端相同的SQL腳本即可（注釋掉WAITFOR），所以在此不放相同代碼了。

四 SQL Server 中WITH (NOLOCK)淺析

BEGIN TRAN

       UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;

       --ROLLBACK

SELECT * FROM TEST;

  SELECT wt.blocking_session_id                    AS BlockingSessesionId

        ,sp.program_name                           AS ProgramName

        ,COALESCE(sp.LOGINAME, sp.nt_username)     AS HostName    

        ,ec1.client_net_address                    AS ClientIpAddress

        ,db.name                                   AS DatabaseName        

        ,wt.wait_type                              AS WaitType                    

        ,ec1.connect_time                          AS BlockingStartTime

        ,wt.WAIT_DURATION_MS/1000                  AS WaitDuration

        ,ec1.session_id                            AS BlockedSessionId

        ,h1.TEXT                                   AS BlockedSQLText

        ,h2.TEXT                                   AS BlockingSQLText

  FROM sys.dm_tran_locks AS tl

  INNER JOIN sys.databases db

    ON db.database_id = tl.resource_database_id

  INNER JOIN sys.dm_os_waiting_tasks AS wt

    ON tl.lock_owner_address = wt.resource_address

  INNER JOIN sys.dm_exec_connections ec1

    ON ec1.session_id = tl.request_session_id

  INNER JOIN sys.dm_exec_connections ec2

    ON ec2.session_id = wt.blocking_session_id

  LEFT OUTER JOIN master.dbo.sysprocesses sp

    ON SP.spid = wt.blocking_session_id

  CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1

  CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

BEGIN TRAN 

UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1; 

--ROLLBACK 

這種語法會(huì)報(bào)錯(cuò)

SELECT  * FROM   sys.indexes  WITH(NOLOCK) AS i

-Msg 156, Level 15, State 1, Line 1

-Incorrect syntax near the keyword 'AS'.

這種語法正常

SELECT  * FROM   sys.indexes  (NOLOCK) AS i

可以全部改寫為下面語法

SELECT  * FROM   sys.indexes   i WITH(NOLOCK) 

SELECT  * FROM   sys.indexes   i (NOLOCK) 

BEGIN TRAN 

  ALTER TABLE TEST ADD Grade VARCHAR(10) ; 

SELECT * FROM TEST WITH(NOLOCK)