sql server锁（lock）知识及锁应用

妖狐艹你老母 2022-05-28 13:15 271阅读 0赞

提示：这里所摘抄的关于锁的知识有的是不同sql server版本的，对应于特定版本时会有问题。

## 一 关于锁的基础知识 ##

###    (一). 为什么要引入锁 ###

当多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:

◆丢失更新

A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统

◆脏读

A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致

◆不可重复读

A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致

并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

### (二) 锁的分类 ###

◆锁的类别有两种分法：

1. 从数据库系统的角度来看：分为独占锁（即排它锁），共享锁和更新锁

MS-SQL Server 使用以下资源锁模式。

锁模式 描述

共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

意向锁 用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

架构锁 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。

◆共享锁

共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

◆更新锁

更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

◆排它锁

排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据

◆意向锁

意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

锁模式 描述

意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。

意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。

与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。

◆独占锁：

只允许进行锁定操作的程序使用，其他任何对他的操作均不会被接受。执行数据更新命令时，SQL Server会自动使用独占锁。当对象上有其他锁存在时，无法对其加独占锁。

共享锁：共享锁锁定的资源可以被其他用户读取，但其他用户无法修改它，在执行Select时，SQL Server会对对象加共享锁。

◆更新锁：

当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。

2. 从程序员的角度看：分为乐观锁和悲观锁。

◆乐观锁：完全依靠数据库来管理锁的工作。

◆悲观锁：程序员自己管理数据或对象上的锁处理。

MS-SQLSERVER 使用锁在多个同时在数据库内执行修改的用户间实现悲观并发控制

### 三 锁的粒度 ###

锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小

SQL Server支持的锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁

资源 描述

RID 行标识符。用于单独锁定表中的一行。

键 索引中的行锁。用于保护可串行事务中的键范围。

页 8 千字节 (KB) 的数据页或索引页。

扩展盘区 相邻的八个数据页或索引页构成的一组。

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

DB 数据库。

SQL Server 提供以下的锁级别:  
  
DATABASE  -- 无论何时当一个SQL Server 进程正在使用除master以外的数据库时，Lock Manager为该进程授予数据库级的锁。数据库级的锁总是共享锁，用于跟踪何时数据库在使用中，以防其他进程删除该数据库，将数据库置为脱机，或者恢复数据库。注意，由于master和tempdb数据库不能被删除或置为脱机，所以不需要在它们之上加锁。  
FILE \-- 文件级的锁用于锁定数据库文件。  
EXTENT \-- Extent锁用于锁定extents,通常仅在空间分配和重新分配的时候使用。一个extent由8个连续的数据页或索引页组成。Extent锁可以是共享锁也可以是独占锁。  
ALLOCATION\_UNIT -- 使用在数据库分配单元上。  
TABLE -- 这种级别的锁将锁定整个表，包括数据和索引。何时将获得表级锁的例子包括在Serializable隔离级别下从包含大量数据的表中选取所有的行，以及在表上执行不带过滤条件的update或delete。  
Heap or B-Tree (HOBT) \-- 用于堆数据页，或者索引的二叉树结构。  
PAGE -- 使用页级锁，由8KB数据或者索引信息组成的整个页被锁定。当需要读取一页的所有行或者需要执行页级别的维护如页拆分后更新页指针时，将会获取页级锁。  
Row ID (RID) -- 使用RID锁，页内的单一行被锁定。无论何时当提供最大化的资源并发性访问是有效并且可能时，将获得RID锁。  
KEY \-- SQL Server使用两种类型的Key锁。其中一个的使用取决于当前会话的锁隔离级别。对于运行于Read Committed 或者 Repeatable Read 隔离模式下的事务，SQL Server 锁定与被访问的行相关联的的实际索引key。（如果是表的聚集索引，数据行位于索引的叶级。行上在这些你看到的是Key锁而不是行级锁。）若在Serializable隔离模式下，通过锁定一定范围的key值从而不允许新的行插入到该范围内，SQL Server防止了“幻读”。这些锁因而被称作“key-range lock”。  
METADATA \-- 用于锁定系统目录信息（元数据）。  
APPLICATION -- 允许用户定义他们自己的锁，指定资源名称、锁模式、所有者、timeout间隔。

### 四 SQL Server 锁类型(与粒度相对应) ###

1． HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。　　   
2． NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。　　   
3． PAGLOCK：指定添加页锁（否则通常可能添加表锁）。　   
4． READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。   
5． READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作。　　  
6． READUNCOMMITTED：等同于NOLOCK。　　   
7． REPEATABLEREAD：设置事务为可重复读隔离性级别。　   
8． ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。　　   
9． SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。　   
10． TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。　　  
11． TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。　   
12． UPDLOCK ：指定在读表中数据时设置更新 锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。

### 五 锁定时间的长短 ###

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。

用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用 READ COMMITTED 的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下一页上获取锁时才释放锁。如果指定 HOLDLOCK 提示或者将事务隔离级别设置为 REPEATABLE READ 或 SERIALIZABLE，则直到事务结束才释放锁。

根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下一次提取或关闭游标（以先发生者为准）时才释放滚动锁。但是，如果指定 HOLDLOCK，则直到事务结束才释放滚动锁。

用于保护更新的排它锁将直到事务结束才释放。

如果一个连接试图获取一个锁，而该锁与另一个连接所控制的锁冲突，则试图获取锁的连接将一直阻塞到：

将冲突锁释放而且连接获取了所请求的锁。

连接的超时间隔已到期。默认情况下没有超时间隔，但是一些应用程序设置超时间隔以防止无限期等待

### 六 SQL Server 中锁的自定义 ###

◆处理死锁和设置死锁优先级

死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待

可以使用SET DEADLOCK\_PRIORITY控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。

◆2 处理超时和设置锁超时持续时间。

@@LOCK\_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒

SET LOCK\_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK\_TIMEOUT 设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息

示例

下例将锁超时期限设置为 1,800 毫秒。

SET LOCK\_TIMEOUT 1800

◆设置事务隔离级别。

◆对 SELECT、INSERT、UPDATE 和 DELETE 语句使用表级锁定提示。

◆配置索引的锁定粒度

可以使用 sp\_indexoption 系统存储过程来设置用于索引的锁定粒度

### 七 查看锁的信息 ###

1 执行 EXEC SP\_LOCK 报告有关锁的信息

2 查询分析器中按Ctrl+2可以看到锁的信息

### 八 使用注意事项 ###

如何避免死锁，最小化锁竞争

1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务，事务持有锁的时间越短，锁竞争发生的机会就越少；将不是事务所管理的工作单元锁必需的命令移出事务。；

2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；

3 优化程序，检查并避免死锁现象出现；

4 .对所有的脚本和SP都要仔细测试，在正是版本之前。

5 所有的SP都要有错误处理（通过@error）

6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁

7 将组成事务的语句作为一个的单独的批命令处理，以消除 BEGIN TRAN 和 COMMIT  TRAN 语句之间的网络延迟造成的不必要的延迟。

