sql server 鎖與事務撥雲見日(上)

 一.概述

　　講到sql server鎖管理時，感覺它是一個大話題，因為它不但重要而且涉及的知識點很多，重點在於要掌握高併發要先要掌握鎖與事務，涉及的知識點多它包括各式各樣的鎖，鎖的組合，鎖的排斥，鎖延伸出來的事務隔離級別， 鎖住資源帶來的阻塞，鎖之間的爭用造成的死鎖，索引數據與鎖等。這次介紹鎖和事務，我想分上下篇，上篇詳細介紹鎖，下篇介紹事務， 針對鎖與事務我想把我掌握的以及參考多方面資料，整合出來儘量說詳細。 最後說下，對於高級開發人員或DBA，鎖與事務應該是重點關註的，它就像是資料庫里的一個大boss，如完全掌握了它，資料庫就會像就像庖丁解牛一樣游刃有餘  哈哈 。

二.鎖的產生背景

　　在關係型資料庫里鎖是無處不再的。當我們在執行增刪改查的sql語句時，鎖也就產生了。鎖對應的就的是事務，不去顯示加tran就是常說的隱式事務。當我們寫個存儲過程希望數據一致性時， 要麼同時回滾，要麼同時提交，這時我們用begin tran 來做顯示事務。鎖的範圍就是事務。在sql server里事務預設是提交讀(Read Committed) 。
　　鎖是對目標資源（行、頁、區、表..）獲取所有權的鎖定，是一個邏輯概念，用來保存事務的ACID. 當多用戶併發同時操作數據時，為了避免出現不一致的數據，鎖定是必須的機制。 但同時如果鎖的數量太多，持續時間太長，對系統的併發和性能都沒有好處。

三.鎖的全面認識

　　3.1 鎖住的資源

　　我們知道sql server的存儲數據單元包括文件組,頁,區,行。鎖住資源範圍從低到高依次對應的是：行(RID/KEY)鎖，頁(PAGE)鎖, 表(OBJECT)鎖。可通過sp_lock查看，比如： 當我們操作一條數據時應該是行鎖， 大批量操作時是頁鎖或表鎖， 這是大批量操作會使鎖的數量越多，鎖就會自動升級 將大量行鎖合成多個頁鎖或表鎖，來避免資源耗盡。SQL SERVER要鎖定資源時，預設是從最底級開始鎖起(行) 。鎖住的常見資源如下：

　　　　下圖是通過sp_lock的查看的，顯示了鎖住的資源類型以及資源

　　3.2 鎖的類型及鎖說明

四 鎖的互斥(相容性)

　　在sql server里有個表，來維護鎖與鎖之間的相容性,這是sqlserver預先定義好的，沒有任務參數或配置能夠去修改它們。如何提高相容性呢？那就是在設計資料庫結構和處理sql語句時應該考慮，儘量保持鎖粒度小，這樣產生阻塞的概率就會比較小，如果一個連接經常申請頁面級，表級，甚至是資料庫級的鎖資源，程式產生的阻塞的可能性就越大。假設：事務1要申請鎖時，該資源已被事務2鎖住，並且事務1要申請的鎖與事務2的鎖不相容。事務1申請鎖就會出現wait狀態，直到事務2的鎖釋放才能申請到。 可通過sp_lock查看wait等待（也就是常說的阻塞）　

　　下麵是最常見的鎖模式的相容性

五. 鎖與事務關係

　　如今系統併發現象，引起的資源急用，出現的阻塞死鎖一直是技術人員比較關心的。這就涉及到了事務， 事務分五種隔離級別，每個隔離級別有一個特定的併發模式，不同的隔離級別中，事務里鎖的作用域,鎖持續的時間都不同，後面再詳細介紹事務。這裡看下客戶端併發下的鎖與事務的關係， 可以理解事務是對鎖的封裝，事務就是在併發與鎖之間的中間層。如下圖：

六. 鎖的持續時間

　　下麵是鎖在不同事務隔離級別里，所持續占用的時間：

　　6.1  SELECT動作要申請的鎖

　　　　我們知道select 會申請到共用鎖,下麵來演示下共用鎖在Repeatable 重覆讀的級別下，共用鎖保留到事件提交時才釋放。

　　　　具體是1.事務A設置隔離級別為Repeatable重覆讀，開啟事務運行且不提交事務。

　　　　　　　2.再打開一個會話視窗，使用sys.dm_tran_locks來分析查看事務的持有鎖。　

--開啟一個事務A， 設置可重覆讀， 不提交
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN 
SELECT  * FROM dbo.Product WHERE SID=204144　

