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

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

官网：https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#Lock_Basics

【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）的隔离级别

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 无法进行查询，这应该是加了排它锁的原因。

【最佳实践】

参考转自：https://www.cnblogs.com/princessd8251/p/4188947.html

（1）修改事务为快照事务

1. ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON

2. set transaction isolation level snapshot

假如我们不执行step1，只执行step2，然后开启事务进行查询，会报如下错误：

Msg 3952, Level 16, State 1, Line 3
Snapshot isolation transaction failed accessing database 'AdventureWorks' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

（2）读已提交快照的设置

ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;

set transaction isolation level Read Committed

--由于SQL Server默认的isolation level 就是Read Committed,所以这句可以不需要,这就意味着当设置了数据库参数READ_COMMITTED_SNAPSHOT后，

隔离级别就自动变成了read committed snapshot.

两种行版本控制隔离级别的差别：

　　READ_COMMITTED_SNAPSHOT 数据库选项为ON时， READ_COMMITTED事务通过使用行版本控制提供语句级读取一致性.同时在执行DML语句的时候，会把snapshot数据自动存储在tempdb里面。

　　哪怕当前session的隔离级别不是READ_COMMITTED.因为每个session的隔离级别是可以随时变的，所以只要数据库的这个选项设置为on的时候，就必须存储行版本数据，以提供行版本数据控制

ALLOW_SNAPSHOT_ISOLATION 数据库选项为ON时，SNAPSHOT事务通过使用行版本控制提供事务级读取一致性。

在执行DML的时候，会保持更多的行版本数据，以供需要行版本数据的snapshot隔离级别使用。所以这个可能会影响tempdb的使用。

　　行版本数据是在被更新的时候(DML),把前snapshot数据放到tempdb里面，可以在视图sys.dm_tran_version_store查到对于的记录.

select * from sys.dm_tran_version_store

（3）snapshot 与 read_commited_snapshot 的实际使用案例

--认清SQL_Server的基于行版本控制的两种隔离级别
--快照隔离级别(snapshot)和已提交读快照隔离级别(read committed snapshot)

--特点：在这两种隔离级别下，读取数据时不再请求共享锁，而且永远不会与修改进程的数据发生冲突，如果请求的行被锁定(例如正在被更新)，--SQL_Server会从行版本存储区返回最早的关于该行的记录

--说明：首先这两种隔离级别都是基于快照的实现模式，所以使用前必须修改数据库选项"允许快照隔离"为ON，否则
--        以下两种隔离级别将都被禁用：
        ALTER DATABASE DBNAME SET ALLOW_SNAPSHOT_ISOLATION ON
--        修改这个选项时可能会需要将数据库置为单用户模式
        ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--        修改完允许快照隔离后再将数据库重置为多用户模式
        ALTER DATABASE DBNAME set MULTI_USER
--    一、快照隔离级别是一种全新的隔离级别，在打开“允许快照隔离”选项后，不管是否使用快照隔离级别--        在更新数据时，SQL SERVER总是会在tempdb库中保存更改前的最后的行数据链接列表,从这里可以想到
--        将会影响SQL SERVER在更新数据时的事务性能。当然，该隔离级别的主要作用是提高并发，所以读取多，修改少的时候可大胆使用.
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
--    二、已提交读快照隔离级别，其实就是SQL Server默认隔离级别Read Committed的衍生品，或者说是
--        另一种版本的已提交读，打开此数据库选项的命令是：
        ALTER DATABASE DBNAME SET read_committed_snapshot ON
--        在这里大家要明白，它只是一个数据库选项开关，是在 READ COMMITTED 隔离模式下时才会起作用，而且
--        将改变整个数据库的全局行为。因为SQL SERVER默认就是在READ COMMITTED隔离模式下，所以在稍后的
--        示例中我们不会用到SET TRANSACTION ISOLATION LEVEL READ COMMITTED语句，但是我们心里要明白。

--适用情况：主要是读取数据的环境，在这种环境下偶尔需要修改操作并且很少发生更新冲突。
--区别：我们会在稍后的演示中进行说明，那样更容易理解一些。
--示例一：快照
--创建环境：
    IF DB_ID('DB_TEST') IS NOT NULL DROP DATABASE DB_TEST;
    GO
    CREATE DATABASE DB_TEST
    USE DB_TEST;
    GO
    IF OBJECT_ID('T_TEST','U') IS NOT NULL DROP TABLE T_TEST
    GO
    CREATE TABLE T_TEST(ID INT IDENTITY(1,1),COL VARCHAR(50))
    GO
    INSERT INTO T_TEST SELECT 'AAAAAAAAA' UNION ALL SELECT 'BBBBBBBBBB'
    GO
    SELECT * FROM T_TEST
    /*
        ID    COL
        1    AAAAAAAAA
        2    BBBBBBBBBB
    */
