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

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

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

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

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

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

鎖模式 描述
• 共享（S） 用于不更改或不更新數(shù)據(jù)（只讀操作），如SELECT語句
• 更新（U） 用于可更新的資源中。防止當多個會話在讀取、鎖定以及隨后可能進行的資源更新時發(fā)生常見形式的死鎖。
• 排它（X） 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE或DELETE。確保不會同時對同一資源進行多重更新
• 意向 當 Microsoft SQL Server 數(shù)據(jù)庫引擎獲取低級別的鎖時，它還將在包含更低級別對象的對象上放置意向鎖.例如： 當鎖定行或索引鍵范圍時，數(shù)據(jù)庫引擎將在包含行或鍵的頁上放置意向鎖。當鎖定頁時，數(shù)據(jù)庫引擎將在包含頁的更高級別的對象上放置意向鎖。
  意向鎖的類型為：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX）
• 架構(gòu) 在執(zhí)行依賴于表架構(gòu)的操作時使用。架構(gòu)鎖的類型為：架構(gòu)修改（Sch-M）和架構(gòu)穩(wěn)定（Sch-S）
• 大容量更新（BU） 向表中大容量復制數(shù)據(jù)并指定了TABLOCK提示時使用

四 SQL Server 中鎖的設(shè)置
1 處理死鎖和設(shè)置死鎖優(yōu)先級
死鎖就是多個用戶申請不同封鎖,由于申請者均擁有一部分封鎖權(quán)而又等待其他用戶擁有的部分封鎖而引起的無休止的等待
可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時會話的反應(yīng)方式。
Syntax:
SET DEADLOCK_PRIORITY { LOW | NORMAL}
其中LOW說明該進程會話的優(yōu)先級較低，在出現(xiàn)死鎖時，可以首先中斷該進程的事務(wù)。
2 處理超時和設(shè)置鎖超時持續(xù)時間。
@@LOCK_TIMEOUT 返回當前會話的當前鎖超時設(shè)置，單位為毫秒
SET LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時間。當語句等待的時間大于 LOCK_TIMEOUT 設(shè)置時，系統(tǒng)將自動取消阻塞的語句，并給應(yīng)用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息
示例
1）將鎖超時期限設(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 報告有關(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，這個條件是永遠也不會成立的，如此寫的含義是什么呢？
這里的where子句看起來很奇怪，盡管計算出的結(jié)果總是false。當優(yōu)化器處理此查詢時，因為它找不到任何有效的SARG，它的查詢規(guī)劃就會強制使用一個獨占鎖定來進行表掃描。此事務(wù)執(zhí)行時，where子句立即得到一個false值，于是不會執(zhí)行實際上的掃描，但此進程仍得到了一個獨占的表鎖定。
因為此進程現(xiàn)在已有一個獨占的表鎖，所以可以保證沒有其他事務(wù)會修改任何數(shù)據(jù)行，能進行重復讀，且避免了由于holdlock所引起的潛在性死鎖。
但是，在使用表鎖定來盡可能地減少死鎖的同時，也增加了對表鎖定的爭用。因此，在實現(xiàn)這種方法之前，你需要權(quán)衡一下：避免死鎖是否比允許并發(fā)地對表進行訪問更重要。
所以，在這個事務(wù)中，沒有其他進程修改表中任何行的price。

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

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

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

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

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表，在更新時，對其他事務(wù)進行排他
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

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

-- A事務(wù)先查詢table1表，在查詢時，加共享鎖，防止其他事務(wù)對該表進行修改操作
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í)行上述兩個事務(wù)，則B事務(wù)中的select查詢可以執(zhí)行,而update必須等待第一個事務(wù)釋放共享鎖轉(zhuǎn)為排它鎖后才能執(zhí)行即要等待30秒
3）死鎖

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

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

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

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

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