SQL 事务的隔离

为了遵守 ACID 规则，事务必须与其他事务相隔离。这意味着在一个事务中使用的数据必须与其他事务相隔离。为了实现这种分离，每一个事务会锁住它使用的数据以防止其他事务使用它。锁定义在需要锁定的资源上，这些资源可以是索引、数据行或者表。 SQL Server 总会尝试精细地锁住资源。在大多数情况下，它会首先基于行级加锁。如果锁住的行太多，会提升锁至在表级。这个过程是自动完成的。在 SQL Server 中锁定数据的最常见锁资源为：

·     RID 在没有聚集索引的堆中用于锁定指定行的行标识。

·     KEY 锁定的一个索引的索引键。表中存在聚集索引时，此类型的锁用于锁住表中的一行。因为在聚集索引中，数据是索引的一部分。可以参见第 6 章学习详细了解索引的内部工作机制。

·     PAGE 数据库中锁定的 8 KB 大小的一个页。它可以是一个索引或者一个数据页。

·     TABLE 表锁用于为一个操作锁定一个表。

其他锁资源：

·     DB----- 数据库，由于 dbid 列已包含数据库的数据库 ID ，所以没有提供任何信息

·     FIL---- 文件

·     IDX---- 索引

·     EXT---- 区域 , 相邻的八个数据页或索引页构成的一组。正被锁定的扩展盘区中的第一个页码。页由 fileid:page 组合进行标识

使用锁一般都是为防止以下的情况发生：

·     脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

·     不 可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个 事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

·     幻觉读是指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

 

此 外，每一个锁都有一个特定的锁类型定义锁的行为。例如，如果事务希望防止其他事务更新数据但允许其他事务读取数据，那么在有些情况下可能为写操作锁住数 据。在其他情况下，要求排他地锁定数据以防止其他事务对数据的任何访问。这种行为通过锁的兼容性来实现。每一种锁类型的定义都在同样的资源上与一些来自其 他事务的特定锁兼容。由于一个特定的锁类型必须在 SQL Server 中授权所有数据访问操作，因此可以使用锁的兼容性来管理两个或两个以上的操作是否可以在同一时间用于同样的数据。 SQL Server 中最常用的锁类型为：

·         共享 (S) 锁 共享锁用于为读访问锁住数据。它们会防止其他事务更改数据，但不阻止读数据。共享锁与其他共享锁相兼容，这就允许多个事务在同一个被锁的资源上拥有一个共享锁。因此，事务可以并行地读同一个数据。

·         排他 (X) 锁 排他锁用于每一次数据的更新。它们会阻止其他事务访问数据，因此一个排他锁与其他锁都不兼容。

·         更新 (U) 锁 更新锁是共享锁的一种特例。它们主要用于对 UPDATE 语句的支持。在 UPDATE 语句中，数据必须在它被更新前读取。因此，这需要一种锁类型在它读自己的数据时不阻止其他事务读数据。然而，当 SQL Server 开始更新数据的时候，它必须提升锁类型为排他锁。对于这种读操作， SQL Server 使用与共享锁兼容但与其他更新锁不兼容的更新锁。因此，其他事务在数据由于 UPDATE 语句而被读取的时候可以读取，但其他 UPDATE 语句必须等待直到更新锁被释放。

·         意向 (I) 锁 意 向锁是前面几种锁类型的变体，包括意向共享锁、意向排他锁等等。它们用于在低层次的锁上保护高层次的不接受的锁。考虑一下这种情况：一个事务在表中的行上 有一个排他锁。此时不允许其他事务在整张表上获取排他锁。为了管理这种情况，会在高层次应用意向锁使其他事务知道一些资源已经在低层次上被锁定了。在这种 情况下，事务会在行上保持一个排他锁，同时使用一个排他意向锁锁定页和表。

其他的锁：

·         Null 没有得到资源的访问权限

·         Sch-S (Schema Stability) 对查询进行编译时。能防止加锁的对象被删除直到解锁

·         Sch-M (Schema Modification) 改变数据库结构时发生。能防止其他的事务访问加锁的对象

·         IS (Intent Shares) 意图共享锁。

·         SIU(Share Intent Update) 意图在维护资源的共享锁时，把更新锁放到锁层次结构的下层资源上

·         IS-S(Intent Share-shared) 复合键范围锁

·         IX(Intent Exclusive) 意图排他锁

·         SIX(Share Intent Exclusive)

·         S(Share) 共享锁

·         U(Update) 更新锁。防止死锁

·         Iin-Nul(Intent Insert-Null) 索引行层次的锁定，复合键范围锁

·         IS-X(Intent Share-Exclusive)

·         IU(Intent Update) 意图更新锁

