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

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

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

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

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

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

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

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

五 查看鎖的信息
1 執行 EXEC SP_LOCK 報告有關鎖的信息
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子句看起來很奇怪，盡管計算出的結果總是false。當優化器處理此查詢時，因為它找不到任何有效的SARG，它的查詢規劃就會強制使用一個獨占鎖定來進行表掃描。此事務執行時，where子句立即得到一個false值，于是不會執行實際上的掃描，但此進程仍得到了一個獨占的表鎖定。
因為此進程現在已有一個獨占的表鎖，所以可以保證沒有其他事務會修改任何數據行，能進行重復讀，且避免了由于holdlock所引起的潛在性死鎖。
但是，在使用表鎖定來盡可能地減少死鎖的同時，也增加了對表鎖定的爭用。因此，在實現這種方法之前，你需要權衡一下：避免死鎖是否比允許并發地對表進行訪問更重要。
所以，在這個事務中，沒有其他進程修改表中任何行的price。

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

八如何對行、 表、數據庫加鎖
1 如何鎖一個表的某一行

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

2 鎖定數據庫的一個表
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事務先更新table1表，在更新時，對其他事務進行排他
begin tran
update table1 set A='aa' where B='b2'；
waitfor delay '00:00:30'； --等待30秒
commit tran
-- A事務先更新table2表
begin tran
select * from table1 where B='b2';
commit tran

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

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

若并發執行上述兩個事務，則B事務中的select查詢可以執行,而update必須等待第一個事務釋放共享鎖轉為排它鎖后才能執行即要等待30秒
3）死鎖

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

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

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

十、應用程序鎖

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