SqlServer事务详解（事务隔离性和隔离级别详解）

2023-08-03 20:39:22

概述

　　不少人对于事务的使用局限于begin transaction：开始事务、commit transaction：提交事务、rollback transaction：回滚事务的初步运用。

并且知道使用事务后，事务中所有操作命令必须作为一个整体提交或回滚，如果事务中任何操作命令失败，则整个事务将因失败而回滚。

　　除了这个概念性的东西后，其他就不怎么知道了，比如事务的隔离性，具体怎么隔离、有几种隔离方式、执行顺序是什么。

我们今天来聊一聊这一块的内容（主要是事务的隔离性）。

什么是事务（定义）

　　事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。

事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。

事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

事务是作为单个逻辑单元执行的一系列操作，它是一个不可分割的工作逻辑单元。它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。

回到顶部

事务的4个特性（特性）

事务是恢复和并发控制的基本单位。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性（atomicity）

　　原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，

因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（consistency）

　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

比如转账，假设张三和李四两个人的钱加起来一共是2000，那么不管张三和李四之间怎么转账，转几次账，事务结束后两个人的钱加起来还得是2000，这就是事务的一致性。

隔离性（isolation）

　　隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

持久性（durability）

持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

　　在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，

即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

回到顶部

事务的分类（分类）

事务分为三类：显式事务、隐式事务、自动提交事务

(1) 显式事务：用 begin transaction 明确指定事务的开始，由 commit transaction 提交事务、rollback transaction 回滚事务到事务结束。

(2) 隐式事务：通过设置 set implicit_transactions on 语句，将隐式事务模式设置为打开。

当以隐式事务模式操作时，不必使用 begin transaction 开启事务，当一个事务结束后，这个模式会自动启用下一个事务，

只需使用 commit transaction 提交事务或 Rollback Transaction 回滚事务即可。

(3) 自动提交事务：这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

回到顶部

简单事务应用示例

我们先看一个简单的应用。

--这里指定不指定事务名称均可
BEGIN TRANSACTION tran_UpdateTable --开启事务(tran_UpdateTable：事务名称)

DECLARE @tran_error int;--定义错误变量
SET @tran_error=0;        --错误变量的初始值为0

--使用 try catch进行错误捕捉
BEGIN TRY
UPDATE a_Students SET Name='孙悟空' WHERE Number='100001';
UPDATE a_StudentsScore SET C# ='我是分数,应该是数字' WHERE Number='100001';
END TRY

BEGIN CATCH
set @tran_error=@tran_error+1; --（计算捕捉到的错误数）加分号或不加都能正常执行
END CATCH

--判断是否有执行错误
IF(@tran_error>0)
    BEGIN 
        ROLLBACK TRANSACTION tran_UpdateTable  --执行出错，回滚事务（tran_UpdateTable：指定事务名称）
        PRINT '有【'+CONVERT(VARCHAR(50),@tran_error)+'】条执行失败，进行回滚:'; 
    END 
ELSE
    BEGIN
        COMMIT TRANSACTION tran_UpdateTable --没有异常，提交事务(tran_UpdateTable：指定事务名称)
        --事务执行成功后，查看修改后的数据
        SELECT  s.Number ,
        s.Name ,
        sc.ClassName ,
        ss.C# ,
        ss.SqlDB ,
        ss.Java ,
        ss.Python
        FROM    a_Students s
        INNER JOIN a_StudentClass sc ON s.ClassId = sc.ClassId
        INNER JOIN a_StudentsScore ss ON s.Number = ss.Number
        WHERE s.Number='100001'
    END

上面事务表示：根据学生编号修改学生姓名和学生学科C#的成绩，如果有任何一条执行失败则全部返回不执行，否则执行修改成功。

回到顶部

事务不隔离导致的问题

以上介绍完事务的四大特性(简称ACID)，现在重点来说明下事务的隔离性，

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，

在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

更新丢失（Lost update）、脏读（Dirty Reads）、不可重复读（Non-repeatable Reads）

1、更新丢失（Lost update）

两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。

2、脏读（Dirty Reads）

一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。

　　当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。

例如：用户A向用户B转账1000元，对应SQL命令如下：

--用户A向用户B转账1000元，B的钱增加
update account set money=money+1000 where name='B';


--此时A通知B我给你转钱了，A的钱减少
update account set money=money - 1000 where name='A';

　　当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），

而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

3、不可重复读（Non-repeatable Reads）

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

（1）虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

（2） 幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。

这是因为在两次查询过程中有另外一个事务插入数据造成的。

回到顶部

事务的隔离级别

为了避免上面出现的几种情况，在标准SQL规范中，定义了4+2个事务隔离级别，不同的隔离级别对事务的处理不同

1、未提交读取（相当于with(nolock)）：第一级别

　　也称为未授权读取：允许脏读取，但不允许更新丢失。

如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。

　　该隔离级别可以通过“排他写锁”实现。

　　缺点：会产生脏读、不可重复读、幻读。

