来源： 30分钟全面解析-SQL事务+隔离级别+阻塞+死锁 – 悟空聊架构 – 博客园

阅读目录

• 概述：
• 一、事务
• 二、锁
• 三、阻塞
• 四、隔离级别
• 五.死锁

以前总是追求新东西，发现基础才是最重要的，今年主要的目标是精通SQL查询和SQL性能优化。

 本系列主要是针对T-SQL的总结。

概述：

本篇主要是对SQL中事务和并发的详细讲解。

一、事务

1.什么是事务

为单个工作单元而执行的一系列操作。如查询、修改数据、修改数据定义。

2.语法

（1）显示定义事务的开始、提交

BEGIN TRAN
INSERT INTO b(t1) VALUES(1)
INSERT INTO b(t1) VALUES(2)
COMMIT TRAN

（2）隐式定义

如果不显示定义事务的边界，则SQL Server会默认把每个单独的语句作为一个事务，即在执行完每个语句之后就会自动提交事务。

3.事务的四个属性ACID

（1）原子性Atomicity

（2）一致性Consiitency

（3）隔离性Isolation

（4）持久性Durability

二、锁

1.事务中的锁

（1）SQL Server使用锁来实现事务的隔离。

（2）事务获取锁这种控制资源，用于保护数据资源，防止其他事务对数据进行冲突的或不兼容的访问。

2.锁模式

（1）排他锁

a.当试图修改数据时，事务只能为所依赖的数据资源请求排他锁。

b.持有排他锁时间：一旦某个事务得到了排他锁，则这个事务将一直持有排他锁直到事务完成。

c.排他锁和其他任何类型的锁在多事务中不能在同一阶段作用于同一个资源。

如：当前事务获得了某个资源的排他锁，则其他事务不能获得该资源的任何其他类型的锁。其他事务获得了某个资源的任何其他类型的锁，则当前事务不能获得该资源的排他锁。

（2）共享锁

a.当试图读取数据时，事务默认会为所依赖的数据资源请求共享锁。

b.持有共享锁时间：从事务得到共享锁到读操作完成。

c.多个事务可以在同一阶段用共享锁作用于同一数据资源。

d.在读取数据时，可以对如何处理锁定进行控制。后面隔离级别会讲到如何对锁定进行控制。

3.排他锁和共享锁的兼容性

（1）如果数据正在由一个事务进行修改，则其他事务既不能修改该数据，也不能读取（至少默认不能）该数据，直到第一个事务完成。

（2）如果数据正在由一个事务读取，则其他事务不能修改该数据（至少默认不能）。

4.可锁定的资源的类型

RID、KEY（行）、PAGE（页）、对象（例如表）、数据库、EXTENT（区）、分配单元（ALLOCATION_UNIT）、堆（HEAP）、以及B树（B-tree）。

RID: 标识页上的特定行
格式: fileid: pagenumber: rid （1:109:0 ）
其中fileid标识包含页的文件， pagenumber标识包含行的页，rid标识页上的特定行。
fileid与sys.databases_files 目录视图中的file_id列相匹配
例子：
在查询视图sys.dm_tran_locks的时候有一行的resource_description列显示RID 是1:109:0 而status列显示wait,
表示第1个数据文件上的第109页上的第0行上的锁资源。

5.锁升级

SQL Server可以先获得细粒度的锁（例如行或页），在某些情况下将细粒度锁升级为更粗粒度的锁（例如，表）。
例如单个语句获得至少5000个锁，就会触发锁升级，如果由于锁冲突而导致无法升级锁，则SQL Server每当获取1250个新锁时出发锁升级。

三、阻塞

1.阻塞

当多个事务都需要对某一资源进行锁定时，默认情况下会发生阻塞。被阻塞的请求会一直等待，直到原来的事务释放相关的锁。锁定超时期限可以限制，这样就可以限制被阻塞的请求在超时之前要等待的时间。

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
 
--Connection2
SELECT * FROM dbo.myProduct WHERE id=1
 
--Connection3
SELECT  request_session_id AS 会话id ,
		resource_type AS 请求锁定的资源类型 ,
		resource_description AS 描述 ,
		request_mode AS 模式 ,
		request_status AS 状态
FROM    sys.dm_tran_locks

查询窗口	服务器进程标识符SPID	执行语句	结果	说明
Connection1	52	--语句1： BEGIN TRAN UPDATE dbo.myProduct SET price = price + 1 WHERE id=1 更新产品价格10.00->11.00		为了更新id=1这一行数据，会话必须先获得一个排他锁。事务处于一直打开状态，没有提交，所以事务一直持有排他锁，直到事务提交并完成。
Connection2	56	--语句2： SELECT * FROM dbo.myProduct WHERE id=1		事务为了读取数据，需要请求一个共享锁，但是这一行已经被其他会话持有的排他锁锁定，而且共享锁和排他锁不是兼容的，所以会话被阻塞，进入等待状态
Connection3	57	--语句3： SELECT request_session_id AS 会话id , resource_type AS 请求锁定的资源类型 , resource_description AS 描述 , request_mode AS 模式 , request_status AS 状态 FROM sys.dm_tran_locks		会话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资源，排他锁

（2）分析阻塞

★ 1.sys.dm_tran_locks 视图

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

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

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

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

★ 2.sys.dm_exec_connections 视图

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

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

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

查询窗口	服务器进程标识符SPID	执行语句	结果	说明
Connection3	57	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.sys.dm_exec_sql_text 表函数

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

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

查询窗口	服务器进程标识符SPID	执行语句	结果	说明
Connection3	57	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 )		会话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

★ 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

★ 5.sys.dm_exec_requests 视图

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

SELECT * FROM sys.dm_exec_sessions

★ 6.Lock_TIMEOUT 选项

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

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

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

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

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

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

★7.KILL <spid> 命令

（1）杀掉会话52，KILL 52

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

四、隔离级别

1.基本概念：

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

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

（2）写操作
a.任何对表做出修改的语句

b.使用排他锁

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

（3）读操作：

a.任何检索数据的语句

b.默认使用共享锁

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

2.隔离级别的分类

（1）未提交读 （READ UNCOMMITTED）

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

（3）可重复读（REPEATABLE READ）

（4）可序列化（SERIALIZABLE）

（5）快照（SNAPSHOT）

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

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);

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;

两个事务的流程图：

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

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

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

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

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

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

Step1: 执行Connection1的SQL 语句

Step2: 执行Connection2的SQL 语句

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

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

两个事务的流程图：

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

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

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

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

★ 3.可重复读（REPEATABLE READ）

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

Step1: 执行Connection1的SQL 语句

Step2: 执行Connection2的SQL 语句

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

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

两个事务的流程图：

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

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

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

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

★ 4.可序列化（SERIALIZABLE）

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

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

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

两个事务的流程图：

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

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

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

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

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

5.隔离级别总结

五.死锁

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

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

Step1: 执行Connection1的SQL 语句

Step2: 执行Connection2的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

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

两个事务的流程图：

关于分析死锁的问题，可以参考前面写的关于阻塞的内容。

原文链接：30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

参考资料：《T-SQL基础》