擦亮自己的眼睛去看SQL Server之談談鎖機制(1)

在談談SQLServer的鎖機制之前，來思考以下這個情境：當你在酷暑的時候騎著自己的小車往目的地行走時，路上連續遇到幾個時間很長的紅燈，是不是很鬱悶?有時候你可能實在受不了闖了個紅燈，其實在大部分情況下問題不大，如果通行的汽車很多那就不好說了。因為不遵守規則的人太多，都為了達到目的去走捷徑，不願意等待。這樣才有了交警。交警的作用就是維護這些紅綠燈的規則。這些紅綠燈就像鎖一樣，鎖住或延長你去目的地的時間。但是如果沒有交警大家又不自由遵守紅綠燈規則會導致什麼呢?大家想想都知道。

這個系列的一篇文章中提供的交易管理員中有個鎖管理器就是這裡的交警。它維護著SQLServer中的鎖。前段提到的大部分情況指的就是在系統事務量不大的時候，這時候的鎖永遠不會是什麼大問題。除非你知道你的系統永遠就給幾個人用，否則考慮到系統以後的並發量上升不出現資料與效率問題，那你得深入瞭解鎖機制。在研究鎖之前，假定你已經瞭解事務的ACID概念，它是整個SQLServer的精髓所在。如果沒有事務那就不用談鎖了，除了事務需要鎖以外其他任何東西都需要這個讓SQL不自由的機制。說到底鎖是一個平衡並發與資料安全的機制，如果沒有鎖，任何SQL都能覆蓋其他SQL執行的資料，那麼資料會出現不一致的情況。如果鎖得太狠，那將影響資料庫系統的並發性以及效率(包括鎖本身帶來的額外開銷)。這時候就需要去權衡，SQLServer鎖管理器就充當權衡這兩者關係的角色，如所示：

SQLServer中鎖的知識點實在太多，比如鎖從模式上分為：共用鎖定(S)、更新鎖定(U)、獨佔鎖定(X)、架構鎖(Sch-S、Sch-M)、意圖鎖定(IS、IU、IX)、轉換鎖(SIX、SIU、UIX)、大容量更新鎖定(BU);鎖從粒度上分為：資料庫鎖、檔案鎖、表鎖、堆鎖、索引鎖、頁鎖、鍵鎖、區鎖、行鎖、應用程式鎖、中繼資料鎖;鎖之間存在相容性問題;鎖會根據情況進行升級;鎖控制不好會出現死結;悲觀鎖的隔離性：未提交讀、已提交讀、可重複讀、可序列化;樂觀鎖的隔離性：讀提交快照隔離、快照隔離;閂(shuan)鎖。。。隨便列下就一大堆問題要說清楚需要花很大篇幅。還是抱著與前幾篇文章的風格，仔細分析一個具體的問題——鎖定擴大。

1、準備

有一個動態管理檢視可以查看所有鎖：sys.dm_tran_locks，還有一個動態管理檢視可以查看哪些請求正在阻塞其他的請求：sys.dm_os_waiting_tasks

2、什麼是鎖定擴大

鎖定擴大是指鎖的粒度由細向粗轉換。如：由行鎖轉成表鎖。

3、需要鎖定擴大嗎?

一般來說，鎖的粒度越小，並發性越好但是如果去鎖定的東西多就需要的鎖越多，這樣會消耗SQLServer的cpu與記憶體。一個鎖佔用記憶體約為96位元組，你算算如果用行鎖去鎖定百萬千萬的表需要多少記憶體。而且管理鎖(建立鎖、維護鎖、銷毀鎖等)也是有代價的，會消耗cpu。 如果用一個大點的鎖就將這些百萬千萬的鎖合并成一個鎖了，管理起來也方便消耗資源也小。

4、什麼時候出現鎖定擴大

SQLServer意識到鎖定的頁面或行數過大的時候發生。怎麼意識到過大呢?由兩種方法識別：請求用於的鎖的數目超過鎖數目臨界值;鎖管理器為單獨一個查詢消耗過多的記憶體超過記憶體臨界值。有其他一個超過臨界值，SQLServer就會試圖升級。注意這裡說的鎖資料以及記憶體是值由同一個查詢發生的，而不是總共的。這裡說的臨界值並不是固定的，SQLServer採用啟發學習法演算法去動態調整。

5、控制鎖定擴大

SQLServer提供一些可以讓我們控制鎖定擴大的入口。在SQLServer2008中可以通過：

 

  
alter table test 
  
set (lock_escalation = auto|table|disable) 
 

我們還可以通過在代碼中顯示指定pagelock、tablock提示，會強制SQLServer使用更粗的鎖。不過這個設定不合理的話會導致並發降低。建議一般情況下不用，除非你很清楚這樣帶來的影響。

6、舉例說明6.1建庫建表：

 

  
create database Test 
  
create table test 
  
( 
  
ID identity(1,1) primary key, 
  
[Name] varchar(50) not null default '', 
  
CreatedTime datetime not null default getdate(); 
  
) 
 

查看當前鎖情況：

預設某個串連對整個資料庫有個共用鎖定。