处理死锁 阻塞问题预备知识之SELECT,UPDATE,DELETE
操作需要申请的锁(三 DELETE 操作)
前面我们已经测试过了 SELECT 和 UPDATE 操作时所申请的锁
接下来我们再来测试一下 DELETE 操作和 INSERT 操作时需要
申请的锁
本次测试使用数据库连接的默认隔离级别:READ COMMITTED
USE AdventureWorks
GO
--查看当前连接的隔离级别:
DBCC USEROPTIONS
返回结果如下图(图1)所示:
图1
从图一的最后一行可以看到当前连接的隔离级别为 READ COMMITTED
现在我们来看看一个 DELETE 操作:
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
DELETE [HumanResources].[Employee_BTree]
WHERE LoginID='adventure-works\kim1'
此时的执行计划如下图(图2):
图2
接下来我们再来看连接B的代码执行后的结果(图3):
--连接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
图3
我的表[Employee_BTree]只创建了一个聚集索引
从上面的结果我们可以分析出:
从他的执行计划可以看到有这几部分:
聚集索引删除 前几行 聚集索引扫描
DELETE 语句在聚集索引(index_id=1)上申请了一个X锁(如图4)
图4
在它所在的页面上申请了一个IX锁(如图5所示)
图5
以上是 DELETE 语句在 READ COMMITTED 隔离级别下申请的锁
那么在 REPEATABLE READ 这个级别下，在有费聚集索引的情况下又会是什么样的呢？
下面我们对表[HumanResources].[Employee_Test]做一个跟上面相同的测试:
--更改该连接的隔离级别:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
--查看当前连接的隔离级别:
DBCC USEROPTIONS
GO
在执行下面的删除语句的时候先记得回滚之前的事务:
ROLLBACK TRAN
GO
--对表[HumanResources].[Employee_Test]测试:
BEGIN TRAN
DELETE [HumanResources].[Employee_Test]
WHERE LoginID='adventure-works\kim1'
--执行计划(如下图):
图6
从图6中我们可以看到执行计划改变:
之前的聚集索引删除变成了表删除
之前的前几行还是不改变
之前的聚集索引扫描变成了索引查找
那么他们申请的锁有变化吗？
接下来查看连接B的代码执行后返回的结果(如下图):
图7
从图7可以看到:
DELETE 操作在该非聚集索引(index_id=2,4)上各申请了一个X锁(图8)
图8
在他们所在的页面上申请了一个IX锁(图9)
图9
在发生修改的heap页面上申请了一个IX锁(图10)
图10
在相应的RID上申请了一个X锁(图11)
图11
从以上我们可以看到以下规律:
DELETE 过程实现查找到符合条件的记录 然后再删除 所以如果索引合适
第一步申请的锁就会少
DELETE 不但会把数据行本身删除 还会删除相关的索引键 所以一张表
的索引越多 锁的数目也就会越多 也就越容易发生阻塞
为了防止阻塞
我们不能绝对的不建索引 也不能随便的建很多索引 而是要建立对查
找有利的索引 对于没有使用到的索引 最好是去掉
INSERT操作较为简单 SQL Server 会对新插入的数据本身申请一个X锁
在发生变化的页面上申请一个 IX 锁
由于插入的数据是新增的 被其它连接用到的概率较小 所以阻塞发生的
几率很小 这里不做详细测试了 有兴趣的可以自己试试