對鎖機(jī)制的研究要具備兩個(gè)條件：
1．?dāng)?shù)據(jù)量大
2．多個(gè)用戶同時(shí)并發(fā)
如果缺少這兩個(gè)條件，數(shù)據(jù)庫不容易產(chǎn)生死鎖問題。研究起來可能會事倍功半。如果這兩個(gè)條件都有，但你還是按數(shù)據(jù)庫缺省設(shè)置來處理數(shù)據(jù)，則會帶來很多的問題，比如：
1）丟失更新
A,B兩個(gè)用戶讀同一數(shù)據(jù)并進(jìn)行修改,其中一個(gè)用戶的修改結(jié)果破壞了另一個(gè)修改的結(jié)果
2）臟讀
A用戶修改了數(shù)據(jù)時(shí),B用戶也在讀該數(shù)據(jù),但A用戶因?yàn)槟承┰蛉∠藢?shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時(shí)B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致
3）不可重復(fù)讀
B用戶讀出該數(shù)據(jù)并修改,同時(shí)，A用戶也在讀取數(shù)據(jù)，此時(shí)A用戶再讀取數(shù)據(jù)時(shí)發(fā)現(xiàn)前后兩次的值不一致
SQL SERVER 作為多用戶數(shù)據(jù)庫系統(tǒng)，以事務(wù)為單位，使用鎖來實(shí)現(xiàn)并發(fā)控制。SQLSERVER使用“鎖”確保事務(wù)完整性和數(shù)據(jù)一致性。

一、鎖的概念
鎖（LOCKING）是最常用的并發(fā)控制機(jī)構(gòu)。是防止其他事務(wù)訪問指定的資源控制、實(shí)現(xiàn)并發(fā)控制的一種主要手段。鎖是事務(wù)對某個(gè)數(shù)據(jù)庫中的資源（如表和記錄）存取前，先向系統(tǒng)提出請求，封鎖該資源，事務(wù)獲得鎖后，即取得對數(shù)據(jù)的控制權(quán)，在事務(wù)釋放它的鎖之前，其他事務(wù)不能更新此數(shù)據(jù)。當(dāng)事務(wù)撤消后，釋放被鎖定的資源。
當(dāng)一個(gè)用戶鎖住數(shù)據(jù)庫中的某個(gè)對象時(shí)，其他用戶就不能再訪問該對象

二、鎖的粒度
SQL Server 2000 具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的的資源。為了使鎖定的成本減至最少，SQL Server 自動將資源鎖定在適合任務(wù)的級別。鎖定在較小的粒度（例如行）可以增加并發(fā)但需要較大的開銷，因?yàn)槿绻i定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就并發(fā)而言是相當(dāng)昂貴的，因?yàn)殒i定整個(gè)表限制了其它事務(wù)對表中任意部分進(jìn)行訪問，但要求的開銷較低，因?yàn)樾枰S護(hù)的鎖較少。SQL Server 可以鎖定行、頁、擴(kuò)展盤區(qū)、表、庫等資源。

• 資源 級別 描述
• RID 行鎖 表中的單個(gè)行
• Key 行級鎖 索引中的行
• Page 頁級鎖 一個(gè)數(shù)據(jù)頁或者索引頁
• Extent 頁級鎖 一組數(shù)據(jù)頁或者索引頁
• Table 表級鎖 整個(gè)表
• Database 數(shù)據(jù)庫級鎖 整個(gè)數(shù)據(jù)庫

選擇多大的粒度，根據(jù)對數(shù)據(jù)的操作而定。如果是更新表中所有的行，則用表級鎖;如果是更新表中的某一行，則用行級鎖。
行級鎖是一種最優(yōu)鎖，因?yàn)樾屑夋i不可能出現(xiàn)數(shù)據(jù)既被占用又沒有使用的浪費(fèi)現(xiàn)象。但是，如果用戶事務(wù)中頻繁對某個(gè)表中的多條記錄操作，將導(dǎo)致對該表的許多記錄行都加上了行級鎖，數(shù)據(jù)庫系統(tǒng)中鎖的數(shù)目會急劇增加，這樣就加重了系統(tǒng)負(fù)荷，影響系統(tǒng)性能。因此，在SQL Server中，還支持鎖升級(lock escalation)。
所謂鎖升級是指調(diào)整鎖的粒度，將多個(gè)低粒度的鎖替換成少數(shù)的更高粒度的鎖，以此來降低系統(tǒng)負(fù)荷。在SQL Server中當(dāng)一個(gè)事務(wù)中的鎖較多，達(dá)到鎖升級門限時(shí)，系統(tǒng)自動將行級鎖和頁面鎖升級為表級鎖。
特別值得注意的是，在SQL Server中，鎖的升級門限以及鎖升級是由系統(tǒng)自動來確定的，不需要用戶設(shè)置。

