SQL server鎖的機制
2024-08-31 00:48:24
供稿：網友
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 列已包含表的對象 id，所以沒有提供任何信息
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 的設置是在執行或運行時設置，而不是在分析時設置。
