（3.12）常用知识-事务的隔离级别

部分参考引用自：https://www.cnblogs.com/ljhdo/p/5037033.html

【1】设置隔离级别最佳实践

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL
     READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE

--查看当前会话的隔离级别
DBCC UserOptions

实际相关操作：

1，使用snapshot 隔离级别

step1，设置数据库选项

ALTER DATABASE CURRENT SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON; 
--ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT OFF; 
ALTER DATABASE CURRENT SET MULTI_USER;
step2，修改Session-Level的隔离级别为snapshot

set transaction isolation level snapshot
2，使用Read_Committed_Snapshot隔离级别

ALTER DATABASE CURRENT SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON; 
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON; 
ALTER DATABASE CURRENT SET MULTI_USER;

【2】事务的概念

首先什么是事务
　　事务指数据操作的最小逻辑单元。一个事务要么完成，要么不完成，不存在粒度更小的数据操作。
在关系型数据中，当没有使用“begin transaction”显式的定义一个事务时，一条SQL语句默认就是一个transaction。例如：

insert into my_table_1 
select * 
from my_table_2

这条语句就是一个transaction。my_table_2中的所有数据都会被插入到my_table_1中，要么操作失败一条都没有插入，要么全部插入，不存在其他可能性。 

　　需要注意的是SQL Server是隐式提交事务的，即运行一条DML会直接提交到数据库，不需要显式指定Commit关键字。但Oracle则不会自动提交DML，除非遇到一条Commit或Rollback语句。在上例中，如果插入了一半后发生了某种错误，Oracle将回滚已插入的那部分数据，这称为statement-level rollback，且回滚后事务挂起，等待用户的进一步指令。
　　在SQL Server中，一个存储过程或函数中包含多个SQL语句，且没有显式指定事务时，多条SQL语句默认不在一个transaction里。即可能出现一个存储过程中的前半部分SQL执行成功，但后面的SQL没有执行的情况。
如果显式的使用“begin transaction”定义了transaction，则被包含到语句里的多条SQL属于一个transaction。
　　另外在SQL Server中，使用Savepoint可以有条件的回滚部分事务。虽然这样做破坏了事务的基本含义，但由于通常回滚操作的代价很高，因此部分回滚事务是一种现实的折中选择。Oracle也有类似的功能。

【2.1】ACID原则

　　ACID(Atomicity, Consistency, Isolation, Durability)是关系型数据库都采用的原则。具体含义是：
　　Atomicity原子性：指一个事务(transaction)要么全部执行，要么完全不执行。也就是不允许一个事物只执行了一半就停止。
　　consistency一致性: any transaction will bring the database from one valid state to another.在分布式系统中，一致性也分很多种或者说程度，即一个事务对分布式系统的影响可能是局部的。但在关系型数据库中，一致性就是指强一致性，即整个系统处于统一的状态。
　　Durability持久性：当事务运行成功后，对系统状态的影响是持久的，不会无缘无故的撤销。在实现上，持久性一般对应着数据在硬盘上的持久化。
　　Isolation独立性：当有多个事务同时运行时，它们之间互相独立，互不干扰的程度。我们今天要说的事务隔离级别就是关于Isolation。

【2.2】事务的现象与隔离级别

1、脏读：一个事务读到另外一个事务还没有提交的数据。
解决方法：把事务隔离级别调整到READ COMMITTED，即SET TRAN ISOLATION LEVEL READ COMMITTED。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果，因为此时事务以已经把自己的更改ROLLBACK了，所以事务二可以返回正确的结果。
　　

　　

2、更新丢失：2个事务同时修改一个数据，必定有一个事务的更新丢失。

解决方法：把事务隔离级别调整到READ COMMITTED，即SET TRAN ISOLATION LEVEL READ COMMITTED

　　

3、 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。
解决方法：把事务隔离级别调整到REPEATABLE READ。使用SET TRAN ISOLATION LEVEL REPEATABLE READ。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果。

　　

4、幻象读：一个事务先后读取一个范围的记录，但两次读取的纪录数不同。
解决方法：把事务隔离级别调整到SERIALIZABLE。使用SET TRAN ISOLATION LEVEL SERIALIZABLE。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果。

 　　

【2.3】隔离级别与事务现象的关系

　　

5、使用行版本（Row Version）的隔离级别 Snapshot

