十九、SqlServer锁

一、SqlServer概述

SQL Server 数据库支持多个用户同时访问数据库，但当用户同时访问数据库时，就会造成并发问题，锁的机制能很好地解决这个问题，保证数据的完整性和一致性；

SQL Server 自带锁机制，若是简单的数据库访问机制，完全能满足用户的需求；但对于数据完全与数据完整性有特殊要求，就必须自动控制锁机制解决；

二、什么是SQL Server 锁机制

锁是处理 SQL Server 中并发问题的最有效手段，当多个事务访问同一数据时，能很好地保证数据的完整性和一致性；

在很多数据库系统中（如DB2、MySQL、Oracle）都有锁机制，其规则也大同小异；

在 SQL Server 中采用系统来管理锁，SQL Server 中采用的是动态加锁的机制；

SQL Server 中有一套默认的锁机制，若用户在使用数据库的过程中不设置任何锁，系统将自动对锁管理；

三、SQL Server锁模式

在 SQL Server 中有不同的锁，在各种锁的类型中有些是能相互兼容的，锁的类型决定了并发

发生时数据资源的范文模式，在 SQL Server 中常用的锁以下 5 种；

3.1、共享锁（Shared lock）

又称读锁（S锁），共享锁不阻塞其他事务的读操作，但阻塞写操作，同一数据对象A可以共存多个共享

锁，这被称为共享锁兼容。

 

当Session1为数据对象A加上共享锁后，可以对A进行读操作，但不能进行写操作，并且Session2可以

再次对A加共享锁，大家都可以正常地读A，但是在A上的共享锁释放之前，任何事务不可以对A进行写

操作。

例 1：

Session1： select * from [ZhaoxiEdu].[dbo].[StudentsScore]
Session2： update [ZhaoxiEdu].[dbo].[StudentsScore] set Python=80

理解：

假设Session1先执行，则Session2必须等待Session1执行完才可以执行。

因为Session2为写操作，需要为table加一个排他锁，而数据库规定相同资源不可以同时存在共享锁和

排他锁，所以Session2必须等待Session1执行完，释放掉共享锁，才可以加排他锁，然后执行

update。

例 2： (可能发生死锁)

Session1：
begin Transaction t1
select * from [ZhaoxiEdu].[dbo].[StudentsScore] with (holdlock) -- (holdlock)共享锁
update [ZhaoxiEdu].[dbo].[StudentsScore] set Python=80
Session2：
begin Transaction t2
select * from [ZhaoxiEdu].[dbo].[StudentsScore] with (holdlock) -- (holdlock)共享锁
update [ZhaoxiEdu].[dbo].[StudentsScore] set Python=67

理解：

分析：

假设Session1和Session2同时到达select语句，都为table加上了共享锁，那么当Session1、Session2

要执行update时，根据锁机制，共享锁需要升级为排他锁，但是排他锁与共享锁不能共存，要给table

加排他锁，必须等待table上的共享锁全部释放才可以，可是holdlock的共享锁必须等待事务结束才能

释放，因此Session1和Session2都在等待对方释放共享锁，形成循环等待，造成死锁。

例3：

Session1： update [ZhaoxiEdu].[dbo].[StudentsScore] set Python=60 where Id=4
Session2： update [ZhaoxiEdu].[dbo].[StudentsScore] set Python=65 where Id=4

理解：

此种情况有可能造成等待，分为id列有索引与无索引两种情况。

（1）id列有索引，则Session1直接定位到Id=4行，加排他锁，更新；Session2直接定位到Id=4行，

加排他锁，更新。互不影响。

（2）id列无索引，Session1扫描全表，找到Id=4行，加排他锁后，Session2为了找到Id=4行，需要

全表扫描，那么就会为table加共享锁或更新锁或排他锁，但不管加什么锁，都需要等待Session1释放

Id=4行的排他锁，不然无法为全表加锁。

死锁可以通过直接对表加排他锁来解决，即将事务的隔离级别提高至最高级——串行读，各个事务串行

执行，可是这样虽然避免了死锁，但是效率太低了。

3.2、更新锁（Update lock）

更新锁（U锁）。当Session1给资源A加上更新锁后，代表该资源将在稍后更新，更新锁与共享锁兼

容，更新锁可以防止例2里那种一般情况的死锁发生，更新锁会阻塞其他的更新锁和排他锁。因此相同

资源上不能存在多个更新锁。

更新锁允许其他事务在更新之前读取资源。

但不可以修改。因为其他事务想获取资源的排他锁时，发现该资源已存在U锁，则等待U锁释放。

在Session1找到需要更新的数据时，更新锁直接转为排他锁，开始更新数据，不需要等待其他事务释放

共享锁啥的。

那么就问了，共享锁为什么不可以直接升级为排他锁，而必须等待其他共享锁都释放掉才可以转为排他

锁呢？

这就是共享锁和更新锁的一个区别了，共享锁之间是兼容的，但是更新锁之间互不兼容，因此仅有一个

更新锁直接转为排他锁是安全的，而多个共享锁问也不问直接转为排他锁，那怎么行呢，排他锁只能有

一个的，这就是为什么共享锁需要等待其他共享锁释放才可以升级为排他锁的原因了。

案例分析

例 4：

Session1:
begin
select * from [ZhaoxiEdu].[dbo].[StudentsScore] with (updlock) ---(加更新锁)
update [ZhaoxiEdu].[dbo].[StudentsScore] set C#=70 --(重点：这里Session1做update时，不需要等
Session2释放什么，而是直接把更新锁升级为排他锁，然后执行update)

