SQL Server锁与事务隔离

  • 锁产生的背景
  • 锁资源模式与兼容性
  • 锁的粒度
  • 锁的类型
  • 锁的兼容性
  • 事务隔离
  • 事务隔离级别
  • 锁的申请与释放
  • 监视锁的申请、持有与释放


锁产生的背景

在关系型数据库中，事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须具备原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）四种属性（ACID）才能成为一个事务。

数据库引擎通过以下三种方法来保证每个逻辑事务的物理完整性：

  • 通过在访问不同资源时需要申请不同类型锁的方式，实现了不同事务之间的隔离（isolation）；
  • 通过先写入日志的方式，保证所有提交了的事务在硬盘上的日志文件里都有记录（即可以redo）；
  • 事务启动之后就必须成功完成，否则数据库引擎实例将撤销该事务启动之后对数据所做的所有修改（即undo）。

如果一个连接没有提交事务，SQL Server会使这个事务一直保持活动状态，直到该连接自己提交事务，或者登出（Logout）。如果登出时还有未提交的事务，SQL Server会将该事务范围内的所有操作回滚（Rollback）。

锁（Lock）是SQL Server实现事务隔离的一部分，阻塞（Blocking）正是事务隔离的体现。要实现事务的隔离，阻塞是事务对SQL Server提出的要求，也是使用事务要付出的代价。数据库开发者和管理员的工作，不是消除阻塞，而是要把阻塞的时间和范围控制在一个合理的界限之内，使用户既能享受事务的ACID，又能享受预期的性能。

要避免产生较为严重的阻塞，可以从申请资源的互斥度、锁的粒度和数目、事务持有锁资源的时间长度等方面进行考虑。

锁资源模式与兼容性

锁的粒度

锁定在较小的粒度上可以提高并发度，但是开销较高，因为需要维护很多锁；反之，锁定在较大的粒度上会降低并发度，但是开销较低。为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。

下表列出了数据库引擎可以在不同粒度上锁定的资源。

资源

说明

RID

堆（Heap）中的某一行

KEY

索引上的某一行，或者某个索引键

PAGE

数据库中的一个8KB页，例如数据页或索引页

EXTENT

一组连续的8页

HoBT

表下面的某一个分区（partition）

TABLE

包括所有数据和索引的整个表

FILE

数据库文件

APPLICATION

应用程序专用的资源

METADATA

元数据锁

ALLOCATION_UNIT

分配单元

DATABASE

整个数据库

锁的类型

下表显示了数据库引擎使用的资源锁模式。

锁模式

说明

共享锁（Shared locks, S）

用于不更改或不更新数据的读取操作，如SELECT。共享锁允许并发事务在封闭式并发控制下读取资源。资源上存在共享锁时，任何其他事务都不能修改数据。

更新锁（Update locks, U）

用于可更新的资源，防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。一次只有一个事务可以获得资源的更新锁，当事务真正修改数据时，将更新锁转换为排他锁。

排他锁（Exclusive locks, X）

用于数据修改操作，如INSERT, UPDATE, DELETE。使用排他锁时，任何其他事务都无法读取或修改数据，仅在使用NOLOCK提示或者未提交隔离级别时才会进行读取操作。语句在执行修改操作之前需要先读取数据，因此数据修改语句通常会先请求共享锁和排他锁。

意向锁（Intent locks, I）

放置在资源层次结构的一个级别上的锁，以保护较低级别资源上的共享或排他锁，包括意向共享（IS）、意向排他（IX）、意向排他共享（SIX）三种类型。例如，当读取表里的页面时，在请求页的共享锁之前，事务先在表级请求共享意向锁，以防止其他事务随后在表上获取排他锁修改整个表格。

架构锁 （Schema locks, Sch）

在执行依赖于表架构的操作时使用，包括架构修改（Sch-M）、架构稳定性（Sch-S）两种类型。数据库引擎在表数据定义语言（DDL）操作的过程中使用Sch-M锁，阻止其他用户对表格的访问；在编译和执行查询时使用Sch-S锁，阻止对表格进行修改性的DDL（如Create, Alter, Drop Table）和DML（Insert, Update, Delete）操作。

大容量更新锁（Bulk update locks, BU）

在对表进行大容量数据复制且指定了TABLOCK提示时使用。

键范围

当使用可序列化事务隔离级别时保护查询读取的行的范围，确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。在使用可序列化事务隔离级别时，键范围可以防止幻读、幻插入和幻删除。

锁的兼容性

锁的兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将被迫进入等待状态，也就是产生了阻塞。

下表显示了常见的锁模式的兼容性。

请求模式

现有授予模式

IS

S

U

IX

SIX

X

IS

是

是

是

是

是

否

S

是

是

是

否

否

否

U

是

是

否

否

否

否

IX

是

否

否

是

否

否

SIX

是

否

否

否

否

否

X

否

否

