【SqlServer】解析SqlServer中的事务

目录结构：

contents structure [+]

1. 事务是什么
2. 控制事务
3. 数据并发访问产生的影响
4. 事务的隔离级别
5. 锁
1. NOLOCK、HOLDLOCK、UPDLOCK
2. 死锁分析

在这篇Blog中，笔者将会解析阐述SqlServer中的事务，希望可以对你有所帮助。

1.事务是什么

事务就是单个逻辑单元执行的一系列操作。事务都具有ACID特性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性（Durability）。

原子性(Atomicity):事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性(Consistency):事务在完成时，必须使所有的数据都保持一致状态。 在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。 事务结束时，所有的内部数据结构都必须是正确的。

隔离性(Isolation):由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。 事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。 这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

持久性(Durability):完成完全持久的事务之后，它的影响将永久存在于系统中。 该修改即使出现系统故障也将一直保持。 SQL Server 2014和更高版本将启用延迟的持久事务。 提交延迟的持久事务后，该事务日志记录将保留在磁盘上。

2.控制事务

应用程序主要通过指定通过控制事务的启动和事务的结束时间来控制事务。

启动事务的方式主要包括显式、自动提交、隐式启动事务事务。

显式启动事务：

通过函数发出BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK 语句明确定义事务的开始和结束。 当事务结束时，连接将返回到启动显式事务前所处的事务模式，或者是隐式模式，或者是自动提交模式。

自动提交事务：

自动提交模式是 SQL Server 数据库引擎的默认事务管理模式。 每个SQL语句在完成时，都被提交或回滚。 如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。 只要没有显式事务或隐性事务覆盖自动提交模式，与数据库引擎实例的连接就以此默认模式操作。

隐式事务：

当连接以隐式事务模式进行操作时，数据库引擎实例将在提交或回滚当前事务后自动启动新事务。 无须描述事务的开始，只需提交或回滚每个事务。 隐性事务模式生成连续的事务链。 通过 API 函数或 SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开。

结束事务可以通过COMMIT 或 ROLLBACK 语句，或者通过相应 API 函数来结束事务。

COMMIT语句：

如果事务成功，则提交。 COMMIT 语句保证事务的所有修改在数据库中都永久有效。 COMMIT 语句还释放事务使用的资源（例如，锁）。

ROLLBACK语句：

如果事务中出现错误，或用户决定取消事务，则回滚该事务。 ROLLBACK 语句通过将数据返回到它在事务开始时所处的状态，来取消事务中的所有修改。 ROLLBACK 还释放事务占用的资源。

例如下面是事务使用的常见方式：

--启动事物
begin tran
    begin try
        insert into TableTest(name,age) values('test',12);
    end try
    begin catch
        select    ERROR_NUMBER() error_number,
                ERROR_SEVERITY() error_severity,
                ERROR_STATE() error_state,
                ERROR_PROCEDURE() error_procedure,
                ERROR_LINE() error_line,
                ERROR_MESSAGE() error_message;
        if(@@trancount>0)
            rollback tran     --回滚事务
    end catch
if(@@trancount>0)
    commit tran --提交事务

3.数据并发访问产生的影响

一个用户在修改数据可能会影响到此时其它用户正在读取、修改的数据。即用户并发访问同一资源时，可能会产生如下影响：

丢失更新：

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。 每个事务都不知道其他事务的存在。 最后的更新将覆盖由其他事务所做的更新，这将导致数据丢失。

例如，两个编辑人员制作了同一文档的电子副本。 每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。 最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。 如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

未提交的依赖关系（脏读）

当第二个事务选择其他事务正在更新的行时，会发生未提交的依赖关系问题。 第二个事务正在读取的数据还没有提交并且可能由更新此行的事务所更改。

例如，一个编辑人员正在更改电子文档。 在更改过程中，另一个编辑人员复制了该文档（该副本包含到目前为止所做的全部更改）并将其分发给预期的用户。 此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。 分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应视为从未存在过。 如果在第一个编辑人员保存最终更改并提交事务之前，任何人都不能读取更改的文档，则可以避免此问题。

不一致的分析（不可重复读）

当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。 不一致的分析与未提交的依赖关系类似，因为其他事务也是正在更改第二个事务正在读取的数据。 但是，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。 此外，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都被其他事务更改，因此我们称之为“不可重复读”。

例如，编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。 当编辑人员第二次读取文档时，文档已更改。 原始读取不可重复。 如果在编辑人员完成最后一次读取文档之前，作者不能更改文档，则可以避免此问题。

虚拟读取

执行两个相同的查询但第二个查询返回的行集合是不同的，此时就会发生虚拟读取。

上面并发访问产生的这些负面影响，在SQLServer中提供了一种机制来限制这些情况，该机制就是数据库中的事务隔离级别。在介绍事务的隔离级别后，笔者会详细阐述这四种负面影响。

