sql server中意向锁的作用

意向锁

数据库引擎使用意向锁来保护锁层次结构的底层资源，以防止其他事务对自己锁住的资源造成伤害，提高锁冲突检测性能。例如，当读取表里的页面时，在请求页共享锁（S锁）之前，事务在表级请求共享意向锁。这样可以防止其他事务随后在表上获取排他锁（X锁），修改整个表格。意向锁可以提高性能，因为数据库引擎仅在表级检查意向锁，确定事务是否能安全地获取该表上的锁，而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

如何理解上面这句话的意思呢？我们以一个实际例子来说明。

假如有一张Student，其中包含1000条数据，测试数据如下：

create table Student
(
    id int,
    name char(30),
    constraint pk_id primary key(id)
)

--3.插入1000条记录
SET NOCOUNT ON;
GO

DECLARE @i int;
SET @i = 1;
WHILE @i <= 1000 BEGIN
  INSERT INTO Student values(@i,'zhangsan'+cast(@i as char))
  SET @i = @i + 1;
 END;
GO

因为设置了id列为主键，那么sql server会自动为其添加聚集索引。假如我们使用如下语句更新表中的数据，

begin tran 
UPDATE Student SET name ='zhangsan' WHERE id=1000;

然后再查询当前的锁状态，执行如下TSQL

--查看所状态
SELECT request_session_id, resource_type, resource_associated_entity_id,
    request_status, request_mode, resource_description
    FROM sys.dm_tran_locks

其查询结果如下图所示：

我们可以看到在Object和Page上面加了IX锁，而在Key上面加了X锁，这三种类型的锁层次结果如下图所示：

那么在表上加一个意向排它锁(IX)有什么用呢？假如此时有另外一个事务要求对整张表加S锁，它需要判定能够对这张表加这个S锁。

1. 如果不使用意向锁的话，那么得遍历Key查看是否有与S锁冲突的锁，而我们上面加锁的那一条记录刚好是最后一条，那么就得遍历所有数据。现在只有1000条数据还好，数据量不大，如果是上千万或者过亿的话，那么消耗会非常大。
2. 如果使用意向锁的话，我们就不需要遍历数据，我们发现Key上面有X锁，那么会在表上面加一个IX锁，而IX锁与S锁冲突，因此加S锁失败，这样很快就得到了结果。

锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将被迫进入等待状态，阻塞也就随之产生。例如，如果一个事务申请了在某个资源上的排他锁（X锁），则在它释放排他锁（X锁）之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。另一种情况是，如果一个事务已经获得了某个资源上的共享锁（S锁），则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁（U锁）。但是，在第一个事务释放共享锁之前，其他事务无法获取排他锁。

表9-3显示了最常见的锁模式的兼容性。

表9-3  最常见的锁模式兼容性

	现有授予模式
请求模式	IS	S	U	IX	SIX	X
意向共享(IS) [WX2]	是	是	是	是	是	否
共享(S)	是	是	是	否	否	否
更新(U)	是	是	否	否	否	否
意向排他(IX)	是	否	否	是	否	否
意向排他共享(SIX)	是	否	否	否	否	否
排他(X)	否	否	否	否	否	否

锁的模式和兼容性是SQL Server预先定义好的，没有任何参数或配置能够去修改它们。但是可以通过隔离级别来控制申请锁和释放锁的时机，四个隔离级别中申请与释放S锁时机可以参考：数据库弱一致性四个隔离级别。但是申请锁的粒度，是数据库设计能够影响的。如果应用申请的锁粒度都比较小，产生阻塞的几率就会比较小。如果一个连接会经常申请页面级、表级，甚至是数据库一级的锁资源，程序产生阻塞的可能性就会很大。