否

否

否

否

事务隔离

事务隔离级别

数据库有并发操作时，修改数据的事务会影响同时要去读取或者修改相同数据的其他事务。如果没有并发控制，则可能出现以下结果：

  • 脏读：当一个事务更新了数据但是并未提交，第二个事务把第一个事务所更改的数据读了出来。第二个事务读取的数据是临时的，因为第一个事务可能回滚撤销更新操作；
  • 不可重复读：在一个事务中读取了某一行的数据，这时候第二个事务修改了该行的数据，然后第一个事务再次读取这一行时发现读到的数据变了；
  • 幻读：在一个事务中，读取数据时发现没有特定的行，这时候第二个事务插入了该行数据，然后在第一个事务再次读取时会发现该行突然出现了。

数据库系统通过定义事务的隔离级别来定义使用哪一级别的并发控制。下表显示了不同隔离级别允许的并发副作用。

隔离级别

脏读

不可重复读

幻读

未提交读

是

是

是

已提交读

否

是

是

可重复读

否

否

是

快照

否

否

否

可序列化

否

否

否

未提交读（READ UNCOMMITTED）
在未提交读级别运行的事务，不会申请共享锁来阻止其他事务修改当前读取的数据，同时自己也不会被排他锁阻塞。因此，未提交读允许脏读，且只能保证不读取物理上损坏的数据。

已提交读（READ COMMITTED）
已提交读是数据库引擎的默认事务隔离级别，可以防止脏读。已提交读会申请使用共享锁防止其他事务修改当前事务正在读取的行，同时也会阻止当前事务读取其他事务已修改但尚未提交的行。并且语句运行完成后就会释放共享锁，而不是等到当前事务提交的时候。

可重复读（REPEATABLE READ）
可重复读对当前事务中的每个语句所读取的全部数据都设置了共享锁，并且直到事务完成提交才会释放。指定语句不能读取其他事务已经修改但尚未提交的行，并且其他事务不能修改当前事务读取的任何数据。但是，其他事务可以插入与当前事务语句搜索条件相匹配的新行，从而产生幻读。

快照（SNAPSHOT）
在任何一个修改之前，先对修改前的版本做一个复制，后续的一切读操作都会去读这个复制的版本，修改将创建一个新的版本。快照事务不会阻塞其他事务的更新操作，而是忽略数据的修改直接从版本化的行读取数据。因此，读写操作不会相互阻塞，缺点是虽然读到的不是脏数据，但是可能是个马上就要过期的数据。快照事务不能修改由其他事务修改的数据。

使用行版本控制的已提交读（READ_COMMITTED_SNAPSHOT）
与快照事务相同的是，使用行版本控制的已提交读会忽略其他事务所作的数据修改直接读取版本化的行。与快照事务不同的是，可以在其他事务提交数据更改后读取修改的数据，也能够更新由其他事务修改的数据。

可序列化（SERIALIZABLE）
SQL Server通过加范围锁来实现可序列化。范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内，可以阻止其他事务删除或插入任何行，并且在事务完成之前一直保持范围锁。其作用与在事务内所有SELECT语句中的所有表上设置HOLDLOCK相同。

锁的申请与释放

可以发现，SQL Server主要是通过对共享锁申请和释放机制的不同处理来实现不同事务隔离级别的。因此，如果阻塞发生在共享锁上，可以通过降低事务隔离级别得到缓解。

SQL Server在处理排他锁时，每个除SNAPSHOT的事务隔离级别都是再修改的时候申请，直到事务提交的时候才释放。因此，如果阻塞发生在排他锁上，是不能通过降低事务隔离级别得到缓解的。

监视锁的申请、持有与释放

检查一个连接当前持有的锁
可以使用sp_lock命令来列出当前所有连接（spid）持有的锁（Type, Resource, Mode）。也可以查询系统动态管理视图sys.dm_tran_locks。

SELECT request_ssesion_id, resource_type, resource_associated_entity_id,
	request_status, request_mode, resource_description
FROM sys.dm_tran_locks

也可以结合其他的动态管理视图，直接查出某个数据库上面的锁在哪些表格和索引上面。

USE dbname
GO
SELECT request_ssesion_id, resource_type, resource_associated_entity_id,
	request_status, request_mode, resource_description, p.object_id,
	OBJECT_NAME(p.object_id) AS object_name, p.*
FROM sys.dm_tran_locks LEFT JOIN sys.partitions p
ON sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id
WHERE resource_database_id = DB_ID('dbname')
ORDER BY request_session_id, resource_type, resource_associated_entity_id

监视语句执行过程中锁的申请和释放
管理员必须借助SQL Server Profiler。在定义一个跟踪（trace）的时候，选择Show all columns，再选择Lock:Accquired 和 Lock:Released。

References
[1] Main Concept of SQL Server Locking [2] INF: Understanding and resolving SQL Server blocking problems