Sql Server之旅——第十三站深入的探討鎖機制

上一篇我只是做了一個堆表讓大家初步的認識到鎖的痙攣狀態，但是在現實世界上並沒有這麼簡單的事情，起碼我的表不會沒有索引對吧，還有就是我的表一定會有很多的連接過來，10:1的讀寫，很多碼農可能都會遇到類似神乎其神的死鎖，卡住，讀不出來，插不進入等等神仙的事情導致性能低下，這篇我們一起來探討下。

一：當select遇到性能低下的update會怎麼樣？

1. 還是使用原始的person表，插入6條數據，由於是4000字節，所以兩條數據就是一個數據頁，如下圖：

DROP TABLE dbo.Person
CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'aaaaa')
--插入6條數據，剛好3個數據頁
INSERT INTO dbo.Person DEFAULT VALUES
go 6

2. 爲了模擬性能低下的Update操作，我們開個顯式事務來更新ID=4的記錄，並且用profile看一下，如下圖：

BEGIN TRAN
UPDATE dbo.Person SET NAME='bbbbb' WHERE id=4

3. 然後我們開下另一個會話連接，讀取ID=6的記錄會是怎樣？好奇嗎？

SELECT * FROM Person WHERE ID=6

從上面流程你是否看到，當掃描到89號數據頁的slot1槽位的時候卡住了。。。我想你應該知道update正好已經給這條記錄加上了X鎖。。。如果你夠細心，你還會發現，給S鎖附加記錄的條件是在當引擎發現記錄所在的數據頁已經附加上了IX鎖的情況下，纔給該號數據頁下的每條記錄附加S鎖，對吧。。。好了，既然在Profile上面看不到了，我還是有其他辦法來判斷到底select語句現在處於什麼狀態。

4. 使用sys.dm_tran_locks來看當前各個連接持有鎖的狀態。

SELECT  l.request_session_id,
        DB_NAME(l.resource_database_id),OBJECT_NAME(p.object_id),
        l.resource_description,l.request_type,
        l.request_status,request_mode
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p
ON l.resource_associated_entity_id=p.hobt_id

仔細觀察上圖可以看到，當前有51和52號會話，51號在1:89:1槽位上使用了X鎖並且沒有釋放，52號此時也進入了1:89:1中，並且想給該RowID附加S鎖，但是你也知道S和X鎖是排斥的，所以很無奈的一直保持等待狀態。

二：使用索引或許可以幫你逃過一劫

當你看完上面的講敘，是不是有點害怕？要是在生產環境下出現了這種情況，那我們是不是死的很慘？那接下來使用索引是不是真的可以幫我們躲過一劫呢？一起看一看。

1. 新建索引index

-- 在ID列上建一個index
CREATE INDEX idx_person ON dbo.Person(ID)

2. 然後我們看下數據頁的分佈情況，可以看到下圖中78,89,90是表數據頁，93號爲索引數據頁。

DBCC TRACEON(2588,3604)
DBCC IND(Ctrip,Person,-1)

3. 繼續執行上面的那個慢update

BEGIN TRAN
UPDATE dbo.Person SET NAME='bbbbb' WHERE id=4

4. 激動人心的時刻來了，由於數據太少，所以我這裏強制讓引擎執行我創建的索引，看看結果怎樣？

居然沒卡住？現在是不是有一股強烈的好奇心來了,gogogo...馬上開啓profile，看看到底都發生了什麼？？？

仔細看完這個圖，是不是覺得很有意思呢？？？具體步驟如下：

第一步：給表（Object）加上IS鎖。

第二步：因爲要走索引，給93號索引數據頁加上IS鎖。

第三步：找到93號索引數據頁的目標key，給這個key加上S鎖，有人可能就會問了。。。這個key不就是6嘛，爲什麼這個key=(61005a25560e)，這是因爲每行索引記錄都有一個散列值，這個值就是根據索引的幾個字段散列出來的，好處就是防止你的索引長度過大，導致鎖這個記錄的時候太耗費鎖空間了。。。。如果你還是不太相信的話，我用DBCC給你看一看。