--上面執行完後，打開另一會話查詢鎖狀態
SELECT  k.request_session_id,k.resource_type,k.request_status,k.request_mode,k.resource_description,
 OBJECT_NAME( p.object_id) as objectName,p.index_id FROM SYS.dm_tran_locks k LEFT JOIN SYS.PARTITIONS p
ON k.resource_associated_entity_id=p.hobt_id
ORDER BY request_session_id,resource_type

　　  先看看查詢單條語句的執行計劃,再看看鎖住的資源

 　　通過DMV查詢，我們看到：

　　　　(1)首先是鎖住DATABASE資源，是資料庫級別的共用鎖，以防止別人將資料庫刪除。

　　　　(2)鎖住OBJECT表資源，在Product表上加了意向共用鎖IS，以防止別人修改表的定義。

　　　　(3)鎖住了二個PAGE頁加了意向共用鎖IS，通過上面執行計劃可以看出來，查詢出來的數據是通過索引查詢50%，RID堆查詢50%。這條數據分佈在二個頁上，通過where SID來查找沒有完全走索引查找。

　　　　(4)通過第3點可以看出，數據1個頁是對應RID行，另一頁對應KEY行 二個共用鎖，堆位置1:112205:25  ，KEY的哈希值(70009fe3578a) 。

　　總結下：通過Repeatable 重覆讀，直要事務不提交，共用鎖一直會存在。針對想減少被別人阻塞或者阻塞別人的概率，能考慮事情有：1. 儘量減少返回的記錄，返回的記錄越多，需要的鎖也就越多，在Repeatable隔離級別及以上，更是容易造成阻塞。2.返回的數據如果是一小部份，儘量使用索引查找，避免全表掃描。3.可以的話，根據業務設計好最合適的幾個索引，避免通過多個索引找到結果。                                                  

　　4.2  UPDATE動作要申請的鎖

　　　　對於UPDATE需要先查詢，再修改。具體是查詢加S鎖，找到將要修改的記錄後先加U鎖，真正修改時升級成X鎖。還是通過上面的product表來演示具體：選用Repeatable級別，運行一個update語句(先kill 掉之前的會放52)　

--開啟一個事務， 設置可重覆讀， 不提交
BEGIN TRAN 
UPDATE    dbo.Product SET model='test'
 WHERE SID IN(10905,119921,204144)

　　通過 dmv查看，嚇一跳沒想到鎖住了這麼多資源，糾結 那下麵試著來分析下為什麼鎖住這麼多資源：使用sys.indexes查看index_id 的0,2,4各使用了什麼索引

　　SELECT  * FROM sys.indexes WHERE object_id= OBJECT_id('product')

　　(1)這個product表並沒有建聚集索引，是在堆結構上建立的非索聚索引，index_id=0 是堆， index_id=2和4 又是分別二個非索聚索引

　　(2)同樣在DATABASE和OBJECT資源 上都加了共用鎖。

　　(3)意向排它鎖IX，鎖住的Page共9頁 說明數據關聯了9頁，其中堆上3頁，ix_1非索聚索引上3頁，ixUpByMemberID非索聚索引上3頁。 

　　(4) 排它鎖X鎖住RID堆上3行，KEY索引上6行。大家可能會覺得奇怪明明只改三行的model值，為什麼會涉及到9行呢？  我來解釋下這個表是建了三個非聚集索引，其中ix_1索引里有包含列model，xUpByMemberID索引里也同樣有包含列model，還有model數據是在堆，當堆上數據修改後，model關聯的非聚集索引也要重新維護。如下圖

　　(5) 這裡還有架構鎖Sch-s ，鎖住了元數據。

　　總結：1.一定要給表做聚集索引，除了特殊情況使用堆結構。2.要修改的數據列越多，鎖的數目就會越多，這裡model就涉及到了9行維護。3. 描述的頁面越多，意向鎖就會越多，對掃描的記錄也會加鎖，哪怕沒有修改。所以想減少阻塞要做到：1).儘量修改少的數據集，修改量越多，需要的鎖也就越多。2) 儘量減少無謂的索引，索引的數目越多，需要的鎖也可能越多。3.嚴格避免全局掃描，修改表格記錄時，儘量使用索引查詢來修改。

　　4.3  DELETE動作要申請的鎖　　

BEGIN TRAN 
DELETE     dbo.Product WHERE SID =10905

　　 (1) 刪除了RID堆的數據，以及關聯的非聚集索引三個key的值分別是(2,5,4)

 　　(2) 在要刪除的4個page上加了意向排它鎖，同樣對應一個RID和三個KEY。

　 　(3)在OBJECT資源表上加了意向排它鎖。