4.事务的隔离级别

事务指定一个隔离级别，该隔离级别定义一个事务必须与其他事务所进行的资源或数据更改相隔离的程度。

数据库引擎隔离级别

ISO 标准定义了下列隔离级别，SQL Server 数据库引擎支持所有这些隔离级别：

隔离级别	定义
未提交读(READ UNCOMMITTED)	隔离事务的最低级别，只能保证不读取物理上损坏的数据。 在此级别上，允许脏读，因此一个事务可能看见其他事务所做的尚未提交的更改。
已提交读(READ COMMITTED)	允许事务读取另一个事务以前读取（未修改）的数据，而不必等待第一个事务完成。 数据库引擎保留写锁（在所选数据上获取）直到事务结束，但是一执行 SELECT 操作就释放读锁。 这是数据库引擎默认级别。
可重复读(REPEATABLE READ)	数据库引擎保留在所选数据上获取的读锁和写锁，直到事务结束。 但是，因为不管理范围锁，可能发生虚拟读取。
可序列化(SERIALIZABLE)	隔离事务的最高级别，事务之间完全隔离。 数据库引擎保留在所选数据上获取的读锁和写锁，在事务结束时释放它们。 SELECT 操作使用分范围的 WHERE 子句时获取范围锁，主要为了避免虚拟读取。

SqlServer默认的隔离级别是READ COMMITTED。可通过 SQL 脚本  SET TRANSACTION ISOLATION LEVEL  语句修改隔离级别。

下表显示了不同隔离级别导致的并发副作用。

隔离级别	脏读	不可重复读	虚拟读取
未提交的读取(READ UNCOMMITTED)	是	是	是
已提交的读取(READ COMMITTED)	否	是	是
可重复的读取(REPEATABLE READ)	否	否	是
可序列化(SERIALIZABLE)	否	否	否

通过这个表可以观察出，默认的隔离级别READ COMMITTED 不支持脏读，但是支持不可重复读和虚拟读取。

笔者为了更好的阐述并发产生的负面影响，在下面的例子中笔者不使用系统默认的隔离级别，重新设置为 未提交的读取(READ UNCOMMITTED)
脏读：
在第一个客户端，执行如下命令：

begin tran
insert into TableTest(name,age) values('jamy',21);
--延迟20秒后，提交事务
waitfor delay '0:0:20'
commit tran

在第二个客户端中，执行如下命令：

--设置事务隔离级别为 未提交的读取
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
GO
begin tran
select * from TableTest;
commit tran

在执行第一个客户端后，然后迅速执行第二个客户端。会发现第二个客户端读取到第一个客户端还未提交的数据。

第二个客户端之所以读取到第一个客户端未提交的数据，这是因为它的隔离级别READ UNCOMMITTED，使得它可以读取到其他事务中未提交的数据。

不可重复读：
在第一个客户端中，执行如下命令：

--设置事务隔离级别为 未提交的读取
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
GO
begin tran
--第一次查询
select * from TableTest where age=21;
waitfor delay '0:0:10'
--第二次查询
select * from TableTest where age=21;
commit tran

第二个客户端中，执行如下命令：

begin tran
update TableTest set name='gosling' where age=21;
waitfor delay '0:0:20'
commit tran

在执行第一个客户端后，再执行第二个客户端。会发现第一个客户端中，同一个事务内，相同的查询条件却得到不同的数据：


虚拟读取：
第一个客户端命令：

--设置事务隔离级别为 未提交的读取
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
GO
begin tran
--第一次查询,查询表格的第一行
select top 1 * from TableTest;
waitfor delay '0:0:10'
--第二次查询，查询表格的第一行
select top 1 * from TableTest;
commit tran

第二个客户端的SQL语句如下：

begin tran
--设置影响的行数为1
set rowcount 1;
delete from TableTest;
--恢复默认
set rowcount 0;
waitfor delay '0:0:20'
commit tran

在第一个客户端执行后，立即执行第二个客户端，在第一个客户端的结果中可以看出，虽然执行相同的命令，但是两次得到的结果是不同的。

上面读者也需已经知道了设置隔离级别为未提交的读取（READ UNCOMMITTED），其余的隔离级别推理也就可以知道。隔离级别通过锁来控制并发产生的负面影响，下面介绍锁。

5.锁

锁是 SQL Server 数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。事务的隔离级别也是通过控制锁的释放时间来控制并发产生的负面影响。

应用程序一般不直接请求锁。 锁由数据库引擎的一个部件（称为“锁管理器”）在内部管理。 当数据库引擎实例处理 Transact-SQL 语句时，数据库引擎查询处理器会决定将要访问哪些资源。 查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型。 然后，查询处理器将向锁管理器请求适当的锁。 如果与其他事务所持有的锁不会发生冲突，锁管理器将授予该锁。