Session2:
begin
select * from [ZhaoxiEdu].[dbo].[StudentsScore] --（T1的更新锁不影响T2的select）
update [ZhaoxiEdu].[dbo].[StudentsScore] set C#=75 --（T2的update需要等待T1的update执行完）

理解：

（1）Session1先到达，Session1的select句对table加更新锁，此时Session2紧接着到达，Session2

的select句对table加共享锁，假设Session2的select先执行完，要开始Session2的update，发现table

已有更新锁，则Session2等，Session1此时执行完select，然后将更新锁升级为排他锁，开始更新数

据，执行完成，事务结束，释放排他锁，此时Session2才开始对table加排他锁并更新。

（2）Session2先到，Session1紧接着，Session2加共享锁 => Session1加更新锁 => 假设Session2

先结束select => 试图将共享锁升级为排他锁 => 发现已有更新锁 => 之后的情况相同；

3.3、排他锁

又叫独占锁，写锁，X锁，很容易理解，排他锁阻塞任何锁，假设Sesssion1为资源A加上排他锁，则其

他事务不允许对资源A进行任何的读写操作。

案例分析

例 5：(假设id都是自增长且连续的）

Session1: update [ZhaoxiEdu].[dbo].[StudentsScore] set C#=70 where id<5
Session2: update [ZhaoxiEdu].[dbo].[StudentsScore] set C#=75 where id>5

理解：

假设Session1先达，Session2随后至，这个过程中Session1会对id<5的记录施加排他锁.但不会阻塞

Session2的update。

例6

Session1: update [ZhaoxiEdu].[dbo].[StudentsScore] set C#=60 where id<5
Session2: update [ZhaoxiEdu].[dbo].[StudentsScore] set C#=60 where id>2

理解：

假设Session1先达，Session2立刻也到，Session1加的排他锁会阻塞Session2的update。

3.4、意向锁

意向锁，就是说当你给数据加锁时，必须先给他的上级加锁，用来向其他事务表明这段数据中的某些数

据正在被加某某锁，你看着办吧。其实是一个节省开销的做法。

例7

Session1:
begin tran
select * from [ZhaoxiEdu].[dbo].[StudentsScore] with (xlock) where id=5
--意思是对id=5这一行强加排他锁
Session2:
begin tran
select * from [ZhaoxiEdu].[dbo].[StudentsScore] with (tablock) --意思是要加表级锁

理解：

假设Session1先执行，Session2后执行，Session2执行时，欲加表锁，为判断是否可以加表锁，数据库系统要逐条判断table表每行记录是否已有排他锁，如果发现其中一行已经有排他锁了，就不允许再加表锁了。只是这样逐条判断效率太低了。

 

实际上，数据库系统不是这样工作的。当Session1的select执行时，系统对表table的id=5的这一行加了排他锁，还同时悄悄的对整个表加了意向排他锁(IX)，当Session2执行表锁时，只需要看到这个表已经有意向排他锁存在，就直接等待，而不需要逐条检查资源了。

 

常用的意向锁有三种：意向共享锁（Intent Share Lock，简称IS锁）；意向排他锁（Intent Exclusive Lock，简称IX锁）；共享意向排它锁（Share Intent Exclusive Lock，简称SIX锁），共享意向排它锁的意思是，某事务要读取整个表，并更新其中的某些数据。

四、锁的粒度

SQL Server 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源；

为了减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别；锁定在较小的粒度（如行）能提高并发度，但开销较高，因为若锁定了许多行，就需要持有更多的锁；锁定在加大的粒度（如表）会降低并发，因为锁定整个表限制了其他事务对表中任意部分的访问；但其开销较低，因为需要维护的锁较少； 数据库引擎通常必须获取多粒度级别上才能完整地保护资源，多粒度级别上的所称为层次结构；

五、查看锁

在 SQL Server数据库中，能通过查看 sys.dm_tran_locks 返回 SQL Server 数据库中有关当前活动的

锁的管理的信息；向锁管理器发出的已授予锁或正等待授予锁的每个当前活动请求分别对应一行；结果

集中的列大体分为两组：资源组和请求组；

select * from sys.dm_tran_locks

六、死锁

在两个或多个任务中，若每一个任务都锁定了其他的资源，就会造成永久的阻塞，这种情况就是死锁；

形成死锁有以下 4 个必要条件：

1. 互斥条件：资源不能被共享，只能被一个进程使用；

2. 请求与保持条件：已获得资源的进程能同时申请其他资源；

3. 非剥夺条件：已分配的资源不能从该进程中被剥夺；

4. 循环等待条件：多个进程构成环路，且每个进程都在等待相邻进程正在使用的资源；

 

在一个复杂的数据库系统中很难百分之百地避免死锁，但能按照以下的访问策略减少死锁的发生；

1. 所有事务中以相同的次序使用资源；避免出现循环；

2. 减少事务持有资源的时间，避免事务中的用户交互；

3. 让事务保持在一个批处理中；

4. 由于锁的隔离级别越高共享锁的时间就越长，因此能降低隔离级别来达到减少竞争的目的；

 

注意： SQL Server 数据库引擎自动检测 SQL Server 中的死锁循环；数据库引擎选择一个会话作为死锁的牺牲品，然后终止当前事务（出现错误）来打断死锁。