　　　總結：在DELETE過程中是先找到符合條件的記錄，然後再刪除， 可以說是先SELECT後DELETE，如果有索引第一步查詢申請的鎖會比較 少。　對於DELETE不但刪除數據本身，還會刪除所有相關的索引鍵，一個表上的索引越多，鎖的數目就會越多，也容易阻塞。為了防步阻塞我們不能不建索引，也不能隨便就建索引，而是要根據業務建查詢絕對有利的索引。

　　4.4  INSERT動作要申請的鎖　

BEGIN TRAN 
INSERT into    dbo.Product VALUES('modeltest','brandtest',GETDATE(),9708,'test')

　　　　對於以上三種動作，INSERT相對簡單點，只需要對要插入數據本身加上X鎖，對應的頁加IX鎖，同步更新了關聯的索引三個key。

　　　　這裡新增跟刪除最終顯示的鎖一樣，但在鎖申請的過程中，新增不需要先查詢到數據s鎖，升級u鎖，再升級成X鎖。

七. 鎖的升級

　　7.1 使用profiler視窗查看實時的鎖升級

　　以單次批操作受影響的行數超過5000條時（鎖數量最大值5000），升級為表鎖。在sqlserver里可以選擇完全關掉鎖升級，雖然可以減少阻塞，但鎖記憶體會增加，降低性能還可能造成更多死鎖。

 鎖升級缺點：會給其它會話帶來阻塞和死鎖。鎖升級優點：減少鎖的記憶體開銷。

　　檢測方法：在profiler中查看lock:escalation事件類。通過查看Type列，可查看鎖升級的範圍，升級成表鎖（object是表鎖）

　　如下圖：

　　如果減少批操作量，就沒有看到升級表鎖, 可自行通過 escalation事件查看，下圖就是減少了受影響的行數。

　　總結：將批操作量受影響行數減少到5000以下，減少鎖的升級後，發生了更頻繁的死鎖，原因是多個page頁的爭用。後有人指出你先把並行度降下來(刪除500一下的數據可以不使用並行) 在語句中設置maxdop = 1 這樣應該不會死鎖了。具體原因還需具體分析。

　　7.2 使用dmv查看鎖升級

sys.dm_db_index_operational_stats返回資料庫中的當前較低級別 I/O、 鎖定、 閂鎖，和將表或索引的每個分區的訪問方法活動。

index_lock_promotion_attempt_count:資料庫引擎嘗試升級鎖的累積次數。

index_lock_promotion_count:資料庫引擎升級鎖的累積次數。

SELECT  OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] ,
        i.name AS index_name ,
        ddios.index_id ,
        ddios.partition_number ,
        ddios.index_lock_promotion_attempt_count ,
        ddios.index_lock_promotion_count ,
        ( ddios.index_lock_promotion_attempt_count
          / ddios.index_lock_promotion_count ) AS percent_success
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
                                    AND ddios.index_id = i.index_id
WHERE   ddios.index_lock_promotion_count > 0
ORDER BY index_lock_promotion_count DESC;

　　7.3 使用dmv查看頁級鎖資源爭用

　　page_lock_wait_count：資料庫引擎等待頁鎖的累積次數。

　　page_lock_wait_in_ms：資料庫引擎等待頁鎖的總毫秒數。

　　missing_index_identified：缺失索引的表。

SELECT  OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name ,
        i.name AS index_name ,
        ddios.index_id ,
        ddios.partition_number ,
        ddios.page_lock_wait_count ,
        ddios.page_lock_wait_in_ms ,
        CASE WHEN DDMID.database_id IS NULL THEN 'N'
             ELSE 'Y'
        END AS missing_index_identified
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
                                    AND ddios.index_id = i.index_id
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    database_id ,
                                    object_id
                          FROM      sys.dm_db_missing_index_details
                        ) AS DDMID ON DDMID.database_id = ddios.database_id
                                      AND DDMID.object_id = ddios.object_id
WHERE   ddios.page_lock_wait_in_ms > 0
ORDER BY ddios.page_lock_wait_count DESC;

八. 鎖的超時

 　　在sql server 里鎖預設是不會超時的，是無限的等待。多數客戶端編程允許用戶連接設置一個超時限制，因此在指定時間內沒有反饋，客戶端就會自動撤銷查詢, 但資料庫里鎖是沒有釋放的。

　　可以通 select @@lock_timeout  查看預設值是 " -1", 可以修改超時時間  例如5秒超時 set  lock_timeout  5000;

     下麵是查看鎖的等待時間， wait_time是當前會話的等待資源的持續時間(毫秒)

select  session_id, blocking_session_id,command,sql_handle,database_id,wait_type
,wait_time,wait_resource
from sys.dm_exec_requests 
where blocking_session_id>50