18.4 SQL Server事务与锁详解之（锁篇）

SQL Server中的锁是用来同步用户对数据的访问的。

大家在多线程编程时，当多个线程要访问同一个资源时，一般都需要对资源进行加锁，保证线程一个一个的访问数据，即线程同步，这样就可以保证多个线程对资源的访问是安全的。

SQL Server中的锁也是这样的，每个用户查询对应一个线程，当多个用户要访问同一个数据时，就需要对数据进行加锁，这样就可以保证多个用户对数据的访问是安全的。本文将介绍SQL Server中的锁的相关知识。

事务在访问数据之前，为了保证不受其他事务的干扰，需要请求对数据进行锁定。

锁有多种类型，比如共享锁和排他锁。如果一个事务已经获得了数据上某特定的锁，那么其他事务就不能再获得会与该锁发生冲突的锁，直到该事务释放了该锁，这个叫锁的兼容性。

当事务修改某数据时，无论什么隔离级别，都会持有保护它修改数据的锁，直到事务提交或回滚。事务的隔离级别决定了事务持有保护读取数据的锁的时间。

我们的应用程序一般不直接请求锁，锁是由SQL Server的锁管理器自动管理的。当执行一个事务时，SQL Server查询处理器先确定要访问的资源，然后会根据事务的隔离级别、SQL语句访问类型向锁管理器请求适当的锁，如果请求的锁与其他事务持有的锁不会发生冲突，则授予该锁，若发生冲突，则会被阻塞。

SQL Server可以让事务锁定不同的资源，比如表、行、页、键范围、索引等。

锁的粒度越小，锁的数量越多，并发越高，但是锁的开销也就越大，比如锁定在较小粒度的行上，如果一个SQL需要访问很多行，则需要持有大量的锁，每把锁的开销大概是96kb，如果需要访问10000行，则需要持有10000+

把锁，这样就会消耗很多的内存。

锁的粒度越大，锁的数量越少，但是并发就会降低，比如锁定在较大粒度的表上，因为锁住整个表导致了其他事务对表中任意部分的访问。 但开销较低，因为需要维护的锁较少。

什么是锁的层次结构呢？

SQL Server通常需要获取多个级别的锁才能完全保护资源， 这组多粒度级别上的锁就称为锁层次结构。

比如，当一个事务要更新一个表中的一行时，SQL Server会先获取表上的意向排他锁（IX），然后再获取行级别上的排他锁（X），还要获取索引上行的排他锁等，最后才能更新数据。

SQL Server可以锁定的资源如下：

我们可以通过查询sys.dm_tran_locks视图或sp_lock来查看当前事务锁定的资源。

比如通过sys.dm_tran_locks：

执行结果如下：

我们更新了一行数据，可以看到，事务在表（OBJECT）和页（PAGE）上各加了一把意向排他锁（IX），因为表account有主键聚集索引，所以通过在索引的键（KEY）上加了一把排他锁（X）锁住行，否则就锁住堆中的数据行（RID）。

或者：

SQL Server中的锁模式有以下几种：

共享锁（S 锁）允许并发事务在并发控制下读取 (SELECT) 资源。 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。

读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁），比如：

这样事务从开始到结束都会持有表上的共享锁（S 锁），这相当于串行化隔离级别。

排他锁（X 锁）允许并发事务在并发控制下修改 (UPDATE、DELETE、INSERT) 资源。 资源上存在排他锁（X 锁）时，任何其他事务都不能修改数据。仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作，因为此时的读取操作不会加锁，也就不会与资源上的排他锁冲突。

更新锁（U 锁）是共享锁（S 锁）和排他锁（X 锁）的混合。更新锁（U 锁）允许并发事务在并发控制下读取 (SELECT) 资源。 资源上存在更新锁（U 锁）时，任何其他事务都不能修改数据。

更新锁（U 锁）的使用场景是：先查出所需的数据，此时为共享锁（S 锁），为了保护这数据不会被其它事务修改，加上更新锁（U 锁），在真正开始更新时，自动转成排他锁（X 锁）。更新锁（U 锁）和共享锁（S 锁）兼容， 但排他锁（X 锁）和更新锁（U 锁）不兼容。

更新锁可以防止常见的死锁，比如在可重复读和串行化隔离级别下，事务 A 读取数据（S锁），事务 B 读取同一数据（S锁），事务 A 更新数据需要申请排他锁（U锁），但是数据上存在事务B的S锁，S锁与X锁冲突，所以事务B会被阻塞，事务 B 更新数据也要申请排他锁（U锁），也被阻塞，这时事务 A 和事务 B 都会相互阻塞，等待对方释放锁，造成死锁。

上述发生死锁示例SQL：

事务1：先执行如下事务1，10秒内执行事务2

事务2：

执行顺序：

若在类似要先读取数据再更新数据的事务中，使用了更新锁（U 锁），则不会发生死锁：

事务1：

事务2：

执行顺序：

这样一来，事务2就会等到事务1执行完毕再执行，就不会发生死锁了。

意向锁（I 锁）是为了用来保护将共享锁（S锁）或排他锁（X锁）放置锁层次结构中较低资源上的锁。相当于只是一种标志，不是真正的锁。

根据名字“意向锁”，用大白话来说就是“我想要锁住这个资源，但是我还没有锁住，所以我先标记一下，其他事务看到这个标记，就不会做一些冲突的操作了”。

