SqlServer 鎖詳解

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語句
Java代碼
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  

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 如何鎖一個表的某一行
Java代碼
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'
2 鎖定資料庫的一個表
select col1 from 表 (tablockx) where 1=1 ;
加鎖後其它人不可操作，直到加鎖使用者解鎖，用commit或rollback解鎖
3.執行個體
建表
Java代碼
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’);  

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）排它鎖
Java代碼
-- 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  

-- 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）共用鎖定
Java代碼
-- 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  

-- 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）死結
Java代碼
-- 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事務先更新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： 為應用程式資源解鎖
）使用儘可能低的隔離性層級。隔離性層級是指為保證資料庫資料的完整性和一致性而使多使用者事務隔離的程度，SQL92定義了4種隔離性層級：未提交讀、提交讀、可重複讀和可串列。如果選擇過高的隔離性層級，如可串列，雖然系統可以因實現更好隔離性而更大程度上保證資料的完整性和一致性，但各事務間衝突而死結的機會大大增加，大大影響了系統效能。
7）使用Bound Connections。Bound connections 允許兩個或多個事務串連共用事務和鎖，而且任何一個事務串連要申請鎖如同另外一個事務要申請鎖一樣，因此可以允許這些事務共用資料而不會有加鎖的衝突。
8）考慮使用樂觀鎖定或使事務首先獲得一個獨佔鎖定。

八如何對行、 表、資料庫加鎖
1 如何鎖一個表的某一行
Java代碼
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   
SELECT * FROM table1 ROWLOCK WHERE A = 'a1'  

如果能有更多的 隔離等級對應鎖的詳細對比說明,就更好