SQL server鎖的機制

SQL server的所有活動(dòng)都會(huì )產(chǎn)生鎖。鎖定的單元越小，就越能越能提高并發(fā)處理能力，但是管理鎖的開(kāi)銷(xiāo)越大。如何找到平衡點(diǎn)，使并發(fā)性和性能都可接受是SQL Server的難點(diǎn)。
SQL Server有如下幾種瑣：
1、 共享鎖
用于只讀操作(SELECT)，鎖定共享的資源。共享鎖不會(huì )阻止其他用戶(hù)讀，但是阻止其他的用戶(hù)寫(xiě)和修改。
2、 更新鎖
更新鎖是一種意圖鎖，當一個(gè)事物已經(jīng)請求共享瑣后并試圖請求一個(gè)獨占鎖的時(shí)候發(fā)生更新瑣。例如當兩個(gè)事物在幾行數據行上都使用了共享鎖，并同時(shí)試圖獲取獨占鎖以執行更新操作時(shí)，就發(fā)生了死鎖：都在等待對方釋放共享鎖而實(shí)現獨占鎖。更新鎖的目的是只讓一個(gè)事物獲得更新鎖，防止這種情況的發(fā)生。
3、 獨占鎖
一次只能有一個(gè)獨占鎖用在一個(gè)資源上，并且阻止其他所有的鎖包括共享縮。寫(xiě)是獨占鎖，可以有效的防止’臟讀’
4、 意圖縮
在使用共享鎖和獨占鎖之前，使用意圖鎖。從表的層次上查看意圖鎖，以判斷事物能否獲得共享鎖和獨占鎖，提高了系統的性能，不需從爺或者行上檢查。
5、 計劃鎖
Sch-M,Sch-S。對數據庫結構改變時(shí)用Sch-M，對查詢(xún)進(jìn)行編譯時(shí)用Sch-S。這兩種鎖不會(huì )阻塞任何事物鎖，包括獨占鎖。

讀是共享鎖，寫(xiě)是排他鎖，先讀后更新的操作是更新鎖，更新鎖成功并且改變了數據時(shí)更新鎖升級到排他鎖。鎖的類(lèi)型有：
DB-----數據庫，由于 dbid 列已包含數據庫的數據庫 ID，所以沒(méi)有提供任何信息
FIL----文件
IDX----索引
PG-----頁(yè)，數據或索引頁(yè)。頁(yè)碼。頁(yè)由 fileid:page 組合進(jìn)行標識，其中，fileid 是 sysfiles 表中的 fileid，而 page 是該文件內的邏輯頁(yè)碼。
KEY----鍵,用于保護可串行事務(wù)中的鍵范圍
TAB----表，包括所有數據和索引在內的整個(gè)表。由于 ObjId 列已包含表的對象 ID，所以沒(méi)有提供任何信息
EXT----區域, 相鄰的八個(gè)數據頁(yè)或索引頁(yè)構成的一組。正被鎖定的擴展盤(pán)區中的第一個(gè)頁(yè)碼。頁(yè)由 fileid:page 組合進(jìn)行標識
RID----行，表內已鎖定行的行標識符。行由 fileid:page:rid 組合進(jìn)行標識，其中，rid 是頁(yè)中的行標識符

鎖的狀態(tài)：
Grant---能使用被授權的資源
Wait----能使用被其他任務(wù)阻塞的資源
Cnvrt---Convert,鎖正在被轉換

細分鎖的模式：
0 Null 沒(méi)有得到資源的訪(fǎng)問(wèn)權限
1 Sch-S (Schema stability) 對查詢(xún)進(jìn)行編譯時(shí)。能防止加鎖的對象被刪除直到解鎖
2 Sch-M (Schema Modification) 改變數據庫結構時(shí)發(fā)生。能防止其他的事物訪(fǎng)問(wèn)加鎖的對象
3 IS (Intent Shares) 意圖共享鎖。
4 SIU(Share Intent Update) 意圖在維護資源的共享鎖時(shí)，把更新鎖放到鎖層次結構的下層資源上
5 IS-S(Intent Share-shared) 復合鍵范圍鎖
6 IX(Intent Exclusive) 意圖排他鎖
7 SIX(Share Intent Exclusive)
8 S(Share) 共享鎖
9 U(Update) 更新鎖。防止死鎖
10 Iin-Nul(Intent Insert-Null) 索引行層次的鎖定，復合鍵范圍鎖
11 IS-X(Intent Share-Exclusive)
12 IU(Intent Update) 意圖更新鎖
13 IS-U(Intent Share Update) 串行更新掃描
14 X(Exclusive) 排他鎖
15 BU 塊操作使用的鎖