8 考虑完全地使用存储过程编写事务代码。典型地，存储过程比批命令运行更快。

9 在游标中尽可早地Commit更新。因为游标处理比面向集合的处理慢得多，因此导致锁被持有的时间更久。  
  
10 使用每个进程所需的最低级别的锁隔离。比如说，如果脏读是可接受的并且不要求结果必须精确，那么可以考虑使用事务隔离级别0（Read Uncommitted）,仅在绝对必要时才使用Repeatable Read or Serializable隔离级别。  
  
11 在 BEGIN TRAN 和 COMMIT TRAN 语句之间，绝不允许用户交互，因为这样做可能锁被持有无限期的时间。

### 九 几个有关锁的问题 ###

1 如何锁一个表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT \* FROM table ROWLOCK WHERE id = 1

2 锁定数据库的一个表

SELECT \* FROM table WITH (HOLDLOCK)

加锁语句：

sybase:   
update 表 set col1=col1 where 1=0 ;   
MSSQL:   
select col1 from 表 (tablockx) where 1=0 ;   
oracle:   
LOCK TABLE 表 IN EXCLUSIVE MODE ；   
加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁

◆排它锁

新建两个连接,在第一个连接中执行以下语句

**\[sql\]**  [view plain][]  [copy][view plain]

1.  begin tran   
2.  update table1 set A='aa' where B='b2'   
3.  waitfor delay '00:00:30' \--等待30秒   
4.  commit tran   
5.  \--在第二个连接中执行以下语句   
6.  begin tran   
7.  select \* from table1 where B='b2'   
8.  commit tran

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

◆共享锁

在第一个连接中执行以下语句

**\[html\]**  [view plain][]  [copy][view plain]

1.  begin tran   
2.  select \* from table1 holdlock  --holdlock人为加锁   
3.  where B='b2'   
4.  waitfor delay '00:00:30' --等待30秒   
5.  commit tran

◆共享锁

在第一个连接中执行以下语句

**\[sql\]**  [view plain][]  [copy][view plain]

1.  begin tran   
2.  select \* from table1 holdlock \--holdlock人为加锁   
3.  where B='b2'   
4.  waitfor delay '00:00:30' \--等待30秒   
5.  commit tran

在第二个连接中执行以下语句

**\[sql\]**  [view plain][]  [copy][view plain]

1.  begin tran   
2.  select A,C from table1 where B='b2'   
3.  update table1 set A='aa' where B='b2'   
4.  commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行

而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒

◆死锁

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \--在第一个连接中执行以下语句   
2.  begin tran   
3.  update table1  set A='aa' where B='b2'   
4.  waitfor delay '00:00:30'   
5.  update table2  set D='d5'  where E='e1'   
6.  commit tran  
7.     
8.  \--在第二个连接中执行以下语句  
9.    
10. begin tran   
11. update table2  set D='d5'  where E='e1'   
12. waitfor delay '00:00:10'   
13. update table1  set A='aa'  where B='b2'   
14. commit tran

同时执行，系统会检测出死锁，并中止进程

###    十 应用程序锁: ###

应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁

处理应用程序锁的两个过程

sp\_getapplock 锁定应用程序资源

sp\_releaseapplock 为应用程序资源解锁

注意: 锁定数据库的一个表的区别

SELECT \* FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除

SELECT \* FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除

交读事务使用行版本控制。   
使用快照隔离。  
使用绑定连接。

## 二 锁的分析及应用系列       ##

##  ##

### 1 用SqlServer Profile来查看分析锁的信息 ###

这个工具我想大家都明白，它的监视能力真的是无所不能。。。锁的痉挛状态也全在它的掌握之中。

1. 首先我做一个Person表，Name字段设定4000字节，这样一个数据页可以容纳2条数据，如下图：

DROP TABLE dbo.Person
    CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'aaaaa')
    --插入6条，生成3个数据页
    INSERT INTO dbo.Person DEFAULT VALUES
    go 6

2. 下面我们看看数据在数据页的分布情况。

![Center][]

3. 然后我们开启Profile，在“事件选择”的Events中选择”Lock:Acquired“和”Lock:Released“ ，然后运行，如下图：

![Center 1][]

使用测试数据

1. 首先我执行一个简单的 SELECT \* FROM dbo.Person，看看表/数据页/记录的加锁情况。

![Center 2][]

从图中可以看到，select执行的大概步骤如下：

第一步：给表（Object）加上IS（意向共享锁）。

第二步：先给1:78号数据页加IS锁，扫描78号数据页，然后释放IS锁。

第三步：同样的道理扫描之后的数据页。

第四步：最后释放表的IS锁，结束整个锁流程。

看完上面的一系列的Lock:Acquired 和 Lock:Released的话，你有没有发现一个问题，不是说好给记录（RID）加上S锁么？？？这里没加，是因为引擎进入78号数据页的时候，未发现它存在IU锁或者IX锁。。。所以。。。这个属于锁的组合，后续会说。

2. 接下来用UPDATE dbo.Person SET NAME='bbbbb' WHERE ID=3来看看update的整个过程，乍一看，Profile捕获到的记录还是比较多的，下面具体看图：

![Center 3][]

第一步： 给表(Object)加上IX锁，

第二步： 给数据页(1:78)数据页分配IU锁。然后开始逐一扫描78号数据页的RID记录，进入前就Acquired，退出后就Released，当扫描完78号数据页的所有RID后，再释放78                  号数据页的IU锁，进入下一个数据页。。。

第三步： 我们发现ID=3是在89号数据页上，当引擎扫到该RID之后，我们观察到89号的数据页由IU锁变成了IX锁，并且把1:89:0（slot为0的记录）由U锁变成X锁，变成X锁                       后，就排斥了其他所有的锁，这时候就可以进行Update操作了。

第四步： 后面就继续90号数据页，步骤类似，第二步和第三步。

不知道细心的你有没有发现，在Released Object之前我们才释放1:89:0的X锁，然后释放89号数据页的IX锁，这说明什么？？？说明这个Update是贯穿于这个事务的，不像Select操作中，扫完一个数据页就释放一个数据页。

3. 最后再看一个DELETE FROM dbo.Person WHERE ID=3 的操作。

![Center 4][]

大概扫了一下上面的图，或许你感觉和Update操作大差不差，会扫描数据页中的每个记录并加上U锁。当在1:89:0槽位中找到了目标记录后，然后将U锁转化为X锁，具体可以参考Update。

### 2 深入的探讨锁机制 ###

上一篇我只是做了一个堆表让大家初步的认识到锁的痉挛状态，但是在现实世界上并没有这么简单的事情，起码我的表不会没有索引对吧，，，还有就是我的表一定会有很多的连接过来，10:1的读写，很多码农可能都会遇到类似神乎其神的死锁，卡住，读不出来，插不进入等等神仙的事情导致性能低下，这篇我们一起来探讨下。

一： 当select遇到性能低下的update会怎么样？

1. 还是使用原始的person表，插入6条数据，由于是4000字节，所以两条数据就是一个数据页，如下图：

