--处理死锁 阻塞问题预备知识之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,避免全表扫描这种执行计划的出现