三、鎖的模式
鎖模式以及描述表

鎖模式 描述
• 共享（S） 用于不更改或不更新數(shù)據(jù)（只讀操作），如SELECT語句
• 更新（U） 用于可更新的資源中。防止當(dāng)多個(gè)會話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見形式的死鎖。
• 排它（X） 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE或DELETE。確保不會同時(shí)對同一資源進(jìn)行多重更新
• 意向 當(dāng) Microsoft SQL Server 數(shù)據(jù)庫引擎獲取低級別的鎖時(shí)，它還將在包含更低級別對象的對象上放置意向鎖.例如： 當(dāng)鎖定行或索引鍵范圍時(shí)，數(shù)據(jù)庫引擎將在包含行或鍵的頁上放置意向鎖。當(dāng)鎖定頁時(shí)，數(shù)據(jù)庫引擎將在包含頁的更高級別的對象上放置意向鎖。
  意向鎖的類型為：意向共享（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í)使用

四 SQL Server 中鎖的設(shè)置
1 處理死鎖和設(shè)置死鎖優(yōu)先級
死鎖就是多個(gè)用戶申請不同封鎖,由于申請者均擁有一部分封鎖權(quán)而又等待其他用戶擁有的部分封鎖而引起的無休止的等待
可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時(shí)會話的反應(yīng)方式。
Syntax:
SET DEADLOCK_PRIORITY { LOW | NORMAL}
其中LOW說明該進(jìn)程會話的優(yōu)先級較低，在出現(xiàn)死鎖時(shí)，可以首先中斷該進(jìn)程的事務(wù)。
2 處理超時(shí)和設(shè)置鎖超時(shí)持續(xù)時(shí)間。
@@LOCK_TIMEOUT 返回當(dāng)前會話的當(dāng)前鎖超時(shí)設(shè)置，單位為毫秒
SET LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時(shí)間。當(dāng)語句等待的時(shí)間大于 LOCK_TIMEOUT 設(shè)置時(shí)，系統(tǒng)將自動取消阻塞的語句，并給應(yīng)用程序返回"已超過了鎖請求超時(shí)時(shí)段"的 1222 號錯(cuò)誤信息
示例
1）將鎖超時(shí)期限設(shè)置為 1,800 毫秒。
SET LOCK_TIMEOUT 1800
2) 配置索引的鎖定粒度
可以使用 sp_indexoption 系統(tǒng)存儲過程來設(shè)置用于索引的鎖定粒度
3）設(shè)置事務(wù)隔離級別
SET   TRANSACTION   ISOLATION   LEVEL

五 查看鎖的信息
1 執(zhí)行 EXEC SP_LOCK 報(bào)告有關(guān)鎖的信息
2 查詢分析器中按Ctrl+2可以看到鎖的信息

六、奇怪的sql語句

begin tran
update titles set title_idid＝title_id  where 1＝2
if (selectavg(price)fromtitles)>$15
begin
update titles set price＝price*1.10
where price<(select avg(price)from titles)
end
commit tran

update titles set title_idid＝title_id  where 1＝2，這個(gè)條件是永遠(yuǎn)也不會成立的，如此寫的含義是什么呢？
這里的where子句看起來很奇怪，盡管計(jì)算出的結(jié)果總是false。當(dāng)優(yōu)化器處理此查詢時(shí)，因?yàn)樗也坏饺魏斡行У腟ARG，它的查詢規(guī)劃就會強(qiáng)制使用一個(gè)獨(dú)占鎖定來進(jìn)行表掃描。此事務(wù)執(zhí)行時(shí)，where子句立即得到一個(gè)false值，于是不會執(zhí)行實(shí)際上的掃描，但此進(jìn)程仍得到了一個(gè)獨(dú)占的表鎖定。
因?yàn)榇诉M(jìn)程現(xiàn)在已有一個(gè)獨(dú)占的表鎖，所以可以保證沒有其他事務(wù)會修改任何數(shù)據(jù)行，能進(jìn)行重復(fù)讀，且避免了由于holdlock所引起的潛在性死鎖。
但是，在使用表鎖定來盡可能地減少死鎖的同時(shí)，也增加了對表鎖定的爭用。因此，在實(shí)現(xiàn)這種方法之前，你需要權(quán)衡一下：避免死鎖是否比允許并發(fā)地對表進(jìn)行訪問更重要。
所以，在這個(gè)事務(wù)中，沒有其他進(jìn)程修改表中任何行的price。