1 DROP TABLE dbo.Person
    2 CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'aaaaa')
    3 --插入6条数据，刚好3个数据页
    4 INSERT INTO dbo.Person DEFAULT VALUES
    5 go 6

2. 为了模拟性能低下的Update操作，我们开个显式事务来更新ID=4的记录，并且用profile看一下，如下图：

1 BEGIN TRAN
    2 UPDATE dbo.Person SET NAME='bbbbb' WHERE id=4

![Center 5][]

3. 然后我们开下另一个会话连接，读取ID=6的记录会是怎样？好奇吗？

1 SELECT * FROM Person WHERE ID=6

![Center 6][]

从上面流程你是否看到，当扫描到89号数据页的slot1槽位的时候卡住了。。。我想你应该知道update正好已经给这条记录加上了X锁。。。如果你够细心，你还会发现，给S锁附加记录的条件是在当引擎发现记录所在的数据页已经附加上了IX锁的情况下，才给该号数据页下的每条记录附加S锁，对吧。。。好了，既然在Profile上面看不到了，我还是有其他办法来判断到底select语句现在处于什么状态。

4. 使用sys.dm\_tran\_locks来看当前各个连接持有锁的状态。

![复制代码][copycode.gif]

1 SELECT  l.request_session_id,
    2         DB_NAME(l.resource_database_id),OBJECT_NAME(p.object_id),
    3         l.resource_description,l.request_type,
    4         l.request_status,request_mode 
    5 FROM sys.dm_tran_locks AS l
    6 LEFT JOIN sys.partitions AS p
    7 ON l.resource_associated_entity_id=p.hobt_id

![复制代码][copycode.gif]

![Center 7][]

仔细观察上图可以看到，当前有51和52号会话，51号在1:89:1槽位上使用了X锁并且没有释放，52号此时也进入了1:89:1中，并且想给该RowID附加S锁，但是你也知道S和X锁是排斥的，所以很无奈的一直保持等待状态。

二：使用索引或许可以帮你逃过一劫

当你看完上面的讲叙，是不是有点害怕？？？要是在生产环境下出现了这种情况，那我们是不是死的很惨？？？那接下来使用索引是不是真的可以帮我们躲过一劫呢？下面跟我一起看一看。

1. 新建索引index

1 -- 在ID列上建一个index
    2 CREATE INDEX idx_person ON dbo.Person(ID)

2. 然后我们看下数据页的分布情况，可以看到下图中78,89,90是表数据页，93号为索引数据页。

1 DBCC TRACEON(2588,3604)
    2 DBCC IND(Ctrip,Person,-1)

![Center 8][]

3. 麻蛋的，继续执行上面的那个慢update

BEGIN TRAN
    UPDATE dbo.Person SET NAME='bbbbb' WHERE id=4

4. 激动人心的时刻来了，由于数据太少，所以我这里强制让引擎执行我创建的索引，看看结果怎样？

![Center 9][]

居然没卡住？？？现在是不是有一股强烈的好奇心来了，狗狗狗。。。马上开启profile，看看到底都发生了什么？

![Center 10][]

仔细看完这个图，是不是觉得很有意思呢？？？具体步骤如下：

第一步：给表（Object）加上IS锁。

第二步：因为要走索引，给93号索引数据页加上IS锁。

第三步：找到93号索引数据页的目标key，给这个key加上S锁，有人可能就会问了。。。这个key不就是6嘛，为什么这个key=(61005a25560e)，你要是太好奇我可以告                   诉你，年轻人说话不要太屌，每行索引记录都有一个散列值，这个值就是根据索引的几个字段散列出来的，好处就是防止你的索引长度过大，导致锁这个记录的                   时候太耗费锁空间了。。。。如果你还是不太相信的话，我用DBCC给你看一看。

![Center 11][]

第四步：根据这个key直接跳到存放记录的90号数据页中，万幸的是update的记录刚好不在90号数据页中。。。。就这样躲过一劫了。。。然后select顺利的读取到了该                  读的记录，最后释放相关的IS锁。

### 3 nolock引发的三级事件的一些思考 ###

曾今有件事情让我记忆犹新，那年刚来携程不久，马上就被安排写一个接口，供企鹅公司调用他们员工的差旅信息，然后我就三下五除二的给写好了，上线之后，大概过了一个月。。。DBA那边报告数据库出现大量锁超时，并且及时根据sql的来源将email发到了我们部门，指出sql读取时间过长，并且缺少nolock，影响了大量机票订单入库，然后我就拿着sql去生产环境跑了下，22s。。。花擦。。。项目上线时间太久，版本已经不存在了，无法回滚。。。原本准备撤下接口。。。看了下撤下接口跟加上nolock时间相差不多，最后决定先加上nolock，发布紧急单。。。然后再优化，DBA那边暂时做手工解锁，发上去后，最后就是损失XXXX订单。。。定级为三级事件。然后就是追责，当然这个责任只能有老大们去承担了，出了这次由我引发的事件，我得思考了，出了事情对我不见得全是坏事，起码这次会让我铭记如心，想想也搓，来携程之前根本就不会关注要不要给select指定nolock，这其中也包括自己没遇到过大数据吧，也包括自己的能力有限，只知道有锁这个玩意，细说的话就啥也不知道了，后来才知道携程有个规则，就是很多业务产线所写的select都必须指定nolock，懂一点的人可能会说nolock可以提升性能，如果你这样说，确实是这样，因为数据库的锁是有96字节开销的，没了锁，也就没有你在profile中看到accquired和released痉挛了，当你看完我的事件之后，你可能会意识到，性能提升不是最关心的，最关心就是不要出现死锁，锁等待。。。好了，言归正传，下面我们看看到底在数据库中可以指定多少个锁？？？

一：到底可以指定多少个锁

这个问题有意思，我们不需要记，只要你装一个SQL Prompt，有了这个神器，你就知道到底有多少个？如下图：

1 DROP TABLE dbo.Person
    2 CREATE TABLE Person(ID INT IDENTITY,NAME CHAR(4000) DEFAULT 'xxxxx')
    3 INSERT INTO dbo.Person DEFAULT VALUES
    4 go 6

![Center 12][]

一眼扫下去，还是蛮多的，不过你要注意了，那些所谓的XXXLock才是我们需要关注的，根据上面的图，我们大概把锁分个类。。。

粒度锁：PAGLOCK, TABLOCK, TABLOCKX, ROWLOCK, NOLOCK

模式锁：HOLDLOCK, UPDLOCK, XLOCK

接下来我从粒度锁说起：

1. NOLOCK

都说nolock是无锁模式的，那到底是怎样的无锁呢？？？到这篇为止，你应该知道，如果不加nolock，我们的表，数据页是附加IS锁的，那接下来我用profile看下两者有什么区别。

![Center 13][]