所以有如下的結論。

1、一個(gè)連接在修改數據塊時(shí)別的連接不能修改這個(gè)數據塊，直到解鎖。
并行訪(fǎng)問(wèn)是任何數據庫解決方案都最為重視的問(wèn)題了，為了解決并行訪(fǎng)問(wèn)方面的問(wèn)題各類(lèi)數據庫系統提出了各種各樣的方案。SQL Server采用了多線(xiàn)程機制，它當然能夠一次處理多個(gè)請求。不過(guò)，在用戶(hù)修改數據的情況下并行訪(fǎng)問(wèn)問(wèn)題就變得復雜起來(lái)了。顯然，數據庫通常只允許唯一用戶(hù)一次修改特定的數據。當某一用戶(hù)開(kāi)始修改某塊數據時(shí)， SQL Server能很快地鎖定數據，阻止其他用戶(hù)對這塊數據進(jìn)行更新，直到修改該數據的第一位用戶(hù)完成其操作并提交交易或者回滾。但是，當某一位用戶(hù)正在修改某塊數據時(shí)假設另一位用戶(hù)又正想查詢(xún)該數據的信息時(shí)會(huì )發(fā)生什么情況呢？
2、通常情況下，一個(gè)連接在修改數據塊時(shí)別的連接也不能查詢(xún)這個(gè)數據塊，直到解鎖。反之亦然：讀的時(shí)候不能寫(xiě)和修改。這個(gè)方案會(huì )降低系統的性能和效率，盡管現在是行級鎖（7.0以前是鎖頁(yè)甚至是鎖表），如果你一次修改多行數據，SQL Server則會(huì )把數據鎖定范圍提升到頁(yè)級別乃至鎖定整個(gè)數據表，從而不必針對每一記錄跟蹤和維護各自的數據鎖，這樣能加快修改的速度，消耗小的服務(wù)器資源，但是并發(fā)性就差了。。
3、一個(gè)連接寫(xiě)的時(shí)候，另一個(gè)連接可以寫(xiě)，但是不得讀
4、多個(gè)連接可以同時(shí)讀同一行。

所以鎖發(fā)生在讀、寫(xiě)的競爭上。

5、設置事物的級別 SET TRANSACTION ISOLATION LEVEL
A、READ COMMITTED ：指定在讀取數據時(shí)控制共享鎖以避免臟讀，但數據可在事務(wù)結束前更改，從而產(chǎn)生不可重復讀取或幻像數據。該選項是 SQL Server 的默認值。
B、READ UNCOMMITTED：執行臟讀或 0 級隔離鎖定，這表示不發(fā)出共享鎖，也不接受排它鎖。當設置該選項時(shí)，可以對數據執行未提交讀或臟讀；在事務(wù)結束前可以更改數據內的數值，行也可以出現在數據集中或從數據集消失。這是四個(gè)隔離級別中限制最小的級別。
C、REPEATABLE READ：鎖定查詢(xún)中使用的所有數據以防止其他用戶(hù)更新數據，但是其他用戶(hù)可以將新的幻像行插入數據集，且幻像行包括在當前事務(wù)的后續讀取中。因為并發(fā)低于默認隔離級別，所以應只在必要時(shí)才使用該選項。
D、SERIALIZABLE：在數據集上放置一個(gè)范圍鎖，以防止其他用戶(hù)在事務(wù)完成之前更新數據集或將行插入數據集內。這是四個(gè)隔離級別中限制最大的級別。因為并發(fā)級別較低，所以應只在必要時(shí)才使用該選項。該選項的作用與在事務(wù)內所有 SELECT 語(yǔ)句中的所有表上設置 HOLDLOCK 相同。
注釋
一次只能設置這些選項中的一個(gè)，而且設置的選項將一直對那個(gè)連接保持有效，直到顯式更改該選項為止。這是默認行為，除非在語(yǔ)句的 FROM 子句中在表級上指定優(yōu)化選項。
SET TRANSACTION ISOLATION LEVEL 的設置是在執行或運行時(shí)設置，而不是在分析時(shí)設置。