　　案例解读：以前交易所炒股的时候，股民老王购买了5000股，操作员操作录入（此时开启事务），操作时手误，多输入了一个0，数据保存但是未提交。

此时老王查询自己的持股信息，发现自己居然有50000股，瞬间血压升高，昏倒在地。

然后操作员发现自己录入错误，修改成正确的信息，录入完成（事务结束）。

老王被救醒后，哆嗦这查询自己的持股，发现只有5000，没有增减，他之前看到的就是脏读数据。

　　解决方案：采用更高级的隔离机制，如提交读。

2、提交读取（Oracle和SQLServer默认的）：第二级别

　　这是大多数数据库系统的默认隔离级别（Oracle和SQLServer默认的）。

　　也称为授权读取：允许不可重复读取，但不允许脏读取。

这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

　　缺点：会产生不可重复读、幻读。

　　案例解读：股市升高后，老王查看自己持有5000股，就想卖掉4000股，在老王卖股票的时候，老王的老婆看股市太高，就登录老王的账号，卖掉3000股。

当老王想卖股票时，发现自己只有2000股，不是之前看到的5000股，这就是不可重复读问题。

　　解决方案：采用更高级的隔离机制，如可重复读。

3、可重复读取（相当于(HOLDLOCK)）：第三级别

　　MySQL的默认事务隔离级别。

　　可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻读数据。

这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

　　缺点：会产生幻读。

　　问题解读：股市忽涨忽跌，老王焦虑不安，按捺不住，想把持有的多种股票全部抛掉。与此同时，老王老婆听信专家所言，使用老王的账号买了某只神股。

老王抛掉所有股票后，查看自己的持股，猛然发现自己居然还持有一只股票，瞬间觉得一脸懵逼，这就是幻读导致。

　　解决方案：采用更高级的隔离机制，序列化。

4、序列化（这是最高的隔离级别）：第四级别

　　序列化（Serializable）：提供严格的事务隔离。

它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。

仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

　　缺点：可以解决并发事务的所有问题。但是效率地下，消耗数据库性能，一般不使用。

　　隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。

尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

5、快照

(1)SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本。

(2)同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制。

6、已提交读快照

READ COMMITTED SNAPSHOT也是基于行版本控制，但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本，

而不是事务前的已提交版本，有点类似前面的READ COMMITTED能保证已提交读，但是不能保证可重复读，不能避免幻读，

但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据。

回到顶部

事务的隔离级别设置示例

　　SQL Server通过在锁资源上使用不同类型的锁来隔离事务。

为了开发安全的事务，定义事务内容以及应在何种情况下回滚至关重要，定义如何以及在多长时间内在事务中保持锁定也同等重要。

这由隔离级别决定，应用不同的隔离级别，SQL Server赋予开发者一种能力，让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下：

获取事务隔离级别(isolation level)

DBCC USEROPTIONS

1、未提交读取

新建回话并将学生编号为100001的成绩+1；

BEGIN TRANSACTION
UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'

SELECT * FROM a_StudentsScore 
WHERE Number='100001'

然后执行错误的回话，进行回滚

BEGIN TRANSACTION
UPDATE a_StudentsScore 
SET C#=C#+''
WHERE Number='100001'

然后在查询数据

--首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁，所以查询一直在等待锁释放*/
SELECT * FROM a_StudentsScore 
WHERE Number='100001'
---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读，读操作之前不请求共享锁。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM a_StudentsScore 
WHERE Number='100001'
--当然也可以使用表隔离,效果是一样的
SELECT * FROM a_StudentsScore WITH (NOLOCK)
WHERE Number='100001'

我们发现执行两个事务回话，第一个执行成了，但是第二个执行失败了进行回滚，最后查询的数据是第一个执行前的数据，没有任何改变。

2、提交读取

新建回话1并将学生编号为100001的成绩+1，此时回话的排他锁锁住了学生编号为100001的成绩

BEGIN TRANSACTION
UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'

SELECT * FROM a_StudentsScore 
WHERE Number='100001'

在回话2中执行查询，将隔离级别设置为READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM a_StudentsScore 
WHERE Number='100001'
--由于READ COMMITTED需要申请获得共享锁，而锁与回话1的排他锁冲突，回话被堵塞，

--在回话1中执行事务提交
COMMIT TRANSACTION
--由于回话1事务提交，释放了学生100001的排他锁，此时回话2申请共享锁成功查到学生100001的C#成绩为修改后的成绩81，READ COMMITTED由于是已提交读隔离级别，所以不会读脏数据.

重置数据

UPDATE a_StudentsScore 
SET C#=80
WHERE Number='100001'

注意：但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,

也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.

3、可重复读取

在回话1中查询学生编号为100001的成绩，,将回话级别设置为REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

新建回话2修改学生编号为100001的成绩

UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'
---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束，所以回话2无法获取排他锁，处于等待状态

在回话1中执行下面语句，然后提交事务