从上图中，你会看到加上nolock之后，object上面附加了Sch-S锁，这个锁叫做“架构稳定锁”，很简单就是sql编译时附加的一把锁，目的就是防止在编译时，有其他连接修改表结构，而这个锁只与Sch-M锁冲突，与其他锁都兼容，这说明什么？说明其他连接锁住了记录也没关系，我的nolock不跟他们打交道，这样的话，就可能会读到脏数据，不过没关系，携程的很多业务是容许脏数据的，毕竟比锁等待，死锁要强得多，再说nolock读到了其他连接未修改或者未提交的数据，这个概率也比较低，就算遇到了也没关系，一般不会招来客诉的，客人或许再刷下页面，数据或许就正确了，对不对。。。

2.TABLOCK

这个还是比较见名识义的，就是附加在table上的锁，也就是表锁了，很恐怖的。。。下面我举个Update的例子，看看前后对比。

![Center 14][]

![142311395735641.png][]

在上面你有没有看到，X锁已经附加到OBJECT上面去了。。。这样的话，其他连接就动不了这个Object了，只能等待。。。

3.  PAGLOCK

看了名字你应该也知道，就是附加到页面这个级别的锁，我也举一个Update的例子。

1 BEGIN TRAN
    2 UPDATE dbo.Person SET NAME='aaaaa' WHERE ID=6
    3 
    4 BEGIN TRAN
    5 UPDATE dbo.Person WITH(PAGLOCK) SET NAME='bbbbb' WHERE ID=4

![Center 15][]

![Center 16][]

从上面两个图中，你应该可以看到，原来附加到RID上面的U锁，由于PagLock的提升，现在要附加到Page上面了，这个就是所谓的数据页锁。

4.TABLOCKX, ROWLOCK

这两个我就不细说了，TABLOCKX就是直接附加在table上的X锁，你可以通过select看一下。

![Center 17][]

ROWLOCK的话，默认情况下就是ROWLOCK，比如默认的Update，你会发现RID上被附加的U锁，这个就是行锁。

5.UPDLOCK

这个锁还是蛮有意思的，它就是update锁，如果你select下，它会呈现update的锁痉挛效果。

![Center 18][]

6. XLOCK

知道了UPDLOCK锁，我想XLOCK你也应该明白了。。。它就是delete锁，即排他锁，我可以让select带上排他锁。

![Center 19][]

7.HOLDLOCK

最后一个我也没闹明白，据说是让语句在整个事务中持有锁，然后我就用select和update调试一下。

1 SELECT * FROM dbo.Person(HOLDLOCK)
    2 UPDATE dbo.Person WITH(HOLDLOCK) SET NAME='bbbbb' WHERE ID=4

![Center 20][]

## 三 SQL Server 锁机制 悲观锁 乐观锁 实测解析 ##

在使用SQL时，大都会遇到这样的问题，你Update一条记录时，需要通过Select来检索出其值或条件，然后在通过这个值来执行修改操作。  
  
但当以上操作放到多线程中并发处理时会出现问题：某线程select了一条记录但还没来得及update时，另一个线程仍然可能会进来select到同一条记录。  
  
 一般解决办法就是使用锁和事物的联合机制：  
  
  
1. 把select放在事务中, 否则select完成, 锁就释放了  
2. 要阻止另一个select , 则要手工加锁, select 默认是共享锁, select之间的共享锁是不冲突的, 所以, 如果只是共享锁, 即使锁没有释放, 另一个select一样可以下共享锁, 从而select出数据

**\[sql\]**  [view plain][]  [copy][view plain]

1.  BEGIN TRAN  
2.  SELECT \* FROM Table WITH(UPDLOCK)   
3.  \--或者 SELECT \* FROM Table WITH(TABLOCKX, READPAST) 具体情况而定。  
4.  UPDATE ....  
5.  COMMIT TRAN

所有Select加 With (NoLock)解决阻塞死锁,在查询语句中使用 NOLOCK 和 READPAST   
处理一个数据库死锁的异常时候，其中一个建议就是使用 NOLOCK 或者 READPAST 。有关 NOLOCK 和 READPAST的一些技术知识点：   
对于非银行等严格要求事务的行业，搜索记录中出现或者不出现某条记录，都是在可容忍范围内，所以碰到死锁，应该首先考虑，我们业务逻辑是否能容忍出现或者不出现某些记录，而不是寻求对双方都加锁条件下如何解锁的问题。   
NOLOCK 和 READPAST 都是处理查询、插入、删除等操作时候，如何应对锁住的数据记录。但是这时候一定要注意NOLOCK 和 READPAST的局限性，确认你的业务逻辑可以容忍这些记录的出现或者不出现：   
简单来说：  
  
1.NOLOCK 可能把没有提交事务的数据也显示出来  
2.READPAST 会把被锁住的行不显示出来  
  
不使用 NOLOCK 和 READPAST ，在 Select 操作时候则有可能报错误：事务(进程 ID \*\*)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。  
  
SELECT \* FROM Table WITH(NOLOCK)  
SELECT \* FROM Table WITH(READPAST)  
  
实际开始动手用代码说话吧！  
  
SQLServer2012在查询分析器里面开两个连接

#### 插入锁： ####

结论：“表锁”锁定对该表的Select、Update、Delete操作，但不影响对该表的Insert操作也不影响以主键Id为条件的Select，所以Select如果不想等待就要在Select后加With(Nolock)，但这样会产生脏数据就是其他事务已更新但并没有提交的数据，如果该事务进行了RollBack则取出的数据就是错误的，所以好自己权衡利弊，一般情况下90%以上的Select都允许脏读，只有账户金额相关的不允许。

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \------------------A连接 Insert Lock-------------------  
2.  BEGIN TRAN  
3.  INSERT INTO dbo.UserInfo  
4.          ( Name, Age, Mobile, AddTime, Type )  
5.  VALUES  ( 'eee', \-- Name - varchar(50)  
6.            2, \-- Age - int  
7.            '555', \-- Mobile - char(11)  
8.            GETDATE(), \-- AddTime - datetime  
9.            0  \-- Type - int  
10.           )  
11.             
12. SELECT resource\_type, request\_mode,COUNT(\*)  FROM sys.dm\_tran\_locks  
13. WHERE request\_session\_id=@@SPID  
14. GROUP BY resource\_type,request\_mode  
15. \--ROLLBACK TRAN  
16.   
17. \------------------------B连接 Insert Lock------------------------  
18. INSERT INTO dbo.UserInfo  
19.         ( Name, Age, Mobile, AddTime, Type )  
20. VALUES  ( 'fff', \-- Name - varchar(50)  
21.           2, \-- Age - int  
22.           '123', \-- Mobile - char(11)  
23.           GETDATE(), \-- AddTime - datetime  
24.           1  \-- Type - int  
25.           ) \--可以执行插入  
26.             
27. SELECT \* FROM dbo.UserInfo \--需要等待解锁  
28. SELECT \* FROM dbo.UserInfo WHERE Age=1 \--需要等待解锁  
29. SELECT \* FROM dbo.UserInfo WHERE Id=3 \--可以执行查询（根据主键可以）  
30. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) \--可以执行查询(在一个事务中，有更新字段但还没有提交，此时就会查处脏数据)  
31. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) WHERE Age=1 \--可以执行查询  
32. UPDATE dbo.UserInfo SET Type=5 WHERE Name='fff'  \--需要等待解锁  
33. DELETE FROM dbo.UserInfo WHERE Name='fff' \--需要等待解锁