七 如何避免死鎖
1 使用事務(wù)時(shí)，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)；
2 設(shè)置死鎖超時(shí)參數(shù)為合理范圍，如：3分鐘-10分種；超過時(shí)間，自動放棄本次操作，避免進(jìn)程懸掛；
3 所有的SP都要有錯(cuò)誤處理（通過@error）
4 一般不要修改SQL SERVER事務(wù)的默認(rèn)級別。不推薦強(qiáng)行加鎖
5 優(yōu)化程序，檢查并避免死鎖現(xiàn)象出現(xiàn)；
1）合理安排表訪問順序
2）在事務(wù)中盡量避免用戶干預(yù)，盡量使一個(gè)事務(wù)處理的任務(wù)少些。
3）采用臟讀技術(shù)。臟讀由于不對被訪問的表加鎖，而避免了鎖沖突。在客戶機(jī)/服務(wù)器應(yīng)用環(huán)境中，有些事務(wù)往往不允許讀臟數(shù)據(jù)，但在特定的條件下，我們可以用臟讀。
4）數(shù)據(jù)訪問時(shí)域離散法。數(shù)據(jù)訪問時(shí)域離散法是指在客戶機(jī)/服務(wù)器結(jié)構(gòu)中，采取各種控制手段控制對數(shù)據(jù)庫或數(shù)據(jù)庫中的對象訪問時(shí)間段。主要通過以下方式實(shí)現(xiàn): 合理安排后臺事務(wù)的執(zhí)行時(shí)間，采用工作流對后臺事務(wù)進(jìn)行統(tǒng)一管理。工作流在管理任務(wù)時(shí)，一方面限制同一類任務(wù)的線程數(shù)（往往限制為1個(gè)），防止資源過多占用; 另一方面合理安排不同任務(wù)執(zhí)行時(shí)序、時(shí)間，盡量避免多個(gè)后臺任務(wù)同時(shí)執(zhí)行，另外，避免在前臺交易高峰時(shí)間運(yùn)行后臺任務(wù)
5）數(shù)據(jù)存儲空間離散法。數(shù)據(jù)存儲空間離散法是指采取各種手段，將邏輯上在一個(gè)表中的數(shù)據(jù)分散到若干離散的空間上去，以便改善對表的訪問性能。主要通過以下方法實(shí)現(xiàn): 第一，將大表按行或列分解為若干小表; 第二，按不同的用戶群分解。
6）使用盡可能低的隔離性級別。隔離性級別是指為保證數(shù)據(jù)庫數(shù)據(jù)的完整性和一致性而使多用戶事務(wù)隔離的程度，SQL92定義了4種隔離性級別：未提交讀、提交讀、可重復(fù)讀和可串行。如果選擇過高的隔離性級別，如可串行，雖然系統(tǒng)可以因?qū)崿F(xiàn)更好隔離性而更大程度上保證數(shù)據(jù)的完整性和一致性，但各事務(wù)間沖突而死鎖的機(jī)會大大增加，大大影響了系統(tǒng)性能。
7）使用Bound Connections。Bound connections 允許兩個(gè)或多個(gè)事務(wù)連接共享事務(wù)和鎖，而且任何一個(gè)事務(wù)連接要申請鎖如同另外一個(gè)事務(wù)要申請鎖一樣，因此可以允許這些事務(wù)共享數(shù)據(jù)而不會有加鎖的沖突。
8）考慮使用樂觀鎖定或使事務(wù)首先獲得一個(gè)獨(dú)占鎖定。 

八如何對行、 表、數(shù)據(jù)庫加鎖
1 如何鎖一個(gè)表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'

