擦亮自己的眼睛去看SQL Server之談?wù)勬i機制
在談?wù)凷QLServer的鎖機制之前，來思考以下這個場景：當(dāng)你在酷暑的時候騎著自己的小車往目的地行走時，路上連續(xù)遇到幾個時間很長的紅燈，是不是很郁悶?有時候你可能實在受不了闖了個紅燈，其實在大部分情況下問題不大，如果通行的汽車很多那就不好說了。因為不遵守規(guī)則的人太多，都為了達到目的去走捷徑，不愿意等待。這樣才有了交警。交警的作用就是維護這些紅綠燈的規(guī)則。這些紅綠燈就像鎖一樣，鎖住或延長你去目的地的時間。但是如果沒有交警大家又不自由遵守紅綠燈規(guī)則會導(dǎo)致什么呢?大家想想都知道。
這個系列的一篇文章中提供的事務(wù)管理器中有個鎖管理器就是這里的交警。它維護著SQLServer中的鎖。前段提到的大部分情況指的就是在系統(tǒng)事務(wù)量不大的時候，這時候的鎖永遠不會是什么大問題。除非你知道你的系統(tǒng)永遠就給幾個人用，否則考慮到系統(tǒng)以后的并發(fā)量上升不出現(xiàn)數(shù)據(jù)與效率問題，那你得深入了解鎖機制。在研究鎖之前，假定你已經(jīng)了解事務(wù)的ACID概念，它是整個SQLServer的精髓所在。如果沒有事務(wù)那就不用談鎖了，除了事務(wù)需要鎖以外其他任何東西都需要這個讓SQL不自由的機制。說到底鎖是一個平衡并發(fā)與數(shù)據(jù)安全的機制，如果沒有鎖，任何SQL都能覆蓋其他SQL執(zhí)行的數(shù)據(jù)，那么數(shù)據(jù)會出現(xiàn)不一致的情況。如果鎖得太狠，那將影響數(shù)據(jù)庫系統(tǒng)的并發(fā)性以及效率(包括鎖本身帶來的額外開銷)。這時候就需要去權(quán)衡，SQLServer鎖管理器就充當(dāng)權(quán)衡這兩者關(guān)系的角色，如下圖所示：
SQLServer中鎖的知識點實在太多，比如鎖從模式上分為：共享鎖(S)、更新鎖(U)、排他鎖(X)、架構(gòu)鎖(Sch-S、Sch-M)、意向鎖(IS、IU、IX)、轉(zhuǎn)換鎖(SIX、SIU、UIX)、大容量更新鎖(BU);鎖從粒度上分為：數(shù)據(jù)庫鎖、文件鎖、表鎖、堆鎖、索引鎖、頁鎖、鍵鎖、區(qū)鎖、行鎖、應(yīng)用程序鎖、元數(shù)據(jù)鎖;鎖之間存在兼容性問題;鎖會根據(jù)情況進行升級;鎖控制不好會出現(xiàn)死鎖;悲觀鎖的隔離性：未提交讀、已提交讀、可重復(fù)讀、可序列化;樂觀鎖的隔離性：讀提交快照隔離、快照隔離;閂(shuan)鎖。。。隨便列下就一大堆問題要說清楚需要花很大篇幅。還是抱著與前幾篇文章的風(fēng)格，仔細分析一個具體的問題——鎖升級。
1、準(zhǔn)備
有一個動態(tài)管理視圖可以查看所有鎖：sys.dm_tran_locks，還有一個動態(tài)管理視圖可以查看哪些請求正在阻塞其他的請求：sys.dm_os_waiting_tasks
2、什么是鎖升級
鎖升級是指鎖的粒度由細向粗轉(zhuǎn)換。如：由行鎖轉(zhuǎn)成表鎖。
3、需要鎖升級嗎?
一般來說，鎖的粒度越小，并發(fā)性越好但是如果去鎖定的東西多就需要的鎖越多，這樣會消耗SQLServer的cpu與內(nèi)存。一個鎖占用內(nèi)存約為96字節(jié)，你算算如果用行鎖去鎖定百萬千萬的表需要多少內(nèi)存。而且管理鎖(創(chuàng)建鎖、維護鎖、銷毀鎖等)也是有代價的，會消耗cpu。 如果用一個大點的鎖就將這些百萬千萬的鎖合并成一個鎖了，管理起來也方便消耗資源也小。
4、什么時候出現(xiàn)鎖升級
SQLServer意識到鎖定的頁面或行數(shù)過大的時候發(fā)生。怎么意識到過大呢?由兩種方法識別：請求用于的鎖的數(shù)目超過鎖數(shù)目臨界值;鎖管理器為單獨一個查詢消耗過多的內(nèi)存超過內(nèi)存臨界值。有其他一個超過臨界值，SQLServer就會試圖升級。注意這里說的鎖數(shù)據(jù)以及內(nèi)存是值由同一個查詢發(fā)生的，而不是總共的。這里說的臨界值并不是固定的，SQLServer采用啟發(fā)式算法去動態(tài)調(diào)整。
5、控制鎖升級
SQLServer提供一些可以讓我們控制鎖升級的入口。在SQLServer2008中可以通過：
- alter table test
- set (lock_escalation = auto|table|disable)
我們還可以通過在代碼中顯示指定pagelock、tablock提示，會強制SQLServer使用更粗的鎖。不過這個設(shè)置不合理的話會導(dǎo)致并發(fā)降低。建議一般情況下不用，除非你很清楚這樣帶來的影響。
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();
- )
查看當(dāng)前鎖情況：
默認某個連接對整個數(shù)據(jù)庫有個共享鎖。
#p#
6.2循環(huán)插入幾十萬條記錄：
- while 1 = 1
- insert into test(Name) values ('kk')
插入時的鎖快照 :
從上圖中看出這個快照中有：三個數(shù)據(jù)庫共享鎖、一個頁級意向排他鎖、一個表級意向排他鎖、兩個行級排他鎖。
三個數(shù)據(jù)庫共享鎖：前面已經(jīng)提過，默認某個連接對整個數(shù)據(jù)庫有個共享鎖;
一個頁級意向排他鎖、一個表級意向排他鎖：在頁以及表級表示資源的一部分實際已經(jīng)有鎖進行保護，這樣的好處允許其他請求鎖在表頁級別上進行檢查，減少不必要的更細的鎖請求，提高性能。比如在這種情況下，如果允許alter操作那么這個操作就會等待因為這里有表級排他鎖，它提示alter操作該表有活動。
6.3 跟蹤Lock:Escalation事件
在profiler中設(shè)置只跟蹤Lock:Escalation事件，鎖升級事件。
6.4更新表中記錄：
- update test set name = 'name' where name = 'kk'
在profiler中看到了Lock:Escalation事件被觸發(fā)：
更新時的快照為(按順序)：
如上圖：此時update操作以排他鎖定它更新的行。
如上圖：此時update操作以排他鎖鎖定了整個表，以架構(gòu)穩(wěn)定鎖(Sch-S)鎖定它相關(guān)的元數(shù)據(jù)表。
如上圖：此時釋放了對元數(shù)據(jù)表的架構(gòu)穩(wěn)定鎖(Sch-S)鎖，剩下對整個表的排他鎖。
從上面的分析中，發(fā)現(xiàn)SQLServer鎖機制是有點復(fù)雜的，不過也是很有意思的。研究后，你會發(fā)現(xiàn)它真的很智能。今天分析就到此結(jié)束，文中如有描述不當(dāng)?shù)牡胤剑瑲g迎指出。共同進步才是硬道理。
原文鏈接：http://www.cnblogs.com/yueyue_jwfm/archive/2011/08/14/2138388.html
【編輯推薦】
- 微軟SQL Server增加對Hadoop的支持
- 數(shù)據(jù)挖掘邏輯體系結(jié)構(gòu)的內(nèi)容類型有哪些
- SQL Server數(shù)據(jù)挖掘之如何實現(xiàn)Web路徑流挖掘
- 整理索引碎片，提升SQL Server速度