#### 更新锁： ####

结论：“表锁”锁定对该表的Select、Update、Delete操作，但不影响对该表的Insert操作也不影响以主键Id为条件的Select

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \-----------------------A连接 Update Lock-----------------------  
2.  BEGIN TRAN  
3.  UPDATE dbo.UserInfo SET Name = 'eee' WHERE Age = 2  
4.    
5.  SELECT resource\_type, request\_mode,COUNT(\*)  FROM sys.dm\_tran\_locks  
6.  WHERE request\_session\_id=@@SPID  
7.  GROUP BY resource\_type,request\_mode  
8.    
9.  \--ROLLBACK TRAN  
10.   
11. \------------------------B连接 Update Lock------------------------  
12. INSERT INTO dbo.UserInfo  
13.         ( Name, Age, Mobile, AddTime, Type )  
14. VALUES  ( 'ppp', \-- Name - varchar(50)  
15.           15, \-- Age - int  
16.           '666', \-- Mobile - char(11)  
17.           GETDATE(), \-- AddTime - datetime  
18.           9  \-- Type - int  
19.           ) \--可以执行插入  
20. SELECT \* FROM dbo.UserInfo \--需要等待解锁  
21. SELECT \* FROM dbo.UserInfo WHERE Name='ppp' \--需要等待解锁  
22. SELECT \* FROM dbo.UserInfo WHERE Id=3 \--可以执行查询（根据主键可以）  
23. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) \--可以执行查询(在一个事务中，有更新字段但还没有提交，此时就会查处脏数据)  
24. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'ppp' \--可以执行查询  
25. UPDATE dbo.UserInfo SET Age=8 WHERE Name='ccc' \--需要等待解锁  
26. DELETE dbo.UserInfo WHERE Age = 5 \--需要等待解锁

#### 主键锁： ####

结论：“行锁+表锁” 锁定对该表的Select、Update、Delete操作，但不影响对该表的Insert操作也不影响以主键Id为条件的Select、Update、Delete

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \------------------------A连接 Key Lock--------------------  
2.  BEGIN TRAN  
3.  UPDATE dbo.UserInfo SET Name='hhh' WHERE Id=3 \--以主键为条件  
4.    
5.  SELECT resource\_type, request\_mode,COUNT(\*)  FROM sys.dm\_tran\_locks  
6.  WHERE request\_session\_id=@@SPID  
7.  GROUP BY resource\_type,request\_mode  
8.    
9.  \--ROLLBACK TRAN  
10.   
11. \------------------------B连接 Key Lock----------------------  
12. INSERT INTO dbo.UserInfo  
13.         ( Name, Age, Mobile, AddTime, Type )  
14. VALUES  ( 'kkk', \-- Name - varchar(50)  
15.           18, \-- Age - int  
16.           '234', \-- Mobile - char(11)  
17.           GETDATE(), \-- AddTime - datetime  
18.           7  \-- Type - int  
19.           ) \--可以执行插入  
20. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) \--可以执行查询(在一个事务中，有更新字段但还没有提交，此时就会查处脏数据)  
21. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'kkk' \--可以执行查询  
22.   
23. \-----//全表查询及操作正在处理的行  
24. SELECT \* FROM dbo.UserInfo \--需要等待解锁  
25. SELECT \* FROM dbo.UserInfo WHERE Id=3 \--需要等待解锁（根据主键,但与A连接操作相同行不可）  
26. UPDATE dbo.UserInfo SET Name='mmm' WHERE Id=3 \--需要等待解锁（根据主键,但与A连接操作相同行不可）  
27. DELETE dbo.UserInfo WHERE Id=3 \--需要等待解锁（根据主键,但与A连接操作相同行不可）  
28. \-----//使用非主键为条件的操作  
29. SELECT \* FROM dbo.UserInfo WHERE Name='aaa' \--需要等待解锁（非主键不可）  
30. UPDATE dbo.UserInfo SET Name='ooo' WHERE Name='aaa' \--需要等待解锁（非主键不可）  
31. DELETE dbo.UserInfo WHERE Name='aaa' \--需要等待解锁（非主键不可）  
32. \-----//使用主键为条件的操作  
33. SELECT \* FROM dbo.UserInfo WHERE id=1 \--可以执行查询（根据主键可以）  
34. UPDATE dbo.UserInfo SET Name='yyy' WHERE Id=1 \--可以执行更新（根据主键可以）  
35. DELETE dbo.UserInfo WHERE Id=1 \--可以执行删除（根据主键可以）

#### 索引锁： ####

结论：“行锁+表锁” 锁定对该表的Select、Update、Delete操作，但不影响对该表的Insert操作也不影响以主键Id为条件的Select、Update、Delete，也不影响以索引列Name为条件的Update、Delete但不可以Select

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \------------------------A连接 Index Lock--------------------  
2.  DROP INDEX dbo.UserInfo.Index\_UserInfo\_Name  
3.  CREATE INDEX Index\_UserInfo\_Name ON dbo.UserInfo(Name)  
4.    
5.  BEGIN TRAN  
6.  UPDATE dbo.UserInfo SET age=66 WHERE Name='ddd' \--使用name索引列为条件  
7.    
8.  SELECT resource\_type, request\_mode,COUNT(\*)  FROM sys.dm\_tran\_locks  
9.  WHERE request\_session\_id=@@SPID  
10. GROUP BY resource\_type,request\_mode  
11.   
12. \--ROLLBACK TRAN  
13.   
14. \----------------------B连接 Index Lock-------------------  
15. INSERT INTO dbo.UserInfo  
16.         ( Name, Age, Mobile, AddTime, Type )  
17. VALUES  ( 'iii', \-- Name - varchar(50)  
18.           20, \-- Age - int  
19.           '235235235', \-- Mobile - char(11)  
20.           GETDATE(), \-- AddTime - datetime  
21.           12  \-- Type - int  
22.           ) \--可以执行插入  
23. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) \--可以执行查询(在一个事物中，有更新字段但还没有提交，此时就会查处脏数据)  
24. SELECT \* FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'kkk' \--可以执行查询  
25.   
26. \-----//全表查询及操作正在处理的行  
27. SELECT \* FROM dbo.UserInfo \--需要等待解锁  
28. SELECT \* FROM dbo.UserInfo WHERE Id=4 \--需要等待解锁（根据主键,但与A连接操作相同行不可）  
29. UPDATE dbo.UserInfo SET Name='mmm' WHERE Id=4 \--需要等待解锁（根据主键,但与A连接操作相同行不可）  
30. DELETE dbo.UserInfo WHERE Id=4 \--需要等待解锁（根据主键,但与A连接操作相同行不可）  
31. \-----//使用非主键非索引为条件的操作  
32. SELECT \* FROM dbo.UserInfo WHERE Age=5 \--需要等待解锁（非主键不可）  
33. UPDATE dbo.UserInfo SET Name='ooo' WHERE Age=5 \--需要等待解锁（非主键不可）  
34. DELETE dbo.UserInfo WHERE Age=5 \--需要等待解锁（非主键不可）  
35. \-----//使用主键为条件的操作  
36. SELECT \* FROM dbo.UserInfo WHERE Id=1 \--可以执行更新（根据主键可以）  
37. UPDATE dbo.UserInfo SET Name='yyy' WHERE Id=1 \--可以执行更新（根据主键可以）  
38. DELETE dbo.UserInfo WHERE Id=1 \--可以执行删除（根据主键可以）  
39. \-----//使用索引为条件的操作  
40. SELECT \* FROM dbo.UserInfo WHERE Name='aaa' \--需要等待解锁（非主键不可）  
41. UPDATE dbo.UserInfo SET Name='ooo' WHERE Name='aaa' \--可以执行更新（根据索引可以）  
42. DELETE dbo.UserInfo WHERE Name='aaa' \--可以执行删除（根据索引可以）

