概述

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

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

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

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

什么是事务（定义）

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

事务的4个特性（特性）

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

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

原子性（atomicity）

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

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

一致性（consistency）

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

隔离性（isolation）

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

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

持久性（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语句相同）。

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

事务的隔离级别

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

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

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

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

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

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

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

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

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

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

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

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

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'

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

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

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

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

然后在查询数据

--首先不添加隔离级别,默认是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'

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

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

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

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

4、序列化

为了避免幻读需要将隔离级别设置为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、快照

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

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隔离级别同样需要修改数据库选项，

在回话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：百度百科：事务隔离级别

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

一只努力学习的程序猿

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

概述

什么是事务（定义）

事务的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、已提交读快照

事务的分类（分类）