SQL server鎖的機制

SQL server鎖的機制

SQL server的所有活動都會產生鎖。鎖定的單元越小，就越能越能提高並發處理能力，但是管理鎖的開銷越大。如何找到平衡點，使並發性和效能都可接受是SQL Server的痛點。
SQL Server有如下幾種瑣：
1、 共用鎖定
用於唯讀操作(SELECT)，鎖定共用的資源。共用鎖定不會阻止其他使用者讀，但是阻止其他的使用者寫和修改。
2、 更新鎖定
更新鎖定是一種意圖鎖，當一個事物已經請求共用瑣後並試圖請求一個獨佔鎖的時候發生更新瑣。例如當兩個事物在幾行資料行上都使用了共用鎖定，並同時試圖擷取獨佔鎖以執行更新操作時，就發生了死結：都在等待對方釋放共用鎖定而實現獨佔鎖。更新鎖定的目的是只讓一個事物獲得更新鎖定，防止這種情況的發生。
3、 獨佔鎖
一次只能有一個獨佔鎖用在一個資源上，並且阻止其他所有的鎖包括共用縮。寫是獨佔鎖，可以有效防止’髒讀’
4、 意圖縮
在使用共用鎖定和獨佔鎖之前，使用意圖鎖。從表的層次上查看意圖鎖，以判斷事物能否獲得共用鎖定和獨佔鎖，提高了系統的效能，不需從爺或者行上檢查。
5、 計劃鎖
Sch-M,Sch-S。對資料庫結構改變時用Sch-M，對查詢進行編譯時間用Sch-S。這兩種鎖不會阻塞任何事物鎖，包括獨佔鎖。

讀是共用鎖定，寫是獨佔鎖定，先讀後更新的操作是更新鎖定，更新鎖定成功並且改變了資料時更新鎖定升級到獨佔鎖定。鎖的類型有：
DB-----資料庫，由於 dbid 列已包含資料庫的資料庫 ID，所以沒有提供任何資訊
FIL----檔案
IDX----索引
PG-----頁，資料或索引頁。頁碼。頁由 fileid:page 組合進行標識，其中，fileid 是 sysfiles 表中的 fileid，而 page 是該檔案內的邏輯頁碼。
KEY----鍵,用於保護可串列事務中的鍵範圍
TAB----表，包括所有資料和索引在內的整個表。由於 ObjId 列已包含表的物件識別碼，所以沒有提供任何資訊
EXT----地區, 相鄰的八個資料頁或索引頁構成的一組。正被鎖定的擴充盤區中的第一個頁碼。頁由 fileid:page 組合進行標識
RID----行，表內鎖定行的行標識符。行由 fileid:page:rid 組合進行標識，其中，rid 是頁中的行標識符

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

細分鎖的模式：
0 Null 沒有得到資源的存取權限
1 Sch-S (Schema stability) 對查詢進行編譯時間。能防止加鎖的對象被刪除直到解鎖
2 Sch-M (Schema Modification) 改變資料庫結構時發生。能防止其他的事物訪問加鎖的對象
3 IS (Intent Shares) 意圖共用鎖定。
4 SIU(Share Intent Update) 意圖在維護資源的共用鎖定時，把更新鎖定放到鎖階層的下層資源上
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、一個串連在修改資料區塊時別的串連不能修改這個資料區塊，直到解鎖。
並行訪問是任何資料庫解決方案都最為重視的問題了，為瞭解決並行訪問方面的問題各類資料庫系統提出了各種各樣的方案。SQL Server採用了多線程機制，它當然能夠一次處理多個請求。不過，在使用者修改資料的情況下並行訪問問題就變得複雜起來了。顯然，資料庫通常只允許唯一使用者一次修改特定的資料。當某一使用者開始修改某塊資料時， SQL Server能很快地鎖定資料，阻止其他使用者對這塊資料進行更新，直到修改該資料的第一位使用者完成其操作並提交交易或者復原。但是，當某一位使用者正在修改某塊資料時假設另一位使用者又正想查詢該資料的資訊時會發生什麼情況呢？
2、通常情況下，一個串連在修改資料區塊時別的串連也不能查詢這個資料區塊，直到解鎖。反之亦然：讀的時候不能寫和修改。這個方案會降低系統的效能和效率，儘管現在是行級鎖（7.0以前是鎖頁甚至是鎖表），如果你一次修改多行資料，SQL Server則會把資料鎖定範圍提升到頁層級乃至鎖定整個資料表，從而不必針對每一記錄跟蹤和維護各自的資料鎖，這樣能加快修改的速度，消耗小的伺服器資源，但是並發性就差了。。
3、一個串連寫的時候，另一個串連可以寫，但是不得讀
4、多個串連可以同時讀同一行。

所以鎖發生在讀、寫的競爭上。

5、設定事物的層級 SET TRANSACTION ISOLATION LEVEL
A、READ COMMITTED ：指定在讀取資料時控制共用鎖定以避免髒讀，但資料可在事務結束前更改，從而產生不可重複讀取或幻像資料。該選項是 SQL Server 的預設值。
B、READ UNCOMMITTED：執行髒讀或 0 級隔離鎖定，這表示不發出共用鎖定，也不接受排它鎖。當設定該選項時，可以對資料執行未提交讀或髒讀；在事務結束前可以更改資料內的數值，行也可以出現在資料集中或從資料集消失。這是四個隔離等級中限制最小的層級。
C、REPEATABLE READ：鎖定查詢中使用的所有資料以防止其他使用者更新資料，但是其他使用者可以將新的幻像行插入資料集，且幻像行包括在當前事務的後續讀取中。因為並發低於預設隔離等級，所以應只在必要時才使用該選項。
D、SERIALIZABLE：在資料集上放置一個範圍鎖，以防止其他使用者在事務完成之前更新資料集或將行插入資料集內。這是四個隔離等級中限制最大的層級。因為並發層級較低，所以應只在必要時才使用該選項。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 HOLDLOCK 相同。
注釋
一次只能設定這些選項中的一個，而且設定的選項將一直對那個串連保持有效，直到顯式更改該選項為止。這是預設行為，除非在語句的 FROM 子句中在表級上指定最佳化選項。
SET TRANSACTION ISOLATION LEVEL 的設定是在執行或運行時設定，而不是在分析時設定。