#### 悲观锁（更新锁-人工手动设置上锁）： ####

结论：可以理解为在使用版本控制软件的时候A迁出了一个文件，并且8i将这个87文件锁定，B就无法再迁出该文件了，直到A迁入解锁后才能被其他人迁出。

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \------------------------A连接 Update Lock(悲观锁)---------------------  
2.  BEGIN TRAN  
3.  SELECT \* FROM dbo.UserInfo WITH(UPDLOCK) WHERE Id=2  
4.    
5.  SELECT resource\_type, request\_mode,COUNT(\*)  FROM sys.dm\_tran\_locks  
6.  WHERE request\_session\_id=@@SPID  
7.  GROUP BY resource\_type,request\_mode  
8.    
9.  \--COMMIT TRAN  
10. \--ROLLBACK TRAN  
11.   
12. \---------------------------B连接 Update Lock(悲观锁)-------------------------  
13. SELECT \* FROM dbo.UserInfo \--可以执行查询  
14. SELECT \* FROM dbo.UserInfo WHERE id=2 \--可以执行查询  
15. SELECT \* FROM dbo.UserInfo WHERE Name='ooo' \--可以执行查询  
16.   
17. UPDATE dbo.UserInfo SET Age=3 WHERE id=1 \--可以执行更新（根据主键可以）  
18. UPDATE dbo.UserInfo SET Age=3 WHERE Name='ccc' \--需要等待解锁（非主键不可）  
19.   
20. DELETE dbo.UserInfo WHERE id=1 \--可以执行更新（根据主键可以）  
21. DELETE dbo.UserInfo WHERE name='ccc' \--需要等待解锁（非主键不可）

#### 乐观锁（人工通过逻辑在数据库中模拟锁） ####

结论：可以理解为同样在使用版本控制软件的时候A迁出了一个文件，B也可以迁出该文件，两个人都可以对此文件进行修改，其中一个人先进行提交的时候，版本并没有变化所以可以正常提交，另一个后提交的时候，发现版本增加不对称了，就提示冲突由用户来选择如何进行合并再重新进行提交。

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \--------------------------A客户端连接 Lock(乐观锁)------------------------  
2.  \--DROP TABLE Coupon  
3.  \-----------------创建优惠券表-----------------  
4.  CREATE TABLE Coupon  
5.  (  
6.      Id INT PRIMARY KEY IDENTITY(1,1),  
7.      Number VARCHAR(50) NOT NULL,  
8.      \[User\] VARCHAR(50),  
9.      UseTime DATETIME,  
10.     IsFlag BIT DEFAULT(0) NOT NULL,  
11.     CreateTime DATETIME DEFAULT(GETDATE()) NOT NULL  
12. )  
13. INSERT INTO dbo.Coupon(Number) VALUES ( '10000001')  
14. INSERT INTO dbo.Coupon(Number) VALUES ( '10000002')  
15. INSERT INTO dbo.Coupon(Number) VALUES ( '10000003')  
16. INSERT INTO dbo.Coupon(Number) VALUES ( '10000004')  
17. INSERT INTO dbo.Coupon(Number) VALUES ( '10000005')  
18. INSERT INTO dbo.Coupon(Number) VALUES ( '10000006')  
19.   
20. \--SELECT \* FROM dbo.Coupon WITH(NOLOCK) --查询数据  
21. \--UPDATE Coupon SET \[User\]=NULL, UseTime=NULL, IsFlag=0 --还原数据  
22.   
23. \-----------------1、模拟高并发普通更新-----------------  
24. DECLARE @User VARCHAR(50)    \--模拟要使用优惠券的用户  
25. DECLARE @TempId INT            \--模拟抽选出来的要使用的优惠券  
26. SET @User='a'  
27. BEGIN TRAN  
28. SELECT @TempId=Id FROM dbo.Coupon WHERE IsFlag=0    \--高并发时此语句有可能另外一个该事务已取出的Id  
29. \--WAITFOR DELAY '00:00:05'    --改用此方式要开两个SQL Management客户端  
30. UPDATE dbo.Coupon SET IsFlag=1, \[User\]=@User, UseTime=GETDATE() WHERE Id=@TempId  
31. COMMIT TRAN  
32. \--ROLLBACK TRAN  
33.   
34. \-----------------2、悲观锁解决方案-----------------  
35. DECLARE @User VARCHAR(50)    \--模拟要使用优惠券的用户  
36. DECLARE @TempId INT            \--模拟抽选出来的要使用的优惠券  
37. SET @User='a'  
38. BEGIN TRAN  
39. SELECT @TempId=Id FROM dbo.Coupon WITH(UPDLOCK) WHERE IsFlag=0    \--高并发时此语句会锁定取出的Id数据行  
40. \--WAITFOR DELAY '00:00:05'    --改用此方式要开两个SQL Management客户端  
41. UPDATE dbo.Coupon SET IsFlag=1, \[User\]=@User, UseTime=GETDATE() WHERE Id=@TempId  
42. COMMIT TRAN  
43. \--ROLLBACK TRAN  
44.   
45. \-----------------3、乐观锁解决方案-----------------  
46. ALTER TABLE dbo.Coupon ADD RowVer ROWVERSION NOT NULL \--增加数据行版本戳类型字段（微软新推荐数据字段，该字段每张表只能有一个，会在创建行或更新行时自动进行修改无需人为干涉，该字段不能建立索引及主键因为会频繁修改）  
47.   
48.   
49. DECLARE @User VARCHAR(50)    \--模拟要使用优惠券的用户  
50. DECLARE @TempId INT            \--模拟抽选出来的要使用的优惠券  
51. DECLARE @RowVer BINARY(8)    \--抽选出来的优惠券的版本（ROWVERSION数据类型存储大小为8字节）  
52. SET @User='a'  
53.   
54. BEGIN TRY  
55.     BEGIN TRAN  
56.     SELECT @TempId=Id, @RowVer=RowVer FROM dbo.Coupon WHERE IsFlag=0    \--取出可用的Id及对应的版本戳  
57.     \--WAITFOR DELAY '00:00:05'    --改用此方式要开两个SQL Management客户端  
58.     UPDATE dbo.Coupon SET IsFlag=1, \[User\]=@User, UseTime=GETDATE() WHERE Id=@TempId AND RowVer=@RowVer  
59.     IF(@@ROWCOUNT > 0)  
60.         BEGIN  
61.             PRINT('修改成功')  
62.             COMMIT TRAN  
63.         END  
64.     ELSE  
65.         BEGIN  
66.             PRINT('该数据已被其他用户修改')  
67.             ROLLBACK TRAN  
68.         END  
69. END TRY  
70. BEGIN CATCH  
71.     ROLLBACK TRAN  
72. END CATCH  
73.   
74. \--------------------------B客户端连接 Lock(乐观锁)------------------------  
75. \--此测试需要开两个SQL Management Studio客户端，在A客户端使用WAITFOR DELAY来模拟并发占用，在B客户端执行与A客户端相同的SQL脚本即可（注释掉WAITFOR），所以在此不放相同代码了。