·         IS-U(Intent Share Update) 串行更新扫描

·         X(Exclusive) 排他锁

·         BU 块操作使用的锁

监视锁

为了监视数据库中存在哪些锁，可以查询动态管理视图 sys.dm_tran_locks 。这个视图为数据库中当前存在的每一个单独的锁提供了一行数据。

BEGIN TRAN

 

SELECT

    FirstName , LastName , EmailAddress

FROM

    Person . Contact WITH ( HOLDLOCK )

WHERE

    ContactID = 15

为了检查事务使用了哪些锁，可以使用动态管理视图 sys.dm_tran_locks 。为了查询出只属于您的事务的锁，可以将这个视图与另一个称作 sys.dm_tran_current_transaction 的动态管理视图相联接。 sys.dm_tran_current_transaction 获取在当前连接中运行的事务的信息。在查询窗口中键入并执行以下 SELECT 语句来获取锁信息并提交事务：

SELECT

    resource_type ,

resource_associated_entity_id ,

request_mode , request_status

FROM

     sys.dm_tran_locks dml

INNER JOIN

     sys.dm_tran_current_transaction dmt

ON

    dml . request_owner_id = dmt . transaction_id

 

COMMIT TRAN

 

查询结果如：

 

resource_type

resource_associated_entity_id

request_mode

request_status

OBJECT

309576141

IS

GRANT

KEY

72057594043236352

S

GRANT

PAGE

72057594043236352

IS

GRANT

 

它显示了在聚集索引的一个键上存在一个共享锁 (request_mode = S) ，在其相应的页和表 Person.Contact 上分别存在一个意向共享锁 (request_mode = IS) 。在 request_status 列上的 GRANT 值意味着所有请求的锁都已经授权给这个事务。

现在查看更改 WHERE 子句以获取更多行时所发生的情况。按如下方式更改 WHERE 子句并执行整个事务：

BEGIN TRAN

 

SELECT

        FirstName , LastName , EmailAddress

FROM

        Person . Contact WITH ( HOLDLOCK )

WHERE

    ContactID < 7000

现在执行以下代码来检查锁：

SELECT

resource_type ,

resource_associated_entity_id ,

request_mode ,

request_status

FROM

         sys.dm_tran_locks dml

INNER JOIN

     sys.dm_tran_current_transaction dmt

ON

dml . request_owner_id = dmt . transaction_id

 

COMMIT TRAN

可以看出，对象资源类型上定义了一个共享锁，对于此例，这个对象资源是表 Person.Contact 。 SQL Server 认为对此事务保持一个表级锁比保持大约 7000 个键锁及其依赖的意向锁更简单、更快。由于 SQL Server 使用了一个表级锁，因此就不必使用意向锁。因为表在数据锁定层次结构中级别最高。为了找出哪个对象被锁定了，可以使用 OBJECT_NAME 函数。 OBJECT_NAME 将 Object ID 作为参数并会返回对象的名称。 ( 如果 resource_type 是 OBJECT 的话，列 resource_associated_entity_id 存储锁定对象的 Object ID) 。

为了看到 SQL Server 如何在数据更改锁定数据，键入并执行以下事务来 UPDATE Person.Contact 表上的数据并查询相关的锁。在结束处，执行一个 ROLLBACK TRAN 语句来放弃更改。

USE AdventureWorks ;

GO

BEGIN TRAN

UPDATE

    Person . Contact

SET

    Phone = '+43 555 333 222'

 

 

WHERE

    ContactID = 25

SELECT

resource_type ,

resource_associated_entity_id ,

request_mode ,

request_status

FROM

         sys.dm_tran_locks dml

INNER JOIN

     sys.dm_tran_current_transaction dmt

ON

dml . request_owner_id = dmt . transaction_id

 

ROLLBACK TRAN

结果如：

 

resource_type

resource_associated_entity_id

request_mode

request_status

METADATA

0

Sch-S

GRANT

PAGE

72057594043236352

IX

GRANT

OBJECT

309576141

IX

GRANT

KEY

72057594043236352

X

GRANT

可以看出， SQL Server 使用一个排他锁 (request_mode = X) 来锁住键。任何时候，只要数据发生变化， SQL Server 就会使用一个排他锁并保持它直到事务结束。如前所述， SQL Server 还在第一步执行 UPDATE 语句的时候使用了更新锁。由于在更新语句之后对锁进行了查询，因此行上的更新锁已经被提升为排他锁。还会再次看到页和表上有两个意向排他锁 (request_mode = IX) ，并且在 METADATA 资源类型上有一个称作 Sch-S 的锁。 Sch-S 锁是一种架构稳定性锁，用来防止其他事务在数据更新时更改表架构。在更新数据时，架构是不能更改的。