锁模式：

锁模式	描述
共享 (S)	用于不更改或不更新数据的读取操作，如 SELECT 语句。
更新 (U)	用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排他 (X)	用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。

共享锁

共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。

更新锁

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

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

排他锁

排他锁（X 锁）可以防止并发事务对资源进行访问。 使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此，数据修改语句通常请求共享锁和排他锁。 例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。

在更新锁中，对同一个数据来说，一个事务中的排他锁和其他事务中的共享锁不相兼容，那么这是为什么？这里笔者假设可以相互兼容的话，即一个事务中的排他锁和其他事务中的共享锁相互兼容的话，那么会引起如下的问题:

对同一资源，在一个事务A中有select语句，select持有共享锁并且未释放，同时在另一个事务B中有update语句，update已经执行完查询操作，准备释放共享锁请求排他锁。那么这时候，无论事务隔离级别有多高，事务B都可以控制成功请求排他锁，在修改数据后，可能觉得数据不正确，又重新修改了，那么这个时候事务A中同样的查询条件有可能读取到两种状态的数据。这就造成了不可重复读现象。虽然在上面的事务的隔离级别中，有些隔离级别是允许出现不可重复读的现象（默认的隔离级别就允许）。但是这里出现的不可重复读现象，将不受隔离级别限制（因为无论什么隔离级别，排他锁的事务都可以修改共享锁的事务正在访问的数据）。也就是说，不能控制不可重复读现象了。

5.1 NOLOCK、HOLDLOCK、UPDLOCK

在上面我们介绍了锁的一些概念，接下来，讲解一下如何加锁。
NOLOCK:无锁
HOLDLOCK:持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别
UPDLOCK:更新锁
例如：

select * from TableA with (HOLDLOCK);

5.2 死锁分析

常见的死锁形势为：事务A持有TableA的排他锁请求TableB的排他锁，事务B持有TableB的排他锁请求TableA的排他锁。
比如：

--第一个事务
begin tran
update TableA set age=12 where name='abc';
waitfor delay '0:0:10'
update TableB set age=14 where name='abc';
commit tran
--第二个事务
begin tran
update TableB set age=12 where name='abc';
waitfor delay '0:0:10'
update TableA set age=14 where name='abc';
commit tran

这两个事务同时执行，然后会出现死锁现象。除了这种死锁现象，还有另一种死锁现象，就是上面介绍更新锁（U）所时提到的，那种死锁比较隐蔽。
例如：

update TableA set age=12 where name='abc';
update TableA set age=13 where name='abc';

这两个语句放到不同的事务中同时执行，是有可能出现死锁现象的，原因在上面更新锁的讲解中已经解释了。

除了可以给查询字段添加索引外，还可以使用UPDLOCK来避免出现死锁：

update TableA with (UPDLOCK) set age=12 where name='abc';
update TableA with (UPDLOCK) set age=13 where name='abc';

用上面两个update语句展示死锁可能不太直观，下面的例子比较好的展示了该类型的死锁，看如下的两个事务：
第一个事务：

begin tran a
    select * from TableTest with (holdlock);
--      select * from TableTest with (updlock,holdlock) 加入updlock避免死锁
    waitfor delay '0:0:5'
    update TableTest set name='asdddd';
commit tran a

第二个事务：

begin tran b
select * from TableTest with (holdlock);
--select * from TableTest with (updlock,holdlock) 

加入updlock避免死锁

update TableTest set name='dfs'; commit tran b

接下来再分析一种SQL语句，如下：

if not exists (select ... from TableA ....)
insert into TableA ....
else
update TableA set....

如果在并发执行该sql的情况的话，那么这种SQL就无疑是增加了死锁出现了几率，因为select ... from TableA ....延长了共享锁占用的时间。这时候需要加入更新锁，更新锁从语法上来说可以加到select、update、insert....语句中，但是这里笔者建议加到select语句中。

倘若加到insert或update语句中，任然有可能出现死锁现象。一个事务（取名为：事务1）执行select语句，持有TableA的共享锁。恰好这是时候，另一个事务（取名为：事务2）执行insert语句，正在等待事务1释放TableA的共享锁。事务1执行完select语句释放TableA的共享锁（事务的隔离级别为：READ COMMITTED），事务1接着可能会执行insert语句。这时候事务1和事务2就存在竞争共享锁的问题，如果事务2在事务1执行完select释放共享锁和执行insert重新获得共享锁之间，获得了TableA的X锁，执行完更新操作，那么就不会发生死锁；若事务2并未这个时间内获得TableA的X锁，事务1会继续获得TableA的S锁，然后事务1和事务2都等待对方释放S锁，就会发生死锁。

参考文档：SqlServer事务锁定和行版本控制