2 鎖定數(shù)據(jù)庫的一個(gè)表
select col1 from 表 (tablockx) where 1=1 ;
加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖
3.實(shí)例
建表

create table table1(A varchar(50)  not  null, B varchar(50) ,C varchar(50));
create table table2(D varchar(50),E varchar(50))
insert table1 (A,B,C) values(‘a1’,’b1’,’c1’);
insert table1 (A,B,C) values(‘a2’,’b2’,’c2’);
insert table1 (A,B,C) values(‘a3’,’b3’,’c3’);
insert table2 (D,E) values(‘d1’,’e1’);
insert table2 (D,E) values(‘d2’,’e2’);

1）排它鎖

-- A事務(wù)先更新table1表，在更新時(shí)，對其他事務(wù)進(jìn)行排他
begin tran
update table1 set A='aa' where B='b2'；
waitfor delay '00:00:30'； --等待30秒
commit tran
-- A事務(wù)先更新table2表
begin tran
select * from table1 where B='b2';
commit tran

若同時(shí)執(zhí)行上述兩個(gè)事務(wù)，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒
2）共享鎖

-- A事務(wù)先查詢table1表，在查詢時(shí)，加共享鎖，防止其他事務(wù)對該表進(jìn)行修改操作
begin tran
select * from table1 holdlock where B='b2' ;
-holdlock人為加鎖
waitfor delay '00:00:30';--等待30秒
commit tran
-- A事務(wù)先查詢table1表，后更改table1表
begin tran
select A,C from table1 where B='b2';
update table1 set A='aa' where B='b2';
commit tran

若并發(fā)執(zhí)行上述兩個(gè)事務(wù)，則B事務(wù)中的select查詢可以執(zhí)行,而update必須等待第一個(gè)事務(wù)釋放共享鎖轉(zhuǎn)為排它鎖后才能執(zhí)行即要等待30秒
3）死鎖

-- A事務(wù)先更新table1表，然后延時(shí)30秒，再更新table2表；
begin tran
update table1 set A='aa' where B='b2';
--這將在 Table1 中生成排他行鎖，直到事務(wù)完成后才會釋放該鎖。
waitfor delay '00:00:30';
--進(jìn)入延時(shí)
update table2 set D='d5' where E='e1' ;
commit tran
-- B事務(wù)先更新table2表，然后延時(shí)10秒，再更新table1表；
begin tran
update table2 set D='d5' where E='e1';
--這將在 Table2 中生成排他行鎖，直到事務(wù)完成后才會釋放該鎖
waitfor delay '00:00:10'
--進(jìn)入延時(shí)
update table1 set A='aa' where B='b2' ;
commit tran

若并發(fā)執(zhí)行上述兩個(gè)事務(wù)，A,B兩事務(wù)都要等待對方釋放排他鎖，這樣便形成了死鎖。

九、sqlserver提供的表級鎖
sqlserver所指定的表級鎖定提示有如下幾種
1． HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語句執(zhí)行完立即釋放所添加的鎖。 
2． NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語句。  
3． PAGLOCK：指定添加頁鎖（否則通常可能添加表鎖）
4． READCOMMITTED用與運(yùn)行在提交讀隔離級別的事務(wù)相同的鎖語義執(zhí)行掃描。默認(rèn)情況下，SQL Server 2000 在此隔離級別上操作。
5． READPAST: 跳過已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應(yīng)用于READ COMMITTED隔離性級別下事務(wù)操作中的SELECT語句操作
6． READUNCOMMITTED：等同于NOLOCK。   
7． REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級別。 
8． ROWLOCK：使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。
9． SERIALIZABLE：用與運(yùn)行在可串行讀隔離級別的事務(wù)相同的鎖語義執(zhí)行掃描。等同于 HOLDLOCK。
  10． TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQL Server在該語句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束。
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ù)沒有被其他用戶修改
SELECT * FROM table WITH (HOLDLOCK) 其他事務(wù)可以讀取表，但不能更新刪除
SELECT * FROM table WITH (TABLOCKX) 其他事務(wù)不能讀取表,更新和刪除

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

應(yīng)用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖處理應(yīng)用程序鎖的兩個(gè)系統(tǒng)存儲過程
sp_getapplock： 鎖定應(yīng)用程序資源
sp_releaseapplock： 為應(yīng)用程序資源解鎖