處理死結 阻塞問題預備知識之SELECT,UPDATE,DELETE操作需要申請的鎖(二 UPDATE操作)

--處理死結 阻塞問題預備知識之SELECT,UPDATE,DELETE操作需要申請的鎖(二 UPDATE操作)

對於update語句,可以簡單地理解為SQL Server先做查詢,把需要修改的資料找

到後再在這個記錄上做修改。查詢動作需要加S鎖(共用鎖定),找到需要修改的記錄後

會先加U鎖,再將U鎖(更新鎖定)升級為X鎖(獨佔鎖定)。

這裡繼續使用[HumanResources].[Employee]和[HumanResources].[Employee_Test]

這兩張表做測試，看看SQL Server在UPDATE的時怎麼申請鎖的。繼續選用REPEATABLE

的隔離等級,運行一個UPDATE語句：

--串連A代碼:
USE AdventureWorks
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
UPDATE [HumanResources].[Employee_Test]
SET Title='ChangedHeap'
WHERE EmployeeID IN(3,30,200)
--ROLLBACK TRAN
--執行串連A後返回的結果(圖A):

--這時我們開啟一個新的串連(串連B)，執行以下代碼：

--串連B:
USE AdventureWorks
GO
SELECT
request_session_id,
resource_type,
request_status,
request_mode,
resource_description,
OBJECT_NAME(p.object_id) as OBJECT_NAME,
p.index_id
FROM
sys.dm_tran_locks t
left join
sys.partitions p
on
t.resource_associated_entity_id=p.hobt_id
order by
request_session_id,resource_type

--串連B代碼執行返回的結果(圖B):

--從圖B我們可以看出：

這個更新語句在非叢集索引上申請了3個U鎖(更新鎖定)

在RID上申請了3個X鎖(獨佔鎖定)

這個語句藉助非叢集索引PK_EMPLOYEE_Employee_Test(index_id為2)

找到這三條記錄。非叢集索引PK_EMPLOYEE_Employee_Test本身沒有

使用到Title這一列，所以它自己不需要修改。但是資料RID上有了修

改，所以RID加的是X鎖，其它索引上沒有加鎖。

從這個例子可以看出，如果UPDATE藉助了某個索引，這個索引的索引值

就會有U鎖，沒有用到的索引上沒有鎖。真正修改發生的地方會有X鎖

對於查詢涉及到的頁面SQL Server加了IU鎖，修改發生的頁面SQL Server

加了IX鎖

上面的情況是修改的列沒有被索引使用到，那麼假如修改的列有被索引
使用到，那麼又會是什麼情況呢？

下面我使用[HumanResources].[Employee]表來做測試：

首先我在[HumanResources].[Employee]表的Title欄位上建立一個非叢集索引

CREATE NONCLUSTERED INDEX Employee_Title_IDX ON

[HumanResources].[Employee]([Title] ASC) ON [PRIMARY]

GO

--接下來我們首先復原之前的那個事務：
ROLLBACK TRAN

--然後執行下面的更新語句:

BEGIN TRAN
UPDATE [HumanResources].[Employee]
SET Title='ChangedHeap'
WHERE EmployeeID IN(3,30,200)

--此時查詢A的執行計畫結果為(圖C):

--再運行串連B的語句:
USE AdventureWorks
GO
SELECT
request_session_id,
resource_type,
request_status,
request_mode,
resource_description,
OBJECT_NAME(p.object_id) as OBJECT_NAME,
p.index_id
FROM
sys.dm_tran_locks t
left join
sys.partitions p
on
t.resource_associated_entity_id=p.hobt_id
order by
request_session_id,resource_type
go

--該語句執行後返回的結果(圖D):

我們通過對圖D的分析可以知道:

語句利用叢集索引找到會修改的3條記錄。但是我們看到有9個鍵上有

X鎖。

PK_EMPLOYEE_EmployeeID(index_id=1)叢集索引,也是資料存放的地方

剛才的UPDATE語句沒有改到它的索引列,它只需把Title這個列的值改掉

所以在Index_id=1上，它只需申請三個X鎖，每條記錄一條

但是Title上面有一個非叢集索引Employee_Title_IDX(index_id=5)，並

且Title是第一列。它被修改以後。原來的索引索引值就要被刪除，並且插

入新的索引值，所以在index_id=5的索引上要申請6個X鎖，老的索引值3個新

的索引值3個

因為其它索引沒有使用到Title這一列，所以他們上面都沒有申請鎖

以上就為這9個key鎖的來源

從這兩個例子可以看出:

(1) 對每一個使用到的索引，SQL Server會對上面的索引值加U鎖

(2) SQL Server只對需要修改的記錄或索引值加X鎖

(3) 使用到要修改的列的索引越多，鎖的數目也會越多

(4) 掃描的頁面越多，意圖鎖定也會越多。在掃描過程中，對所有掃描到
      的記錄也會加鎖，哪怕上面沒有修改

所以，從以上規律來看，如果想降低一個UPDATE語句被別人阻塞的幾率

除了注意他的查詢部分以外，資料庫設計這還需要做的事情有:

(1) 盡量修改少的記錄。修改的記錄越多，需要的鎖也越多

(2) 盡量減少無所謂的索引。索引的數目越多，需要的鎖也會越多

(3) 盡量避免表掃描的發生。如果只是修改表的一小部分，盡量使用
index seek,避免全表掃描這種執行計畫的出現