比如，我想在表中某些行上加排他锁（X锁），我在行上加排他锁之前，我先在表上加个意向锁排他锁（IX锁），表示我有将表里边数据加排他锁（X锁）的意向，然后再在行上加排他锁（X锁），其他事务如果想要在表上加共享锁或者排他锁甚至修改表结构之类的冲突操作，看到表上有我的意向排他锁（IX锁），就知道表中的数据有被上X锁，因此不需要去检查表中的行有没有被锁，这样也就提高了效率。

所以意向锁有两个作用：

意向锁主要有如下几种：

SQL Server数据库引擎在表数据定义语言 (DDL) 操作（例如添加列或删除表）期间会使用架构修改 (Sch-M) 锁。 持有锁期间，Sch-M 锁将阻止其他用户对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。

SQL Server数据库引擎在编译和执行查询时， 使用架构稳定 锁(Sch-S)。 Sch-S 锁不会阻止某些事务锁，其中包括排他 (X) 锁。 因此，在编译查询的过程中，其他事务（包括那些针对表使用 X 锁的事务）将继续运行。 但是，无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。

大容量更新锁（Bulk Update Lock），允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。 当以下两个条件都成立时，SQL Server数据库引擎使用此锁。

键范围锁在使用串行化事务隔离级别时，保护由 T-SQL 语句读取的记录集中隐式包含的行范围。 键范围锁可防止虚拟读取。 通过保护行之间键的范围，它还防止对事务访问的记录集进行虚拟插入或删除。

详情请参见：键范围锁

在 SQL Server 中，锁兼容性是指在同一资源上可以同时存在的锁的类型。

例如，如果一个事务在表上持有共享锁（S锁），另一个事务可以在表上持有共享锁（S锁），和更新锁（U锁），但不能持有排他锁（X锁）。

排他锁与任何锁都不兼容，因此如果一个事务在表上持有排他锁，另一个事务不能在表上持有任何锁。

下表是常见的锁兼容性。

注意，意向排他锁（IX）与意向排他锁兼容（IX），因为IX只打算更新部分行而不是所有行，还允许其他事务读取或更新其他部分行。如果两个事务尝试更新同一行，两个事务都会在表级和页级上IX锁，但是只有一个事务会在行级上X锁，另一个事务会必须等待这个行上X锁释放。

首先，要记住一点，无论什么隔离级别，即使是最低的读未提交（Read Uncommitted），亦或者是快照隔离级别，事务在更新或者删除数据时（Update，delete），都会在数据上加排他锁，并且持续到事务结束。这样可以防止其他事务同时在这个数据上进行更新或者删除。

然后不同的隔离级别，会对读操作加不同的共享锁，以保证读到的数据是符合隔离级别要求的。

可以使用如下语句查看当前事务占用的锁

比如：

下面是不同隔离级别下，读操作加的锁。

读未提交（Read Uncommitted）是最低的隔离级别，这个隔离界别下，事务对数据的读取（SELECT）是不会加共享锁（S锁）的。因此可以读取几乎任何状态的数据，比如可以读取有排他锁（X锁）的数据，也可以读取有共享锁（S锁）的数据。这样就会导致脏读（Dirty Read）的问题。

演示：

一个窗口执行如下事务1，先不提交：

可以看到，事务获取了account表行1的排他锁（X锁）。以及表和页上的意向排他锁（IX锁）。

另一个窗口执行如下事务2进行查询：

可以看到，事务成功读取到了数据，没有被阻塞。

因为这个隔离级别下的事务，读取数据根本就不加共享锁。

最后提交事务1:

读已提交（Read Committed）是默认的隔离级别，这个隔离级别下，事务对数据的读取（SELECT）会申请共享锁（S锁），但是一旦读取完成，即SELECT语句执行完，就会立即释放这个共享锁（S锁）。

如果其他事务对这个数据进行了更新（UPDATE，DELETE），那么，在其他事务提交之前，数据上就会有其他事务的排他锁，该读取数据的事务要申请相同数据的共享锁（S锁）就会被阻塞，这样就可以避免脏读（Dirty Read）的问题。

演示：

一个窗口执行如下事务1，先不提交：

同样可以看到，事务获取了account表行1的排他锁（X锁）。

另一个窗口执行如下事务2进行查询：

可以看到，事务被阻塞了，因为事务1还没有提交，资源上存在事务1的排他锁（X锁），因此事务2申请共享锁（S锁）被阻塞了。

直到事务1提交事务，然后释放account表行1的排他锁（X锁），事务2才能成功获取account表行1的共享锁（S锁），最终结束阻塞，读取到数据。

可重复读（Repeatable Read）隔离级别下，事务对数据的查询（SELECT）会申请共享锁（S锁），但是查询完成后，不会立即释放这个共享锁（S锁），而是在事务提交之前一直持有这个共享锁（S锁）。这样在事务提交之前，其他事务就不能修改同一数据，修改同一数据的事务需要申请排他锁（X锁），就会被阻塞，不可重复读（Nonrepeatable Read）的问题，同时也避免脏读问题。

演示：

一个窗口执行如下事务1：

可以看到，事务在读取数据后，不会立即释放共享锁（S锁），而是在事务提交之前一直持有这个共享锁（S锁）。

串行化（Serializable）隔离级别下，事务对数据的查询（SELECT）会申请键范围共享锁（RangS-S），也会持续到事务提交之前。

键范围会锁定一个范围内的所有行，比如：