在乐观锁和悲观锁之间进行选择的标准是：冲突的频率与严重性。如果冲突很少，或者冲突的后果不会很严重，那么通常情况下应该选择乐观锁，因为它能得到更好的并发性，而且更容易实现。但是，如果冲突的结果对于用户来说痛苦的，那么就需要使用悲观策略。  
  
我认为如果同一张表的并发很高，但并发处理同一条数据的冲突几率很低，那就应该使用乐观锁，反之，如果同一张表的并发不高，但同时处理同一条数据的几率很高，就应该使用悲观锁。

## 四 SQL Server 中WITH (NOLOCK)浅析 ##

### 概念介绍 ###

开发人员喜欢在SQL脚本中使用WITH(NOLOCK), WITH(NOLOCK)其实是表提示（table\_hint）中的一种。它等同于 READUNCOMMITTED 。 具体的功能作用如下所示（摘自MSDN）：

1： 指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）。有关脏读、不可重复读和幻读的详细信息，请参阅[并发影响][Link 1]。

2： READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。所有查询（包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询）都会在编译和执行过程中获取 Sch-S（架构稳定性）锁。因此，当并发事务持有表的 Sch-M（架构修改）锁时，将阻塞查询。例如，数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。所有并发查询（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询）都会在尝试获取 Sch-S 锁时被阻塞。相反，持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。有关锁行为的详细信息，请参阅[锁兼容性（数据库引擎）][Link 2]。

3:  不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。

功能与缺陷

使用WIHT(NOLOCK)有利也有弊，所以在决定使用之前，你一定需要了解清楚WITH(NOLOCK)的功能和缺陷，看其是否适合你的业务需求,不要觉得它能提升性能，稀里糊涂的就使用它。

1：使用WITH(NOLOCK)时查询不受其它排他锁阻塞

打开会话窗口1，执行下面脚本，不提交也不回滚事务，模拟事务真在执行过程当中

BEGIN TRAN

UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;

--ROLLBACK

打开会话窗口2，执行下面脚本，你会发现执行结果一直查询不出来（其实才两条记录）。当前会话被阻塞了

SELECT * FROM TEST;

打开会话窗口3，执行下面脚本，查看阻塞情况，你会发现在会话2被会话1给阻塞了，会话2的等待类型为LCK\_M\_S：“当某任务正在等待获取共享锁时出现”

SELECT wt.blocking_session_id                    AS BlockingSessesionId

,sp.program_name                           AS ProgramName

,COALESCE(sp.LOGINAME, sp.nt_username)     AS HostName

,ec1.client_net_address                    AS ClientIpAddress

,db.name                                   AS DatabaseName

,wt.wait_type                              AS WaitType

,ec1.connect_time                          AS BlockingStartTime

,wt.WAIT_DURATION_MS/1000                  AS WaitDuration

,ec1.session_id                            AS BlockedSessionId

,h1.TEXT                                   AS BlockedSQLText

,h2.TEXT                                   AS BlockingSQLText

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.databases db

ON db.database_id = tl.resource_database_id

INNER JOIN sys.dm_os_waiting_tasks AS wt

ON tl.lock_owner_address = wt.resource_address

INNER JOIN sys.dm_exec_connections ec1

ON ec1.session_id = tl.request_session_id

INNER JOIN sys.dm_exec_connections ec2

ON ec2.session_id = wt.blocking_session_id

LEFT OUTER JOIN master.dbo.sysprocesses sp

ON SP.spid = wt.blocking_session_id

CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1

CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

![Center 21][]

此时查看会话1（会话1的会话ID为53，执行脚本1前，可以用SELECT  @@spid查看会话ID）的锁信息情况，你会发现表TEST(ObjId=1893581784)持有的锁信息如下所示

![Center 22][]

打开会话窗口4，执行下面脚本.你会发现查询结果很快就出来，会话4并不会被会话1阻塞。

SELECT \* FROM TEST WITH(NOLOCK)

从上面模拟的这个小例子可以看出，正是由于加上WITH(NOLOCK)提示后，会话1中事务设置的排他锁不会阻碍当前事务读取锁定数据，所以会话4不会被阻塞，从而提升并发时查询性能。

2：WITH(NOLOCK) 不发布共享锁来阻止其他事务修改当前事务读取的数据，这个就不举例子了。

本质上WITH(NOLOCK)是通过减少锁和不受排它锁影响来减少阻塞，从而提高并发时的性能。所谓凡事有利也有弊，WITH(NOLOCK)在提升性能的同时，也会产生脏读现象。

如下所示，表TEST有两条记录，我准备更新OBJECT\_ID=1的记录，此时事务既没有提交也没有回滚

![Center 23][]

BEGIN TRAN

UPDATE TEST SET NAME='Timmy' WHERE OBJECT_ID =1;

--ROLLBACK

此时另外一个会话使用WITH(NOLOCK)查到的记录为未提交的记录值

![Center 24][]

假如由于某种原因，该事务回滚了，那么我们读取到的OBJECT\_ID=1的记录就是一条脏数据。

脏读又称无效数据的读出，是指在数据库访问中，[事务][Link 3]T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。

### WITH(NOLOCK)使用场景 ###

什么时候可以使用WITH(NOLOCK)? 什么时候不能使用WITH(NOLOCK),这个要视你系统业务情况，综合考虑性能情况与业务要求来决定是否使用WITH(NOLOCK), 例如涉及到金融或会计成本之类的系统，出现脏读那是要产生严重问题的。关键业务系统也要慎重考虑。大体来说一般有下面一些场景可以使用WITH(NOLOCK)

1: 基础数据表，这些表的数据很少变更。

2：历史数据表，这些表的数据很少变更。

3：业务允许脏读情况出现涉及的表。

4：数据量超大的表，出于性能考虑，而允许脏读。

另外一点就是不要滥用WITH(NOLOCK),我发现有个奇怪现象，很多开发知道WITH(NOLOCK),但是有不了解脏读，习惯性的使用WITH(NOLOCK)。