SELECT * FROM a_StudentsScore 
WHERE Number='100001'
COMMIT TRANSACTION

回话1的两次查询得到的结果一致，前面的两个隔离级别无法得到一致的数据，此时事务已提交同时释放共享锁，回话2申请排他锁成功，对行执行更新

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致，同时保证了丢失更新
丢失更新：两个事务同时读取了同一个值然后基于最初的值进行计算，接着再更新，就会导致两个事务的更新相互覆盖。
例如酒店订房例子，两个人同时预定同一酒店的房间，首先两个人同时查询到还有一间房间可以预定，然后两个人同时提交预定操作，事务1执行number=1-0，同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖，REPEATABLE READ隔离级别就能避免这种丢失更新的现象，当事务1查询房间时事务就一直保持共享锁直到事务提交，而不是像前面的几个隔离级别查询完就是否共享锁，就能避免其他事务获取排他锁。

4、序列化

SERIALIZABLE(可序列化)，对于前面的REPEATABLE READ能保证事务可重复读，但是事务只锁定查询第一次运行时获取的数据资源（数据行），

而不能锁定查询结果之外的行，就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间，有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时，

那么在第二次查询的结果中就会存在这些新插入的数据，使两次查询结果不一致，这种读操作称之为幻读。

为了避免幻读需要将隔离级别设置为SERIALIZABLE。

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

在回话2中执行修改操作

UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'

返回回话1重新执行查询操作并提交事务

SELECT * FROM a_StudentsScore 
WHERE Number='100001'
COMMIT TRANSACTION

结果回话1中第二次查询到的数据包含了回话2新修改的数据，两次查询结果不一致（验证之前的隔离级别不能保证幻读）

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

在回话2中执行修改操作

UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'

返回回话1重新执行查询操作并提交事务

SELECT * FROM a_StudentsScore 
WHERE Number='100001'
COMMIT TRANSACTION

两次执行的查询结果相同

重置所有打开回话的默认隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

5、快照

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制
使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

在打开的所有查询窗口中执行以下操作

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

--在回话1中打开事务,将学生100001的成绩加1,并查询跟新后的成绩
BEGIN TRANSACTION
UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'

SELECT * FROM a_StudentsScore 
WHERE Number='100001'
---查询到更新后的成绩为81

---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

---查询到的结果还是回话1修改前的成绩,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中
--在SNAPSHOT级别启动事务会请求行版本

---现在在回话1中执行提交事务,此时学生100001的成绩为81
COMMIT TRANSACTION

---再次在回话2中查询学生100001的成绩并提交事务,结果还是80,因为事务要保证两次查询的结果相同
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

COMMIT TRANSACTION

---此时如果在回话2中重新打开一个事务,查询到的学生100001的成绩为81
BEGIN TRANSACTION
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

COMMIT TRANSACTION

--SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以学生100001的最后提交版本还是修改前的成绩80,
--所以回话2读取到的成绩是回话2事务开始前的已提交版本成绩80,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的成绩已经是81了，
--所以查询到的成绩是81，同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别

6、已提交读快照

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本，

有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项，

在回话1，回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接，可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)。

--在回话1中打开事务,将学生100001的成绩加1,并查询跟新后的成绩,并保持事务一直处于打开状态
UPDATE a_StudentsScore 
SET C#=C#+1
WHERE Number='100001'

--查询到的成绩是81
SELECT * FROM a_StudentsScore 
WHERE Number='100001'

--在回话2中打开事务查询学生100001并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)
BEGIN TRANSACTION
SELECT * FROM a_StudentsScore 
WHERE Number='100001'
--查询到的成绩还是80

--在回话1中提交事务
COMMIT TRANSACTION 

--在回话2中再次执行查询学生100001的成绩,并提交事务
SELECT * FROM a_StudentsScore 
WHERE Number='100001'
COMMIT TRANSACTION 
--此时的成绩为回话1修改后的成绩81,而不是事务之前已提交版本的成绩,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项。

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

参考文献1：百度百科：事务

参考文献2：百度百科：事务隔离级别

作者：熊泽-学习中的苦与乐

码农公寓

概述

什么是事务（定义）

事务的4个特性（特性）

原子性（atomicity）

一致性（consistency）

隔离性（isolation）

持久性（durability）

事务的分类 （分类）

简单事务应用示例

事务不隔离导致的问题

1、更新丢失（Lost update）

2、脏读（Dirty Reads）

3、不可重复读（Non-repeatable Reads）

事务的隔离级别

1、未提交读取（相当于with(nolock)）：第一级别

2、提交读取（Oracle和SQLServer默认的）：第二级别

3、可重复读取（相当于(HOLDLOCK)）：第三级别

4、序列化（这是最高的隔离级别）：第四级别

5、快照

6、已提交读快照

事务的隔离级别设置示例

1、未提交读取

2、提交读取

3、可重复读取

4、序列化

5、快照

6、已提交读快照

相关文章

事务的分类（分类）