　　在默认的隔离级别Read Commited下，在执行读操作时，事务申请shared lock，读写操作相互阻塞。在隔离级别Read Uncommitted下，事务不会申请shared lock，因此读操作不会阻塞写操作，但是读操作可能会读到脏数据。脏数据是指被其它尚未提交的事务修改之后的数据值，不是指更新之前的数据值。

　　行版本是指存储在tempdb中，含有数据行和TSN的数据。数据表的一个Data Row，可以有多个Row Version。修改操作发生时，SQL Server 创建一个Row Version，将Original Row复制到Row Version，并将当前事务的TSN也存储在Row Version中。因此，Row Version存储的是修改之前的数据值。

　　SQL Server 提供Snapshot隔离级别，用于读取修改之前的数据值。在Snapshot隔离级别下，事务在修改任何数据之前，先将原始数据行复制到tempdb，创建数据行的一个原始版本（Row Version)，注意，SQL Server只会复制被修改的数据行，对于未修改的数据行，不会保存行版本数据。后续其他事务的读操作都去读该复制的行版本。在Snapshot隔离级别下，读写操作不会互相阻塞，使用行版本控制能够提高事务的并发性，但是有一个明显的缺点，虽然用户读到的不是脏数据，但是数据可能正在被修改，很快就要过期。如果根据这个过期的数据做数据修改，可能会产生逻辑错误。

6，READ COMMITTED Snapshot隔离级别

在Read Committed 隔离级别下，事务不能读取被其他事务修改，但尚未提交的数据，即只能读取已提交更新的数据，READ COMMITTED隔离级别的行为受到数据库选项：

READ_COMMITTED_SNAPSHOT的影响：

• 如果设置RCS选项为OFF（默认设置），数据库引擎使用Shared Lock阻止其他事务修改当前事务正在读取的数据；当读取被其他事务修改，但尚未提交更新的数据行时，该读操作将被阻塞；
  • If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed.
• 如果设置RCS选项为ON，数据库引擎使用行版本化（Row Versioning）的数据实现语句级别的一致性，不会阻塞其他事务的写操作，但只能读取已提交更新的数据
  • If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

7.READ COMMITTED Snapshot隔离 与 Snapshot 隔离级别的区别

• 当数据库选项 READ_COMMITTED_SNAPSHOT 设置为ON，Read Committed隔离级别使用Row Version提供语句级别（Statement-Level）的读一致性；
  • When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement. 
• Snapshot隔离级别使用Row Version 提供事务级别（Transaction-Level）的读一致性。在当前事务开始时，任何读操作，都基于相同的数据库snapshot。当读取被其他事务修改的数据行时，从tempdb中获取行版本数据。使用Snapshot隔离级别时，必须设置数据库选项ALLOW_SNAPSHOT_ISOLATION为ON；
  • When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started.

• 在snapshot 和 read committed snpshot隔离级别下，事务读取的数据都是已提交的；
• 注意语句级别的读一致性和事务级别的读一致性是snapshot 和 read committed snpshot 最大的区别：
  • 事务级别的读一致性是指：在事务开始，到事务提交期间，该事务持有数据的一个快照。如果在该事务活动期间，其他事务更新表数据，该事务只会读取快照数据，不会读取到被其他事务更新的数据值；
  • 语句级别的读一致性是指：单个语句（single statement）看到的数据是一致性的；在当前事务活动期间，事务中的语句能够读取到被其他事务提交更新的数据值；例如，在语句stmt1执行时，事务没有提交更新，stmt1看到Reader1的值是2；当语句stmt2执行时，事务提交更新，stmt2看到Reader2的值是3；

概念总结：

　　关于快照：深入参考 快照的隔离级别

　　已提交读快照只影响语句级别的锁定行为，而快照隔离影响整个事务。

　　用更专业一点的术语来说：

　　　　 快照化 事务开始时可用的最后提交版本

　　　　 快照读已提交 语句前可用的最后提交版本 

• 在 Read Uncommitted隔离级别下，读操作不会申请Shared Lock；
• 在 Read Committed（不使用row-versioning），Repeatable Read 和 Serializable隔离级别下，都会申请Shared Lock；
• 在 Read Committed（不使用row-versioning） 隔离级别下，在读操作执行时，申请和持有Share Lock；一旦读操作完成，释放Shared Lock;
• 在 Repeatable Read 和 Serializable隔离级别下，事务会持有Shared Lock，直到事务结束（提交或回滚）；
• 在Serializable隔离级别下，事务会持有范围Shared Lock（Range Lock），锁定一个范围，在事务活跃期间，其他事务不允许在该范围中进行更新（Insert 或 delete）操作；

