• 阶段1：事务A请求资源S1，事务不对资源S1进行操作

• 阶段2：事务A用锁A锁定资源S1，事务B请求对资源S1进行不兼容的锁定（锁B）,锁B的请求被阻塞，事务B将进入等待状态

• 阶段3：事务A正在释放锁A，事务B等待锁A释放，

• 阶段4：事务A的锁A已释放，事务B用锁B锁定资源S1

3.2 排除阻塞问题

例子：

3.2.1 准备工作：

• 1.准备测试数据

--先创建一张表Product作为测试。id为表的主键，price为product的价格
CREATE TABLE [dbo].[myProduct](
    [id] [int] NOT NULL,
    [price] [money] NOT NULL
) ON [PRIMARY]
GO
--插入一条数据，id=1,price=10
INSERT INTO [TSQLFundamentals2008].[dbo].[myProduct]([id],[price])VALUES(1,10)

• 2.模拟阻塞发生的情况

在SQL Server中打开三个查询窗口Connection1、Connection2、Connection3，分别按顺序执行表格中的执行语句。

--Connection1 BEGIN TRAN UPDATE dbo.myProduct SET price = price + 1 WHERE id=1 

「结论1：」

为了更新id=1这一行数据，会话必须先获得一个排他锁。事务处于一直打开状态，没有提交，所以事务一直持有排他锁，直到事务提交并完成。

--Connection2 SELECT * FROM dbo.myProduct WHERE id=1 

「结论2：」

事务为了读取数据，需要请求一个共享锁，但是这一行已经被其他会话持有的排他锁锁定，而且共享锁和排他锁不是兼容的，所以会话被阻塞，进入等待状态

--Connection3 SELECT request_session_id AS 会话id ,
        resource_type AS 请求锁定的资源类型 ,
        resource_description AS 描述 ,
        request_mode AS 模式 ,
        request_status AS 状态 FROM sys.dm_tran_locks

「结论3：」

「会话56：」（1）状态WAIT-等待锁

（2）正在等待第1个数据文件上的第109页上的第0行资源的共享锁

（3）持有第1个数据文件上的第109页资源的意向共享锁

（3）持有OBJECT资源，意向共享锁

（4）持有DATABASE资源，意向共享锁

「会话52：」

（1）状态WAIT-授予锁

（2）正在等待第1个数据文件上的第109页上的第0行资源的排他锁（3）持有第1个数据文件上的第109页资源的排他锁

（3）持有OBJECT资源，排他锁

（4）持有DATABASE资源，排他锁

演示与总结如下所示：

3.2.2 分析阻塞原因

3.2.2.1 sys.dm_tran_locks 视图

（1）该动态视图可以查询出哪些资源被哪个进程ID锁了

（2）查询出对资源授予或正在等待的锁模式

（3）查询出被锁定资源的类型

上面的查询语句3已经用到了这个视图，可以参考上图中的分析说明。

3.2.2.2 sys.dm_exec_connections 视图

（1）查询出该动态视图可以查询出进程相关的信息

（2）查询出最后一次发生读操作和写操作的时间last_read,last_write

（3）查询出进程执行的最后一个SQL批处理的二进制标记most_recent_sql_handle

SELECT session_id , connect_time ,
        last_read ,
        last_write ,
        most_recent_sql_handle FROM sys.dm_exec_connections WHERE session_id IN ( 52, 56 )

「结论：」

「会话52：」

（1）connect_time连接时间：2016-06-07 07:09:41.103

（2）last_read最后一次读操作时间：2016-06-07 07:10:56.233

（3）last_write最后一次写操作时间：2016-06-07 07:10:57.873

（4）most_recent_sql_handle这是一个二进制标记，最后一个SQL批处理

「会话56：」

（1）状态WAIT-授予锁

（2）正在等待第1个数据文件上的第109页上的第0行资源的排他锁（3）持有第1个数据文件上的第109页资源的排他锁

（3）持有OBJECT资源，排他锁

（4）持有DATABASE资源，排他锁

演示与总结如下所示：

3.2.2.3 sys.dm_exec_sql_text 表函数

示例：

会话52：
执行的SQL语句：
BEGIN TRAN
UPDATE dbo.myProduct
SET price = price + 1
WHERE id = 1

会话56：
执行的SQL语句：
(@1 tinyint)
SELECT * FROM [dbo].[myProduct]
WHERE [id]=@1

（1）该函数可以将二进制标记most_recent_sql_handle作为参数，然后返回SQL代码。

（2）阻塞进程在不断地运行，所以在代码中看到的最后一个操作不一定是导致问题的语句。在本例中最后一条执行语句是导致阻塞的语句。

