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

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

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

SQL Server中鎖的知識點實在太多，比如鎖從模式上分為：共享鎖（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（）;

）

查看當前鎖情況：

默認某個連接對整個數據庫有個共享鎖。

6.2循環插入幾十萬條記錄：

while 1 = 1

insert into test（Name） values （‘kk’）

插入時的鎖快照 ：

從上圖中看出這個快照中有：三個數據庫共享鎖、一個頁級意向排他鎖、一個表級意向排他鎖、兩個行級排他鎖。

三個數據庫共享鎖：前面已經提過，默認某個連接對整個數據庫有個共享鎖；

一個頁級意向排他鎖、一個表級意向排他鎖：在頁以及表級表示資源的一部分實際已經有鎖進行保護，這樣的好處允許其他請求鎖在表頁級別上進行檢查，減 少不必要的更細的鎖請求，提高性能。比如在這種情況下，如果允許alter操作那麼這個操作就會等待因為這裡有表級排他鎖，它提示alter操作該表有活 動。

6.3 跟蹤Lock:Escalation事件

在profiler中設置只跟蹤Lock:Escalation事件，鎖升級事件。

6.4更新表中記錄：

update test set name = ‘name’ where name = ‘kk’

在profiler中看到了Lock:Escalation事件被觸發：

更新時的快照為（按順序）：

如上圖：此時update操作以排他鎖定它更新的行。

如上圖：此時update操作以排他鎖鎖定了整個表，以架構穩定鎖（Sch-S）鎖定它相關的元數據表。

如上圖：此時釋放了對元數據表的架構穩定鎖（Sch-S）鎖，剩下對整個表的排他鎖。

從上面的分析中，發現SQLServer鎖機制是有點復雜的，不過也是很有意思的。研究後，你會發現它真的很智能。今天分析就到此結束，文中如有描述不當的地方，歡迎指出。共同進步才是硬道理。（來源：博客園）