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