SELECT session_id , text FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text
        (most_recent_sql_handle) AS ST WHERE session_id IN ( 52, 56 )

演示与总结如下所示：

3.2.2.4 sys.dm_exec_sessions 视图

（1）会话建立的时间login_time

（2）特定于会话的客户端工作站名称host_name

（3）初始化会话的客户端程序的名称program_name

（4）会话所使用的SQL Server登录名login_name

（5）最近一次会话请求的开始时间last_request_start_time

（6）最近一次会话请求的完成时间last_request_end_time

SELECT * FROM sys.dm_exec_sessions

演示与总结如下所示：

3.2.2.5 sys.dm_exec_requests 视图

（1）识别出阻塞链涉及到的会话、争用的资源、被阻塞会话等待了多长时间

SELECT * FROM sys.dm_exec_sessions

「结论：」

「会话56：」

（1）被会话52阻塞，blocking_session_id = 52

（2）会话52的开始时间start_time

（3）状态挂起status = suspended

（4）挂起的命令command

演示与总结如下所示：

3.2.3 解决阻塞问题

3.2.3.1 Lock_TIMEOUT 选项

（1）设置会话等待锁释放的超时期限

（2）默认情况下会话不会设置等待锁释放的超时期限

（3）设置会话超时期限为5秒， SET Lock_TIMEOUT 5000

（4）锁定如果超时，不会引发事务回滚

（5）取消会话超时锁定的设置，SET LOCK_TIMEOUT -1

如果超时，将显示以下错误：

3.2.3.1 KILL命令

（1）杀掉会话52，KILL 52

（2）杀掉会话，会引起事务回滚，同时释放排他锁

四、隔离级别

4.1 基本概念

「（1）隔离级别用来做什么」

• a.隔离级别用于决定如何控制并发用户读写数据的操作

「（2）写操作」

• a.任何对表做出修改的语句

• b.使用排他锁

• c.不能修改读操作获得的锁和锁的持续时间

「（3）读操作：」

• a.任何检索数据的语句

• b.默认使用共享锁

• c.使用隔离级别来控制读操作的处理方式

4.2 隔离级别的分类

（1）未提交读 （READ UNCOMMITTED）

（2）已提交读（READ COMMITTED）（默认值）

（3）可重复读（REPEATABLE READ）

（4）可序列化（SERIALIZABLE）

（5）快照（SNAPSHOT）

（6）已经提交读快照（READ_COMMITTED_SNAPSHOT）

4.3 隔离级别的设置

「（1）设置整个会话的隔离级别」

SET TRANSACTION ISOLATION LEVEL <isolation name>; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

「（2）用表提示设置查询的隔离级别」

SELECT ... FROM <table> WITH (<isolation name>); SELECT * FROM dbo.myProduct WITH (READCOMMITTED);

注意：

• 1.设置会话选项的隔离级别时，隔离级别中的每个单词之间需要用空格分隔

• 2.用表提示的隔离级别时，隔离级别中的每个单词之间不需要用空格分隔

• 3.表提示的隔离级别有同义词，如：NOLOCK->READUNCOMMITTED,HOLDLOCK->REPEATABLEREAD

• 4.隔离级别的严格性：1.未提交读<2.已提交读<3.可重复读<4.可序列化

• 5.隔离级别越高，一致性越高，并发性越低

• 6.基于快照的隔离级别，SQL Server将提交过的行保存到tempdb数据库中，当读操作发现行的当前版本和它们预期的不一致时，可以立即得到行的以前版本，从而不用请求共享锁也能取得预期的一致性。

4.4 隔离级别的行为方式

4.4.1 未提交读 （READ UNCOMMITTED）

打开两个查询窗口，Connetion1,connection2

• Step1: 执行Connection1的阶段2的SQL 语句，然后执行connection2的SQL语句

• Step2: 执行Connection1的阶段3的SQL 语句，执行connection2的SQL语句

• Step3: 执行Connection1的阶段4的SQL 语句，执行connection2的SQL语句

--阶段2 UPDATE myProduct SET price = price + 1 WHERE id = 1; SELECT id ,
        price FROM dbo.myProduct WHERE id = 1; --阶段3 UPDATE myProduct SET price = price + 5 WHERE id = 1; SELECT id ,
        price FROM dbo.myProduct WHERE id = 1; --阶段4 COMMIT TRAN

--在阶段2执行之后 SET TRAN ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN; SELECT id ,
        price FROM dbo.myProduct WHERE id = 1 COMMIT TRAN;

两个事务的流程图：

• 阶段1：Price=10,事务A对myProduct表请求排他锁

