SQL鎖機制

今天公司進行了一個有關鎖機制的培訓，講的內容其實大學都學過，但發現很多東西結合了實際專案的時候才理解更深了，發此博文做個記錄，作為以後參考。

　　大綱

　　基本概念
　　死鎖
　　避免死鎖
　　處理死鎖

　　鎖的概念　　鎖主要用於多使用者環境下保證資料庫完整性和一致性。
　　　　　　　　多個使用者能夠同時操縱同一個資料庫中的資料，會發生資料不一致現象，如果沒有鎖定且多個使用者同時訪問一　　　　 個資料庫，則當他們的事務同時使用相同的資料時可能會發生問題。這些問題包括：丟失更新、髒讀、不可重複讀等。

　　鎖的粒度 

資源	級別	描述
RID	行鎖	表中的單個行
Key	行級鎖	索引中的行
Page	頁級鎖	一個資料頁或者索引頁
Extent	頁級鎖	一組資料頁或者索引頁
Table	表級鎖	整個表
Database	資料庫級鎖	整個資料庫

 　　鎖的模式 

鎖模式	描述
共享（S）	用於不更改或不更新資料（只讀操作），如SELECT語句
更新（U）	用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。
排它（X）	用於資料修改操作，例如INSERT、UPDATE或DELETE。確保不會同時對同一資源進行多重更新
意向	SQL Server有在資源的低層獲得共享鎖或排它鎖的意向意向鎖的型別為：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX）
架構	在執行依賴於表架構的操作時使用。架構鎖的型別為：架構修改（Sch-M）和架構穩定（Sch-S）
大容量更新（BU）	向表中大容量複製資料並指定了TABLOCK提示時使用

　　死鎖   死鎖是指在一組程式中的各個程式均佔有不會釋放的資源，但因互相申請被其他程式所站用不會釋放的資源而處於的一種永久等待狀態。

　　死鎖的四個必要條件

　　互斥條件(Mutual exclusion)：資源不能被共享，只能由一個程式使用。
　　請求與保持條件(Hold and wait)：已經得到資源的程式可以再次申請新的資源。
　　非剝奪條件(No pre-emption)：已經分配的資源不能從相應的程式中被強制地剝奪。
　　迴圈等待條件(Circular wait)：系統中若干程式組成環路，該環路中每個程式都在等待相鄰程式正佔用的資源
　　死鎖的排查

 　　使用SQL Server的系統儲存過程sp_who和sp_lock，可以檢視當前資料庫中的鎖情況；進而根據objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以檢視哪個資源被鎖，用dbcc ld(@blk)，可以檢視最後一條發生給SQL Server的Sql語句。

　　避免死鎖

　　死鎖的四個必要條件，我們只要想辦法破其中的任意一個或多個條件，就可以避免死鎖發生。
　　按同一順序訪問物件，避免出現迴圈。
　　避免事務中的使用者互動，減少持有資源的時間，較少鎖競爭。
　　保持事務簡短並處於一個批處理中，減少持有資源的時間。
　　使用較低的隔離級別，使用較低的隔離級別（例如已提交讀）比使用較高的隔離級別（例如可序列化）持有共享鎖的時間更短，減少鎖競爭。
 　　使用基於行版本控制的隔離級別，使用基於行版本控制的隔離級別：2005中支援快照事務隔離和指定READ_COMMITTED隔離級別的事務使用行版本控制，可以將讀與寫操作之間發生的死鎖機率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON --事務可以指定SNAPSHOT事務隔離級別;
SET READ_COMMITTED_SNAPSHOT ON  --指定READ_COMMITTED隔離級別的事務將使用行版本控制而不是鎖定。

　　死鎖的處理方法

　　檢視那個spid處於wait狀態，然後用kill spid來幹掉(即破壞死鎖的第四個必要條件:迴圈等待)。
　　使用SET LOCK_TIMEOUT timeout_period(單位為毫秒)來設定鎖請求超時。預設情況下，資料庫沒有超時期限(timeout_period值為-1，可以用SELECT @@LOCK_TIMEOUT來檢視該值，即無限期等待)。
　　SQL Server內部有一個鎖監視器執行緒執行死鎖檢查，鎖監視器對特定執行緒啟動死鎖搜尋，檢測到死鎖後，資料庫引擎選擇執行回滾開銷最小的事務的會話作為死鎖犧牲品回滾死鎖犧牲品的事務並釋放該事務持有的所有鎖，使其他執行緒的事務可以請求資源並繼續執行。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-617119/，如需轉載，請註明出處，否則將追究法律責任。