### WITH(NOLOCK)与 NOLOCK区别 ###

为了搞清楚WITH(NOLOCK)与NOLOCK的区别，我查了大量的资料，我们先看看下面三个SQL语句有啥区别

SELECT \* FROM TEST NOLOCK

SELECT \* FROM TEST (NOLOCK);

SELECT \* FROM TEST WITH(NOLOCK);

上面的问题概括起来也就是说NOLOCK、(NOLOCK)、 WITH(NOLOCK)的区别：

1： NOLOCK这样的写法，其实NOLOCK其实只是别名的作用，而没有任何实质作用。所以不要粗心将(NOLOCK)写成NOLOCK

2：（NOLOCK）与WITH(NOLOCK)其实功能上是一样的。(NOLOCK)只是WITH(NOLOCK)的别名,但是在SQL Server 2008及以后版本中，(NOLOCK)不推荐使用了，"不借助 WITH 关键字指定表提示”的写法已经过时了。 具体参见MSDN [http://msdn.microsoft.com/zh-cn/library/ms143729][http_msdn.microsoft.com_zh-cn_library_ms143729]%28SQL.100%29.aspx

2.1  至于网上说WITH(NOLOCK)在SQL SERVER 2000不生效，我验证后发现完全是个谬论。

2.2  在使用链接服务器的SQL当中，(NOLOCK)不会生效，WITH(NOLOCK)才会生效。如下所示

![Center 25][]

消息 4122，级别 16，状态 1，第 1 行

Remote table-valued function calls are not allowed.

3.语法上有些许出入，如下所示

这种语法会报错

SELECT  * FROM   sys.indexes  WITH(NOLOCK) AS i

-Msg 156, Level 15, State 1, Line 1

-Incorrect syntax near the keyword 'AS'.

这种语法正常

SELECT  * FROM   sys.indexes  (NOLOCK) AS i

可以全部改写为下面语法

SELECT  * FROM   sys.indexes   i WITH(NOLOCK)

SELECT  * FROM   sys.indexes   i (NOLOCK)

### WITH(NOLOCK)会不会产生锁 ###

很多人误以为使用了WITH(NOLOCK)后，数据库库不会产生任何锁。实质上，使用了WITH(NOLOCK)后，数据库依然对该表对象生成Sch-S（架构稳定性）锁以及DB类型的共享锁， 如下所示，可以在一个会话中查询一个大表，然后在另外一个会话中查看锁信息(也可以使用SQL Profile查看会话锁信息)

不使用WTIH(NOLOCK)

![Center 26][]

使用WITH(NOLOCK)

![Center 27][]

从上可以看出使用WITH(NOLOCK)后，数据库并不是不生成相关锁。  对比可以发现使用WITH(NOLOCK)后，数据库只会生成DB类型的共享锁、以及TAB类型的架构稳定性锁.

另外，使用WITH(NOLOCK)并不是说就不会被其它会话阻塞，依然可能会产生Schema Change Blocking

会话1：执行下面SQL语句，暂时不提交，模拟事务正在执行

BEGIN TRAN

ALTER TABLE TEST ADD Grade VARCHAR(10) ;

会话2：执行下面语句，你会发现会话被阻塞，截图如下所示。

SELECT * FROM TEST WITH(NOLOCK)

![Center 28][]

\-----

本文引用了以下网址内容：

http://www.cnblogs.com/huangxincheng/p/4292320.html

http://blog.itpub.net/13651903/viewspace-1091664/

http://www.cnblogs.com/kerrycode/p/3946268.html

http://www.cnblogs.com/taiyonghai/p/5674462.html

\--- end ---

[view plain]: https://blog.csdn.net/huwei2003/article/details/4047191#
[Center]: /images/20220528/915cc68f8d384a5c9103eeb08895f865.png
[Center 1]: /images/20220528/3259a0a56ea84ba3a4b42ab786a716f1.png
[Center 2]: /images/20220528/41aaff10c22e4f82999046c858167f74.png
[Center 3]: /images/20220528/6a363261bb984d329129b53f13cebbb6.png
[Center 4]: /images/20220528/a775b1c0d14e4092879000df21af38ca.png
[Center 5]: /images/20220528/7094ba1ac4a64d50b2a1b22e93821260.png
[Center 6]: /images/20220528/643fb4548a8944d0bdfdce485eb11cf8.png
[copycode.gif]: /images/20220528/db121665280441f78e61e8632ac0a437.png
[Center 7]: /images/20220528/548629803b9c49a7bfa64c176d6494c6.png
[Center 8]: /images/20220528/36a557cf05404ae8849fddddd4095f5d.png
[Center 9]: /images/20220528/39d283b6f84c42af8cddf7b25e152140.png
[Center 10]: /images/20220528/cb56b6e138dd470b9389e68ffdce3997.png
[Center 11]: /images/20220528/33e51a8796084e86b43354f510b4079a.png
[Center 12]: /images/20220528/2d37caafb5f24219841d6db6dd12a76b.png
[Center 13]: /images/20220528/a9b72c3a8d804ccfb102d0e27c6ffb5a.png
[Center 14]: /images/20220528/bad27af7f48340de879b6fb2bb56895e.png
[142311395735641.png]: /images/20220528/584f66a44475434fbb8d7de5d397f79b.png
[Center 15]: /images/20220528/52b6918e040542108f4f76dc5e73704a.png
[Center 16]: /images/20220528/7a3d496140b84e0383ecffa7c42a1818.png
[Center 17]: /images/20220528/95ca5f2b42dd4adaa9fb4fc7180f0dea.png
[Center 18]: /images/20220528/62dd78ece1b04b5d8a28f1449ba584f3.png
[Center 19]: /images/20220528/30cedfdf09964a2c95111b6f6ce02a3b.png
[Center 20]: /images/20220528/d20fc33d03054b35aef745f907118f11.png
[Link 1]: http://msdn.microsoft.com/zh-cn/library/ms190805%28v=sql.105%29.aspx
[Link 2]: http://msdn.microsoft.com/zh-cn/library/ms186396%28v=sql.105%29.aspx
[Center 21]: /images/20220528/a022a464c84049889c1d9f02898d97f4.png
[Center 22]: /images/20220528/bda32a0d5f4741338ee5ee6b25393137.png
[Center 23]: /images/20220528/7e546e40245f44d2b282073ed9f7cebd.png
[Center 24]: /images/20220528/4228fa1304764bc99b5ac4d6c64c7d24.png
[Link 3]: http://baike.baidu.com/view/121511.htm
[http_msdn.microsoft.com_zh-cn_library_ms143729]: http://msdn.microsoft.com/zh-cn/library/ms143729
[Center 25]: /images/20220528/617b553fbbc14db1a14cca7adc632494.png
[Center 26]: /images/20220528/a13dee48d61f4dc49d662e51853852c7.png
[Center 27]: /images/20220528/e22161d43af743829884f22a8255d33f.png
[Center 28]: /images/20220528/a0eb6ed18d0144ccac77c31688cd56ac.png