• 阶段2：事务A对myProduct表使用了排他锁，更新price = price + 1，然后事务A查询price的价格: price=11。事务B不请求任何锁，事务B在A更新Price之后进行查询，price=11

• 阶段3：事务A更新price = price + 5，然后事务A查询price的价格，price = 16。事务B查询price的价格: price=16

• 阶段4：事务A释放排他锁

• 阶段5：事务A中查询price的价格:price = 16。事务B查询price的价格: price=16

「大家可以看到事务B有两种结果，这就是“未提交读 （READ UNCOMMITTED）”隔离级别的含义：」

（1）读操作可以读取未提交的修改（也称为脏读）。

（2）读操作不会妨碍写操作请求排他锁，其他事务正在进行读操作时，写操作可以同时对这些数据进行修改。

（3）事务A进行了多次修改，事务B在不同阶段进行查询时可能会有不同的结果。

4.4.2 已提交读（READ COMMITTED）（默认值）

打开两个查询窗口，Connetion1,connection2

Step1: 执行Connection1的SQL 语句

UPDATE` `dbo.myProduct ``SET` `price = price + 1 ``WHERE` `id=1 SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id =1 

Step2: 执行Connection2的SQL 语句

SET` `TRANSACTION` `ISOLATION` `LEVEL` `READ` `COMMITTED SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id = 1 

两个事务的流程图：

• 阶段1：Price=10,事务A对myProduct表请求排他锁

• 阶段2：事务A对myProduct表使用了排他锁，更新price = price + 1，然后事务A查询price的价格: price=11。然后事务B请求共享锁进行读操作，查询price，

由于在当前隔离级别下，事务A的排他锁和事务B的共享锁存在冲突，所以事务B需要等待事务A释放排他锁后才能读取数据。

• 阶段3：事务A提交事务（COMMIT TRAN）

• 阶段4：事务A提交完事务后，释放排他锁

• 阶段5：事务B获得了共享锁，进行读操作，price=11

  「“已提交读 （READ UNCOMMITTED）”隔离级别的含义：」

（1）必须获得共享锁才能进行读操作，其他事务如果对该资源持有排他锁，则共享锁必须等待排他锁释放。

（2）读操作不能读取未提交的修改，读操作读取到的数据是提交过的修改。

（3）读操作不会在事务持续期间内保留共享锁，其他事务可以在两个读操作之间更改数据资源，读操作因而可能每次得到不同的取值。这种现象称为“不可重复读”

4.4.3 可重复读（REPEATABLE READ）

打开两个查询窗口，Connetion1,connection2

Step1: 执行Connection1的SQL 语句

SET` `TRANSACTION` `ISOLATION` `LEVEL` `REPEATABLE` `READ SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id = 1 

Step2: 执行Connection2的SQL 语句

UPDATE` `dbo.myProduct ``SET` `price = price + 1 ``WHERE` `id=1 

演示与总结如下所示：

两个事务的流程图：

• 阶段1：Price=10,事务A对myProduct表请求共享锁

• 阶段2：事务A对myProduct表使用了共享锁，事务A查询price的价格: price=10，事务A一直持有共享锁直到事务A完成为止。然后事务B请求排他锁进行写操作price=price+1，

由于在当前隔离级别下，事务A的共享锁和事务B请求的排他锁存在冲突，所以事务B需要等待事务A释放共享锁后才能修改数据。

• 阶段3：事务A查询price, price=10, 说明事务B的更新操作被阻塞了，更新操作没有被执行。然后事务A提交事务（COMMIT TRAN）

• 阶段4：事务A提交完事务后，释放共享锁

• 阶段5：事务B获得了排他锁，进行写操作，price=11

**“可重复读 （REPEATABLE READ）”隔离级别的含义：  **

（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。

（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，这样可以保证实现可重复的读取。

（3）两个事务在第一次读操作之后都将保留它们获得的共享锁，所以任何一个事务都不能获得为了更新数据而需要的排他锁，这种情况将会导致死锁（deadlock），不过却避免了更新冲突。

4.4.4 可序列化（SERIALIZABLE）

打开两个查询窗口，Connetion1,connection2
Step1: 执行Connection1的SQL 语句

BEGIN TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT * FROM dbo.myProduct WHERE id = 1 

Step2: 执行Connection2的SQL 语句

INSERT` `INTO` `dbo.myProduct(id, price) ``VALUES` `(1, 20)

演示与总结如下所示：

两个事务的流程图：

• 阶段1：Price=10,事务A对myProduct表请求共享锁

