事务隔离等级划分

Bertha 。 2022-10-01 03:49 174阅读 0赞

隔离级别
    
    
    read uncommitted  --读未提交，解决了更新丢失，产生脏读
    
    
    read committed--读已提交，解决脏读，修改中的数据不可读，不可重复读。默认级别
    
    
    repeatable read--对查询行进行S锁，可重复读，产生幻读
    
    
    serializable  --进行行锁
    
    更新丢失指：最后的更新覆盖了前面事务的更新操作结果
    
    
    脏读指：读取的结果可能是没有提交的结果，如果事务回滚后，结果将不正确。
    
    
    不可重复读指：同一事务多次读取同一数据集的结果不一致。即事务之间有更新
    
    
    幻读：同一事务多次读取同一数据集的结果不一致。事务之间有插入操作。
    
    
     
    
    ISOLATION     LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY
    
    SET TRANSACTION 命令为当前事务设置特性。 它对后面的事务没有影响。 可用的事务特性是事务隔离级别和事务访问模式（读/写或者只读）。
    
    事务的隔离级别决定一个事务在同时存在其它并行运行的事务时它能够看到什么数据。 
    
    
    
    READ COMMITTED
    
    一条语句只能看到在它开始之前的数据。这是缺省。 
    
    SERIALIZABLE
    当前的事务中的所有语句只能看到在这次事务第一条查询或者修改数据的语句执行之前的数据。
    
    事务隔离级别在事务中第一个数据修改语句（SELECT， INSERT，DELETE，UPDATE，FETCH，或者COPY）执行之后就不能再次设置。
    
    但是在内部，实际上只有两种独立的隔离级别，分别对应读已提交和可串行化。 如果你选择了读未提交的级别，实际上你用的是读已提交， 在你选择可重复的读级别的时候，实际上你用的是可串行化，所以实际的隔离级别可能比你选择的更严格。 这是 SQL 标准允许的：四种隔离级别只定义了哪种现象不能发生，但是没有定义那种现象一定发生。
    
     使用SET   TRANSACTION   ISOLATION   LEVEL来控制由连接发出的所有语句的默认事务锁定行为 
    
    从SQLSERVER2005 开始 引入了  Snapshot Isolation Level（快照隔离级别），该隔离级别可以实现同ORACLE 行版本控制 相同效果。即 实现同一行记录的 读写 不互斥 效果。
    
    分为四个隔离等级
    
    READ UNCOMMITTED (读未提交隔离等级)
    
    表示不发出共享锁，也不接受排它锁。在 READ UNCOMMITTED 级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。
    
    READ COMMITTED (读已提交隔离等级)
    
    指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据，从而产生不可重复读取和幻像数据。这个级别存在两种类型：已提交读和已提交读快照隔离级别。应用哪种类型由数据库选项定义。已提交读级别会在读数据之前等待，直到阻塞锁被释放。已提交读快照级别会在数据被其他事务阻塞时使用行版本控制来读数据最后一次提交的版本。该选项是 SQL Server 的默认设置。
    READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置： 
              1、如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF（默认设置），则数据库引擎 会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。
              2、如果将 READ_COMMITTED_SNAPSHOT 设置为 ON，则数据库引擎 会使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。
    当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，您可以使用 READCOMMITTEDLOCK 表提示为 READ_COMMITTED 隔离级别上运行的事务中的各语句请求共享锁，而不是行版本控制。
        注意：设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中仅允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前，数据库中不允许有其他打开的连接。数据库不必处于单用户模式。
    
    REPEATABLE  READ (可重复读隔离等级)
    
    锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。
    
               a、 指定语句不能读取已由其他事务修改但尚未提交的行，并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。 
               b、 对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句，它会检索新行，从而产生幻读。由于共享锁一直保持到事务结束，而不是在每个语句结束时释放
    
    SERIALIZABLE (可串行化隔离等级)
    
    在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。该选项的作用与在事务内所有   SELECT   语句中的所有表上设置   HOLDLOCK   相同。范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。这样可以阻止其他事务更新或插入任何行，从而限定当前事务所执行的任何语句。这意味着如果再次执行事务中的任何语句，则这些语句便会读取同一组行。在事务完成之前将一直保持范围锁。
    
            a、 语句不能读取已由其他事务修改但尚未提交的数据。 
            b、 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。 
            c、 在当前事务完成之前，其他事务不能使用当前事务中任何语句读取的键值插入新行。
    
    
    隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read） 
    读未提交（Read uncommitted） 可能 可能 可能 
    读已提交（Read committed） 不可能 可能 可能 
    可重复读（Repeatable read） 不可能 不可能 可能 
    可串行化（Serializable ） 不可能 不可能 不可能 
    
    
     
    
      
    
    加深理解
    
    读已提交隔离级别
    
    当一个事务运行在这个隔离级别时， 一个 SELECT 查询只能看到查询开始之前提交的数据而永远无法看到未提交的数据或者是在查询执行时其他并行的事务提交做的改变。 （不过 SELECT 的确看得见同一次事务中前面更新的结果。即使它们还没提交也看得到。） 实际上，一个 SELECT 查询看到一个在该查询开始运行的瞬间该数据库的一个快照。 请注意两个相邻的 SELECT 命令可能看到不同的数据，哪怕它们是在同一个事务里， 因为其它事务会在第一个SELECT执行的时候提交．
    
    UPDATE、 DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 在搜索目标行的时候的行为和SELECT 一样： 它们只能找到在命令开始的时候已经提交的行。 不过，这样的目标行在被找到的时候可能已经被其它并发的事务更新（或者删除，或者锁住）。 在这种情况下，即将进行的更新将等待第一个更新事务提交或者回滚（如果它还在处理）。 如果第一个更新回滚，那么它的作用将被忽略，而第二个更新者将继续更新最初发现的行。 如果第一个更新者提交，那么如果第一个更新者删除了该行，则第二个更新者将忽略该行， 否则它将试图在该行的已更新的版本上施加它的操作。系统将重新计算命令搜索条件（WHERE 子句）， 看看该行已更新的办不那是否仍然符合搜索条件。如果是，则第二个更新继续其操作，从该行的已更新版本开始。 （如果是 SELECT FOR UPDATE 或者 SELECT FOR SHARE， 则意味着是把更新了的行版本锁住并返回给客户端。）
    
     
    
    可串行化隔离级别
    
    可串行化（Serializable） 级别提供最严格的事务隔离。 这个级别模拟串行的事务执行， 就好象事务将被一个接着一个那样串行的，而不是并行的执行。 不过，使用这个级别的应用必须准备在串行化失败的时候重新启动事务．
    
    当一个事务处于可串行化级别， 一个 SELECT 查询只能看到在事务开始之前提交的数据而永远看不到未提交的数据或事务执行中其他并行事务提交的修改。 （不过，SELECT 的确看得到同一次事务中前面的更新的效果。即使事务还没有提交也一样。） 这个行为和读已提交级别是不太一样，它的 SELECT 看到的是该事务开始时的快照，而不是该事务内部当前查询开始时的快照。 这样，一个事务内部后面的 SELECT 命令总是看到同样的数据。
    
    UPDATE、 DELETE、 SELECT FOR UPDATE、和 SELECT FOR SHARE 在搜索目标行上的行为和 SELECT 一样： 它们将只寻找在事务开始的时候已经提交的目标行。但是， 这样的目标行在被发现的时候可能已经被另外一个并发的事务更新了（或者是删除或者是锁住）。 在这种情况下，可串行化的事务将等待第一个正在更新的事务提交或者回滚（如果它仍然在处理中）。 如果第一个更新者回滚，那么它的影响将被忽略， 而这个可串行化的就可以继续更新它最初发现的行。 但是如果第一个更新者提交了（并且实际上更新或者删除了该行，而不只是锁住它）那么可串行化事务将回滚。一个可串行化的事务在可串行化事务开始之后不能更改或者锁住被其他事务更改过的行。只读事务从来没有串行化冲突。
    
    可串行化事务级别提供了严格的保证：每个事务都看到一个完全一致的数据库的视图。 不过，如果并行更新令数据库不能维持串行执行的样子，那么应用必须准备重试事务。 因为重做复杂的事务的开销可能是非常可观的，所以我们只建议在更新命令中包含足够复杂的逻辑， 在读已提交级别中可能导致错误的结果的情况下才使用。 最常见的是，可串行化模式只是在这样的情况下是必要的：一个事务连续做若干个命令， 而这几个命令必须看到数据库完全一样的视图。