Sql Server 最佳化----SQL語句的執行方式與鎖以及阻塞的關係

標籤：asc   ide   重要   不相容   logs   gif   開啟   and   javascrip   

　　阻塞原因之一是不同的Session在訪問同一張表的時候因為不相容鎖的原因造成的， 　　當前執行的SQL語句是否被阻塞（或者死結），不僅跟當前表上的已有的鎖有關，也會跟當前執行的SQL語句的執行方式有關 　　簡單來說，對於表的訪問方式，SQL語句的執行無非是表掃描，索引掃描，（叢集索引或者非叢集索引）索引尋找等等 　　如果SQL語句的執行方式不當或者沒有合理的索引，會造成沒必要的阻塞，如果邏輯控制不當，甚至造成更嚴重的問題，造成資料邏輯的錯誤

 

建個測試表，下面測試示範一下

create table testIndexAndLock(    id int identity(1,1),    col2 varchar(50))GOinsert into testIndexAndLock values (NEWID())GO 100000

 

　　很常見的業務就是，當前Session中開啟了事物，執行更新或者刪除某一行資料，然後再進行一系列其他的操作，當前事物提交之前，該排它鎖一致保持，直到事物提交

　　比如下面：

　　第一個Session中利用事物和排他性伺服器用戶端檔案鎖一行資料，，進行商務邏輯處理，按道理來說：目的僅僅是鎖定當前行的資料（id = 6666），進行排他性操作，並沒有鎖定其的資料行

　　

　　然後再第二個Session中，查詢其他的無關的Id，按道理，上面一個Session只是鎖定了id = 6666的行，怎麼當前Session查詢Id = 7777的行的時候會被阻塞呢？

　　

　　這裡就是上面說的對當前表的訪問方式了，因為當前查詢的表上沒有任何索引，在查詢id = 7777的時候，雖然id = 7777的行上沒有任何鎖，為什麼查詢還是被阻塞？ 　　這裡在執行查詢id = 7777的時候，用的是全表掃描的方式執行的，此時遇到Id = 6666的這行資料的時候，因為這行資料上有排它鎖，當前Session被阻塞 　　可以簡單理解為當前Session會漸進式掃描表中的資料行，在掃描的過程中，不管是表也好，資料行也好，如果遇到相容的鎖，可以正常訪問， 　　如果遇到不相容的鎖，比如這裡的查詢是共用鎖定（s鎖），遇到第一個Session中的id = 6666的這行資料的時候，發現上面是排它鎖， 　　此時，當前Session要對該行加的鎖（共用鎖定）與該行上的排它鎖（x鎖）不相容，就只能等待，知道id = 6666這行資料上的排它鎖釋放或者變化與當前請求的共用鎖定相容的鎖，才能執行 　　如果id = 6666上面的排它鎖一直沒有釋放或者變化為相容共用鎖定的鎖類型，當前Session就一直等待 　　表現為當前Session被阻塞 　　這種問題解決辦法也很簡單，類似現象如果要不被阻塞，要麼等到第一個Session的排它鎖釋放，要麼就換一種查詢的方式 　　嘗試在表的Id列上建立一個索引，當然叢集索引也可以，目的是防止查詢的時候以全表掃描的方式進行

　　

　　在第二個Session中執行上面的查詢，查詢Id = 7777的資料行，可以發現這個查詢可以順利完成

　　

　　這個就是通過索引改變查詢的執行計畫，避免全表掃描的時候遇到任何一行資料上有排它鎖造成阻塞的情況。 　　這裡說的索引，並不僅僅是為了提高效率，更重要的是改變了查詢的執行方式，避免遇到表上有排他性鎖的鎖在成阻塞的情況。

 

索引的作用不僅僅是提高查詢效率，更重要的是避免全表掃描的過程中遇到排它鎖被阻塞的情況，對減少阻塞有一定的協助作用。

Sql Server 最佳化----SQL語句的執行方式與鎖以及阻塞的關係