• 阶段2：事务A对myProduct表使用了共享锁，事务A查询id=1的price的价格:1行记录，price=10，事务A一直持有共享锁直到事务A完成为止。然后事务B请求排他锁进行插入操作id=1,price=20，

由于在当前隔离级别下，事务B试图增加能够满足事务A的读操作的查询搜索条件的新行，所以事务A的共享锁和事务B请求的排他锁存在冲突，事务B需要等待事务A释放共享锁后才能插入数据。

• 阶段3：事务A查询出id=1的数据只有1行，说明事务B的插入操作被阻塞了，插入操作没有被执行。然后事务A提交事务（COMMIT TRAN）

• 阶段4：事务A提交完事务后，释放共享锁

• 阶段5：事务B获得了排他锁，进行插入操作，插入成功，查询出id=1的数据有两条-

「“可序列化（SERIALIZABLE）”隔离级别的含义：」

（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。

（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，且当其他事务增加能够满足当前事务的读操作的查询搜索条件的新行时，其他事务将会被阻塞，直到当前事务完成然后释放共享锁，其他事务才能获得排他锁进行插入操作。

（3）事务中的读操作在任何情况下读取到的数据是一致的，不会出现幻影行（幻读）。

（4）范围锁：读操作锁定满足查询搜索条件范围的锁。

4.5 隔离级别总结

「脏读：」 读取未提交的更改。

「不可重复读：」 读操作不会在事务持续期间内保留共享锁，其他事务可以在两个读操作之间更改数据资源，读操作因而可能每次得到不同的取值。

「丢失更新：」 两个事务进行读操作，获得资源上的共享锁，读取完数据后，不再持有资源上的任何锁，两个事务都能更新这个值，最后进行更新的事务将会覆盖其他事务做的更改，导致其他事务更改的数据丢失。

「幻读：」 第一次和第二次读取到的数据行数不一致。

「范围锁：」 读操作锁定满足查询搜索条件范围的锁

五.死锁

死锁是指一种进程之间互相永久阻塞的状态，可能涉及两个或更多的进程。

打开两个查询窗口，Connetion1,connection2

Step1: 执行Connection1的SQL 语句

SET` `TRANSACTION` `ISOLATION` `LEVEL` `READ` `COMMITTED BEGIN` `TRAN UPDATE` `dbo.myProduct ``SET` `price = price + 1 ``WHERE` `id=1 SELECT` `* ``FROM` `dbo.myOrder ``WHERE` `id =1 

Step2: 执行Connection2的SQL 语句

SET` `TRANSACTION` `ISOLATION` `LEVEL` `READ` `COMMITTED BEGIN` `TRAN UPDATE` `dbo.myOrder ``SET` `customer = ``'ddd'` `WHERE` `id = 1 SELECT` `* ``FROM` `dbo.myProduct ``WHERE` `id = 1 

演示与总结如下所示：

两个事务的流程图：

• 阶段1：Price=10,事务A对myProduct表请求排他锁。Customer = aaa,事务B对myOrder请求排他锁

• 阶段2：事务A对myProduct表使用了排他锁，更新price = price + 1。然后事务B对myOrder表使用了排他锁，更新customer=ddd。

• 阶段3：事务A查询myOrder表，对myOrder表请求共享锁，因为事务A的请求的共享锁与事务B的排他锁冲突，所以事务A被阻塞。然后事务B查询myProduct表，对myProduct表请求共享锁，因为事务B的请求的共享锁与事务A的排他锁冲突，所以事务B被阻塞。

• 阶段4：事务A等待事务B的排他锁释放，事务B等待事务A的排他锁释放，导致死锁。事务A和事务B都被阻塞了。

• 阶段5：SQL Server在几秒之内检测到死锁，会选择一个事务作为死锁的牺牲品，终止这个事务，并回滚这个事务所做的操作。在这个例子中，事务A被终止，提示信息：事务(进程 ID 53)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

“「死锁 （Dead Lock）」” 的一些注意事项：

（1）如果两个事务没有设置死锁优先级，且两个事务进行的工作量也差不多一样时，任何一个事务都有可能被终止。

（2）解除死锁要付出一定的系统开销，因为这个过程会涉及撤销已经执行过的处理。

（3）事务处理的时间时间越长，持有锁的时间就越长，死锁的可能性也就越大，应该尽可能保持事务简短，把逻辑上可以不属于同一个工作单元的操作移到事务以外。

（4）上面的例子中，事务A和事务B以相反顺序访问资源，所以发生了死锁。如果两个事务按同样的顺序来访问资源，则不会发生这种类型的死锁。在不改变程序的逻辑情况下，可以通过交换顺序来解决死锁的问题。