【3】事务隔离级别的总结

实例总结：

1.READ UNCOMMITTED 

概述：不作任何锁
READ UNCOMMITTED:未提交读，读脏数据
默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改.
READ UNCOMMITTED:读操作不申请锁,运行读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁.

2.READ COMMITTED

概述：读的时候加共享锁（读完就释放，不会在一个事务过程中保持共享锁）
　　   可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行）。
READ COMMITTED（已提交读）是SQL SERVER默认的隔离级别，可以避免读取未提交的数据，隔离级别比READ UNCOMMITTED未提交读的级别更高;
该隔离级别读操作之前首先申请并获得共享锁，允许其他读操作读取该锁定的数据，但是写操作必须等待锁释放，一般读操作读取完就会立刻释放共享锁。

注意：但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.

3.REPEATABLE READ(可重复读)：

概述：读的时候加共享锁（一个事务过程中保持共享锁），避免不可重复读，实现可重复读。
保证在一个事务中的两个读操作之间，其他的事务不能修改当前事务读取的数据，该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成，所以此隔离级别查询完并提交事务很重要。

4.SERIALIZABLE
　　即所有事务只能串行执行。只有在这种隔离级别下，事务之间才会完全无干扰，但并发性最低。

5、Snapshot

　　使用行版本（Row Version）的隔离级别

　　在默认的隔离级别Read Commited下，在执行读操作时，事务申请shared lock，读写操作相互阻塞。在隔离级别Read Uncommitted下，事务不会申请shared lock，因此读操作不会阻塞写操作，但是读操作可能会读到脏数据。脏数据是指被其它尚未提交的事务修改之后的数据值，不是指更新之前的数据值。

　　行版本是指存储在tempdb中，含有数据行和TSN的数据。数据表的一个Data Row，可以有多个Row Version。修改操作发生时，SQL Server 创建一个Row Version，将Original Row复制到Row Version，并将当前事务的TSN也存储在Row Version中。因此，Row Version存储的是修改之前的数据值。

　　SQL Server 提供Snapshot隔离级别，用于读取修改之前的数据值。在Snapshot隔离级别下，事务在修改任何数据之前，先将原始数据行复制到tempdb，创建数据行的一个原始版本（Row Version)，注意，SQL Server只会复制被修改的数据行，对于未修改的数据行，不会保存行版本数据。后续其他事务的读操作都去读该复制的行版本。在Snapshot隔离级别下，读写操作不会互相阻塞，使用行版本控制能够提高事务的并发性，但是有一个明显的缺点，虽然用户读到的不是脏数据，但是数据可能正在被修改，很快就要过期。如果根据这个过期的数据做数据修改，可能会产生逻辑错误。

6，READ COMMITTED Snapshot

　　读已提交快照隔离级别

在Read Committed 隔离级别下，事务不能读取被其他事务修改，但尚未提交的数据，即只能读取已提交更新的数据，READ COMMITTED隔离级别的行为受到数据库选项：READ_COMMITTED_SNAPSHOT的影响：

• 如果设置RCS选项为OFF（默认设置），数据库引擎使用Shared Lock阻止其他事务修改当前事务正在读取的数据；当读取被其他事务修改，但尚未提交更新的数据行时，该读操作将被阻塞；
  • If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed.
• 如果设置RCS选项为ON，数据库引擎使用行版本化（Row Versioning）的数据实现语句级别的一致性，不会阻塞其他事务的写操作，但只能读取已提交更新的数据
  • If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

测试：
1.RC模式

场景1 问：2中得到的数据是update之前的还是update之后的？

1 update
2 select
1 rollback
session 1:
1 begin tran a
2 select * from test104
3 waitfor delay '00:00:10'
4 update test104 set name = 'b' where name = 'a'
5 waitfor delay '00:00:10'
6 select * from test104
7 rollback tran a
session 2:
select * from test104
结论：RC模式到底是如何避免脏读的呢，我一直不太明白，后面我终于明白了，这里用实例实测说明；
【1】session 1执行1-3行数据时，session2也可以执行查询，并且两个会话结果相同
【2】当session 1 执行 4行后，只要事务没有执行到提交或者回滚， session 2 无法进行查询，这应该是加了排它锁的原因。