--    在连接1中执行如下语句（确保ALLOW_SNAPSHOT_ISOLATION已置为ON）
    USE DB_TEST;
    GO
    ALTER DATABASE DB_TEST SET ALLOW_SNAPSHOT_ISOLATION ON;
    GO
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
        UPDATE T_TEST SET COL='CCCCCCC' WHERE ID=2;
        SELECT COL FROM T_TEST WHERE ID=2;
--    通过输出的结果我们可以看到，在未完成的事务中ID=2的COL值从'B'变为'C',而且你应该注意到我这里没有使用快照隔离级别，还是用的SQL SERVER默认隔离级别，--    但是因为我们打开了ALLOW_SNAPSHOT_ISOLATION选项，这个时候，我们的事务应该在更改ID=2的COL值之前就把之前的行状态存储到了tempdb中,--    那么我们怎么才能证明这个猜测呢，动态视图sys.dm_tran_version_store可以帮助我们，执行：
    SELECT * FROM sys.dm_tran_version_store
--    你一定可以看到在版本存储区中已经有了一行数据了，接下来我们再打开一个连接2，执行如下SQL:
    USE DB_TEST
    GO
    --SET TRANSACTION ISOLATION LEVEL SNAPSHOT;    --这里我们先注释掉设置隔离级别为快照模式
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--    可以看到查询一直在等待，是因为我们在连接1中一直保持着该行的排它锁X。但是现在我们把该事务commit或rollback
--    掉，然后把快照隔离模式的注释打开，重新执行上面的语句，我们就可以看到
    /*
        BBBBBBBBBB
    */
--    我们可以想象到SQL SERVER在这种隔离级别下的查找思路，它会先去原表查找该行数据，待发现该行被锁后，则去
--    tempdb数据库存储的行版本列表中取出最近的一次数据，这样就避免了等待，但是前提是要求数据查询不用那么精确
--    的情况下，当然，你是否在这里忽略了一个问题，即：SQL SERVER仅会在修改该行数据前才会去存储最新的行版本，
--    而在修改的事务结束后，SQL SERVER并不会去更新之前的快照到最新的行版本，但是即使这样我们也不用担心，因为
--    这个时候原表的该行数据已经不是锁定状态，其他之后的查询依然会得到最新的数据。唯一注意的一点，我们还是用
--    代码说明：在连接1中用COMMIT TRAN 提交事务，然后继续执行连接2中的查询：
    SELECT COL FROM T_TEST WHERE ID=2;
--    我们发现数据还是之前的数据BBBBBBB，为什么？因为事务隔离级别！在事务中的任何地方读取该行数据时，它获取的
--    总是在事务开始时获取的数据，这里要牢记，因为他是稍后我们要说的已提交读快照隔离级别的第一个不同点。


--    接下来我们说说快照隔离级别的另一个特点：冲突检测，代码说明，简洁易懂：
--    在连接1中执行如下语句：
    USE DB_TEST;
    GO
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;--注意这里我们要设置隔离级别为快照模式
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--    这里我们可以得到一个数据，然后再打开一个连接2，执行如下SQL:
    USE DB_TEST;
    GO
    UPDATE T_TEST SET COL='DDDDDDD' WHERE ID=2;
--    回到连接1，继续执行SQL:
    UPDATE T_TEST SET COL='EEEEEEE' WHERE ID=2;
--    这时SQL SERVER 就会检测到你在连接1中事务开始时读取的数据已经与现在的数据发生了改变，所以就会报出更新
--    冲突的错误：
    /*
    消息 3960，级别 16，状态 4，第 1 行
    快照隔离事务由于更新冲突而中止。您无法在数据库'DB_Test'中使用快照隔离来直接或间接访问表 'dbo.T_TEST'，
以便更新、删除或插入已由其他事务修改或删除的行。请重试该事务或更改 update/delete 语句的隔离级别。
    */
--    这里，其实就是快照隔离级别和已提交读快照隔离级别的第二大区别了，READ COMMITTED SNAPSHOT不会检测更新冲突


--示例二：已提交读快照
--    在连接1中执行如下语句：
    ALTER DATABASE DB_TEST SET READ_COMMITTED_SNAPSHOT ON;--首先我们打开该数据库选项
    USE DB_TEST;
    GO;
    BEGIN TRAN
        UPDATE T_TEST SET COL='FFFFFFF' WHERE ID=2;
        SELECT COL FROM T_TEST WHERE ID=2;
--    在该事务里，你将得到你刚刚更新过的值FFFFFFFF
--    在连接2中执行如下语句：
    USE DB_TEST;
    GO
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--    这里你将得到连接1中的事务在修改数据之前的值，而非FFFFFF，这是肯定的。
--    这时我们提交连接1中的事务：
    COMMIT TRAN;
--    在连接2中再进行查询时，我们惊奇的发现与在快照中不同的是，我们竟然在未完成的事务2中得到了连接1中的事务
--    更改后的值！这也是为什么不会进行更新冲突检测的原因，不如我们测试一下：
--    将之前连接1中的事务提交或回滚，然后执行如下SQL:
    USE DB_TEST;
    GO
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;--这里我们显示指定隔离级别是因为刚才指定的快照隔离
-- 级别会在没有关闭的会话中一直有效。
    BEGIN TRAN
        SELECT COL FROM T_TEST WHERE ID=2;
--之后，我们再把连接2中的事务提交或回滚掉，执行如下SQL：
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    UPDATE T_TEST SET COL='aaaaa' WHERE ID=2;
    SELECT COL FROM T_TEST WHERE ID=2;
--    好了，这个时候我们再在连接1中更新这条在事务1中读取后但是在外部被更新过的数据：
    UPDATE T_TEST SET COL='测试已提交读更新冲突检测' WHERE ID=2;
--    我们发现更新可以正常进行，最后我们关闭所有连接，并更改数据库选项：
    ALTER DATABASE DB_TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;
    ALTER DATABASE DB_TEST SET READ_COMMITTED_SNAPSHOT OFF;

--总结：快照隔离模式是乐观并发模型，可以避免脏读、丢失更新、不可重复读、幻读、而且有更新冲突检测的特点。
--已提交快照读隔离模式和已提交读模式是相同的，都只能避免脏读，都无更新冲突检测，但是不同的是，已提交读快照隔离级别是乐观并发模型，并且读取数据不会发生等待
--另附所有隔离级别的允许或防止的问题等。
==============================================================================================
隔离级别        脏读        丢失更新        不可重复读    幻读        并发模型        更新冲突检测
----------------------------------------------------------------------------------------------
未提交读        是            是            是        是        悲观                否
----------------------------------------------------------------------------------------------
已提交读     　　否            是            是        是       悲观                 否
----------------------------------------------------------------------------------------------
可重复读        否            否            否        是        悲观                否
----------------------------------------------------------------------------------------------
可串行读        否            否            否        否        悲观                 否
----------------------------------------------------------------------------------------------
快照           否            否            否        否        乐观                 是
----------------------------------------------------------------------------------------------
已提交读快照    否            是            是        是        乐观                 否
==============================================================================================

（4）DDL语句不允许运行在 snapshot 事务

DDL Statements That Are Not Allowed Within Snapshot Isolation

The following statements are not allowed within a transaction that is running under snapshot isolation because of their disruptive potential on the snapshot copies of the data:

CREATE INDEX
CREATE XML INDEX
ALTER INDEX
ALTER TABLE
DBCC DBREINDEX
ALTER PARTITION FUNCTION
ALTER PARTITION SCHEME
DROP INDEX
Common language runtime (CLR) DDL

An attempt to run one of these statements will cause a severity level 16 message to be returned, such as:

Msg 3964, Level 16, State 1, Line 1
Transaction failed because this DDL statement is not allowed
inside asnapshot isolation transaction. Since metadata is not
versioned,a metadata change can lead to inconsistency 
if mixed within snapshot isolation.

refer：
SQL Server 2005 Row Versioning-Based Transaction Isolation   http://msdn.microsoft.com/en-us/library/ms345124%28SQL.90%29.aspx
Using Row Versioning-based Isolation Levels  http://msdn.microsoft.com/en-us/library/ms189050.aspx

【场景问题】

（1）快照事务下的更新冲突

情况推测：
　　（1）2个事务，几乎同时提交，在查询阶段，其获取到的数据是同一个版本
　　（2）但在有一个事务先提交完成的时候，后一个事务这个时候其实它获取到的查询版本已经是无效的了（已经过时不是最新）
　　（3）但它又不会重新去查一次，所以导致失败这样

行版本可以看作连续的lsn

前后匹配不上就没法写进去（不同行的更新、插入，无所谓LSN顺序，但同一行的更新必定要明确前后顺序）

　　U锁不兼容U锁，但是兼容S锁

　　开始更新的时候U锁要转换成X锁

　　所以死锁和更新冲突是很像的，一个是拿不到资源，一个是找不到匹配的版本

posted @ 2018-06-06 20:47 郭大侠1 阅读(1643) 评论(0) 编辑收藏举报

刷新页面返回顶部