SQL Server 中的6种事务隔离级别简单总结分分快三

作者:分分快三全天计划网站

   分分快三全天计划网站 1

可以看到SQL Server通过MVCC多版本控制机制在3、4两种隔离级别下实现select语句的不加锁读取,避免了阻塞。在已提交读快照隔离级别下通过mvcc实现了select不阻塞,但是依然会有不可重复读和幻读现象。在快照隔离级别下通过mvcc实现了select不阻塞,同时由于是事务级的快照,也顺带避免了不可重复读和幻读。因此可以发现幻读的解决方式目前只有两种:1是键范围锁,2是mvcc机制的事务级镜像(即snapshot隔离级别的方式)。

2,数据库选项READ_COMMITTED_SNAPSHOT(简称RCS)

基于行版本控制的隔离级别

2.已提交读

2,使用Read_Committed_Snapshot隔离级别

 Session级别设置快照隔离级别:set transaction isolation level snapshot

不可重复读:由于其他事务的修改,导致同一事务中两次查询读到的数据不同

3,READ COMMITTED Snapshot隔离级别

  基于行版本控制的已提交读隔离级别最大的特点是,当前读取的数据是,其他Session已修改尚未提交之前的版本,但是当前事物尝试修改时,可以成功提交
  这样一来,就忽略掉了当前事物运行期间,其他事物修改且提交的那个版本的数据,有点绕,需要慢慢理解。

分分快三全天计划网站 2

在任何隔离级别下,事务在执行写操作时都申请互斥锁(exclusive lock),持有互斥锁直到事务结束,互斥锁不受隔离级别的控制;而共享锁(Shared Lock)受到隔离级别的控制,隔离级别影响Shared Lock的申请和释放:

  

可能有人对幻读和不可重复读的定义不太理解,两者最大的区别实质上在于加锁的不同,后边会有讲解。

  • 在 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个概念,可以说事务隔离级别就是为了避免这3种情况出现的。

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

DBCC UserOptions

 分分快三全天计划网站 3

序列化读加的锁与已提交读有区别,此隔离级别下select操作对索引键加的是键范围锁,而不是普通的S、U、X、IS、IU、IX等。

3,Snapshot 使用乐观并发模式

分分快三全天计划网站 4

四、除以上4种隔离级别外SQL Server还支持使用行版本控制的其他两个事务隔离级别:

  • 在读数据时是否使用共享锁,申请何种类型的锁;
  • 事务持有读锁的时间;
  • 读操作引用被其他事务更新,但尚未提交的数据行时,控制读操作的行为:
    • 被阻塞,等待其他事务释放互斥锁;
    • 获取更新之前的数据值,从tempdb中读取行版本,该行版本在事务开始时已经提交;Retrieves the committed version of the row that existed at the time the statement or transaction started.
    • 读没有提交的数据,获取更新之后的数据值;

特点:相比为提交读隔离级别,解决了未提交读隔离级别下的读取“脏数据”的问题,
存在的问题:存在不可重复度或者幻读的问题。

1,在Snapshot隔离级别下,更新操作创建Row Version

 

幻读:由于其他事务的修改,导致同一事务中两次查询读到的记录数不同

1,启用Snapshot隔离级别

 

三、ANSI/ISO标准定义了下列事务隔离级别,SQL Server数据库引擎支持全部这4种隔离级别:

  • 如果设置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.

 不可重复读隔离级别下存在的幻读现象(一个事物中,同样的条件,读到的数据行数不一致)

两者的开启方式为:

参考文档:

 

2、使用READ_COMMITTED_SNAPSHOT,则直接执行下列ALTER语句修改,是在默认的READ COMMITTED隔离级别下修改的,此隔离级别修改后永久生效,使用dbcc useroptions查看可以看到事务隔离级别被全局的修改成了read committed snapshot。

4,Snapshot 隔离和 Row Version的工作模式

 

举例如下:

Snapshot Isolation in SQL Server

   分分快三全天计划网站 5

Ps:对于序列化加的键范围锁是否是我上边所说的那么精确,还需要具体实验,这里只是根据官网猜测会使用多余的一个键范围锁锁定可能造成幻读的记录(总的键范围锁数目为n 1个,n为满足查询条件的行数),具体实验方法参见我的另一篇博客,有兴趣的可以试试。

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

已提交读隔离级别下存在不可重复读的现象(两次读取的同一行数据结果不一致)

Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version.

   分分快三全天计划网站 6

Ps:关于Mvcc机制的实现方式参考,但是此文中关于snapshot和read_committed_snapshot的解释有些矛盾,文中把这两种隔离级别混淆了,但是事实上通过试验可以看到这两种isolation level的差别与Mysql中RR和RC下一致性读的差别是很相似的。正如我之前所写的snapshot实现的是事务级的一致性,而read_committed_snapshot实现的是语句级的一致性。

Snapshot隔离级别使用乐观并发模式,如果一个Snapshot 事务尝试去提交数据行的更新,但是该数据行已经被其他事务修改,并且修改的时间早于当前事务开始的时间,那么SQL Server将当前事务作为失败者,并回滚其事务操作。乐观并发模式用于冲突较少的环境中,如果Application在更新数据时经常发生冲突,Snapshot隔离级别可能不是最好的选择。

已提交读

3.已提交读快照

The net effect of snapshot isolation is that the transaction sees all of the data as it existed at the start of the transaction, without holding or placing any locks on the underlying tables. This can result in performance improvements in situations where there is contention.

 

ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;

事务的隔离级别共有5个,使用SET命令修改Session-Level的隔离级别,使用DBCC UserOptions 查看当前Session的隔离级别:

set transaction isolation level repeatable read;
运行当前Session不能读取其他事务已修改但是尚未提交的数据,并且当前Session运行期间,其他Session不能修改当前Session读取到的数据
也就是说,当前Session运行期间,读取到的数据是被加了共享锁的,所加的共享锁一直保持,直到事务提交的时候才释放。
相比已提交读最大的特点就是事务运行期间,共享锁将一直保持,直到当前Session事务提交,
因此可以保持当前Session读取到的数据不被其他Session修改,所以就不存在两次读取的数据不一致的现象。
可重复读隔离级别解决了不可重复读的问题,但依旧存在幻读的情况。

参考文档:

一旦启用Snapshot隔离级别,在事务中执行更新操作时,SQL Server将被更新的数据行的原始版本存储在tempdb中,即在tempdb中保存数据行的Original data,因此,读取行版本的数据,都只能读取到数据行被更新之前的值。每一个事务都拥有一个唯一的,递增的顺序号,记作TSN(Transaction Sequence Number),TSN能够唯一标识一个事务,每一个行版本都存储一个TSN,标识创建该行版本的事务。

 分分快三全天计划网站 7

用官网的一句话来描述两者区别就是:READ_COMMITTED_SNAPSHOT提供语句级的一致性,SNAPSHOT事务隔离级别提供事务级的一致性。

分分快三全天计划网站,五,Snapshot隔离级别(翻译MSDN)

  本文简单阐述了SQL Server中的几种隔离级别,SQL Server实现了SQL99定义的四个标准隔离级别,并且额外实现了两个快照隔离级别。
  需要说明的是,不同的DBMS的默认隔离级别和对隔离级别的实现是不完全一样的,也不一定是完全按照SQL99定义的四个标准隔离级别来实现的,
  因此在做事物控制的时候,需要了解具体的隔离级别以及具体特性。

五、全部6种隔离级别的加锁模式:

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

可重复读

ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON; --需要单用户模式下修改,因为要加库级别的独占锁。

 然后执行如下语句修改事务隔离级别:(修改后只在会话级别生效,无法修改全局级别的事务隔离级别)

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

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. 

 

一、事务隔离级别控制着事务的如下表现:

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;

 

在执行写操作时,事务总是持有互斥锁,直到事务结束才释放,互斥锁不受事务隔离级别的影响。在SQL Server中,互斥锁和任意锁都不兼容,在同一时间,同一个数据行上,只能有一个事务持有互斥锁,就是说,写操作是顺序进行的,完全隔离的,不能并发执行。隔离和并发,此消彼长。

当前Session不能读取其他Session已修改但未提交的数据(不允许脏读)
当前Session读取的数据上的共享锁一直保持直到事务提交(可重复读)
当前Session事务提交之前,其他Session不能插入当前Session中读取的键值(解决了幻读的问题)
set transaction isolation level serializable
或者开启事务之后对表加holdlock提示
select * from table with(holdlock) where id = n
可序列化解决了另外一个非常经典的问题,使用update table with(holdlock) 或者select * from table with(xlock,holdlock),并发情况下的“存在则更新不存在则插入”重复插入的问题。
参考:

有主键为1,5,8,9,10的记录,select ... where col between 3 and 7;会使用键范围锁将5这条记录锁定,除此之外还会用一个键范围锁将346这几个虚幻的记录也锁定,这样就不能在读取操作期间插入数据了,可以防止幻读。

前提是必须设置数据库选项ALLOW_SNAPSHOT_ISOLATION为ON;一旦启用RCS选项,在默认的Read Committed 隔离级别中,事务访问版本化的数据行。在RCS隔离级别下,事务有两个特性:

 

READ_COMMITTED_SNAPSHOT是指Select语句总是读取最新的已提交的数据,即如果有DML事务正在执行,那么select语句不会被阻塞而是读取这些DML事务预先生成的前镜像,这种读只会在表上加Sch-S锁,其他的行锁页锁全部没有。DML数据一旦提交,再次执行Select语句就会立马读到新的数据。

1,使用snapshot 隔离级别

数据库级别设置为基于行版本控制的已提交读隔离级
alter database Test set read_committed_snapshot on;
go

键范围锁的机制基本与Mysql中的范围锁相似,主要是为了防止幻读,其机制在于select操作不但会将读到的键值锁定,还会将上下键值的范围也锁定。

alter database current
set allow_snapshot_isolation on;

具体执行现象如下:
存在的问题就是,Session1第一次读取的时候,读取的Id = 1数据的那么是AAA,实际上此时其他Session2已经将Id = 1的那么修改为了Update_AAA,
随后Session2事物提交,当前Session执行修改的时候,忽略了Session2修改后的数据,可以直接将数据修改为AAA
需要注意的是,Session1修改成功的前提是Session2的事物提交,如果Session2修改事物没有提交,Session1的修改操作被阻塞。

  1. 读取数据时是否占用锁以及所请求的锁类型。
  2. 占用读取锁的时间。
  3. 引用其他事务修改的行的读操作是否:

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

快照隔离级别最大的特点是,当前Session读取其他事物修改的数据的时候,不会被阻塞,读取的是其他事物已经修改,但是尚未提交的数据
与快照隔离级别相对,当前Session尝试修改“在其他其他Session中提交修改之后的数据”,可以成功提交。
具体过程如下,从时间的维度来看,步骤如下
1)Session2 开启事物,修改Id =1的数据,暂不提交
2)Session1 读取id=1的数据,不会被阻塞,读取到的是Session2修改之前的数据的版本
3)Session2修改Id =1的数据之后,事物提交
4)Session1尝试修改Id=1的数据,成功提交,

因此四种隔离级别与脏读、幻读、不可重复读的对应情况如下:

分分快三全天计划网站 8分分快三全天计划网站 9

 

此隔离级别下可以避免不可重复读,但是不可避免幻读。DML正常加锁。

Isolation Levels in the Database Engine.aspx)

  分分快三全天计划网站 10

  • 一个是全新的事务隔离级别----快照隔离级别。

step2,修改Session-Level的隔离级别为snapshot

截图中第一行的备注没有修改过来,应该是快照已提价读隔离级别

SQL Server特有的隔离级别,主要是为了匹配Oracle的已提交读实现的功能,在此隔离级别下,select只会对表加一个Sch-S锁,因此select不会引发在阻塞,但是会加大tempdb的使用量。DML正常加锁。

  • 当数据库选项 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;

可序列化锁定的原理是加范围锁的方式来实现的,当一个Session发起了请求之后,对于当前Session范围内的数据,不管是否存在,都加一个共享锁。
比如在可序列化的隔离级别之下,select * from table with where id>=100 and id<= 120
在Session执行期间,SQL Server会锁定 100<=id<= 120这个范围的数据,不管表中这个区间是否存在数据, 都锁定这个Id的范围,不允许该Id范围的数据写入。
也即100<=id<= 120这个范围被所锁定(无法增加删除或者修改这个范围的数据)

4.快照

Snapshot隔离级别实现事务级别的数据一致性,这意味着,在单个事务中的所有查询语句,看到的是相同版本的数据。在Snapshot隔离级别下,事务在读取数据不需要加行级锁或页级锁,读写操作互不阻塞。

  行版本控制的已提交读隔离级别的问题也很明显,当前Session读取数据的时候,是其他事物修改之前的版本,当前Session对读取到的数据可以在其他事物提价之前的版本上执行修改,
  而忽略了当前Session在读和写的间隔期间,其他Session修改并且提交事物的影响,为此可能会产生一定程度的影响。

脏读:读到了其他事务已修改但未提交的数据

When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row. The following is the sequence of events that occurs:

 

select语句对读取的数据正常加锁,但是不等事务结束才释放锁,而是读完一个页就会释放,会出现不可重复读和幻读。DML语句正常加锁。

在SNAPSHOT隔离级别下,任何写操作都会将更新之前的数据行保存到tempdb中,读取操作要么从Original Database的数据表中读取数据,要么从tempdb中读取行版本数据。Snapshot隔离级别指定:在一个事务中,任何语句读取的数据,是事务一致性的版本。事务一致性是指在事务开始时,在表级别创建数据快照,只能识别其他事务已提交的数据更新。在事务开始之后,当前事务不会识别其他事务执行的数据更新。Sanpshot隔离级别实现事务级别的数据一致性。SQL Server 使用tempdb来存储行版本化(row versioning)的数据,如果数据更新较多,存储的行版本太多,会导致tempdb成为系统瓶颈。

特点:未提交读是最低的一种隔离级别。
存在的问题:脏读,不一致读,幻读等。

需要特别提醒的是:虽然Mysql、Oracle所支持的事务隔离级别也基本遵循ANSI标准,但却有很大区别:

  • 事务使用行版本(Row version)代替加锁,读操作不会阻塞其他事务的写操作;
  • RCS隔离级别保证语句级别的事务一致性,查询语句只能读取在该语句执行时已经提交的数据,如果在该语句执行时数据更新尚未提交,该语句读取不到;

可序列化

    • 在该行上的排他锁被释放之前阻塞其他事务。
    • 检索在启动语句或事务时存在的行的已提交版本。
    • 读取未提交的数据修改。

A snapshot transaction always uses optimistic concurrency control, with holding any locks that would prevent other transactions from updating rows. If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.

特点:相比前一种隔离级别,可重复读解决了已提交读隔离级别的不可重复读的问题,也即两次读取的同一行数据是一致的
存在的问题:相比已提交读,依旧存在幻读的问题。

以上说明事务隔离级别主要针对读操作来说的。(DML语句我们可以不考虑事务隔离级别,因为任何事物隔离级别下DML的加锁都很严格,属于得不到就等待的类型)

SQL Server 数据库级别默认的事务隔离级别是Read Committed,用户不能修改Database-Level默认的隔离级别,但是,用户能够修改Session-Level默认的事务隔离级别。Session-Level默认的事务隔离级别是Read Committed,该隔离级别受到数据库选项 READ_COMMITTED_SNAPSHOT 的影响,决定Read Committed隔离级别是使用行版本控制事务的读操作,还是使用加共享锁来控制事务的读操作,在默认的Read Committed隔离级别下:

 总结:

1、如果要开启SNAPSHOT事务隔离级别,需要预先设置ALLOW_SNAPSHOT_ISOLATION为ON,且目前只能修改会话级别的事务隔离级别。

SQL Server支持使用Row Version的隔离级别,事务的读操作只申请SCH-S 表级锁,不会申请Page 锁和Row 锁,事务的修改操作仍然申请锁

   分分快三全天计划网站 11

5.可重复读

  • 创建一个新的事务,为其分配TSN,一个唯一,递增的序号;
  • snapshot事务从数据表中读取数据行,从tempdb中读取行版本(row version),该行版本的TSN最接近当前事务的TSN,但比当前事务的TSN小;
  • 在创建Snapshot时,从已提交的事务中获取行版本数据,如果行版本数据标识的事务尚未提交,那么从更早的事务中获取已提交更新的数据;
  • 事务从tempdb中读取行版本数据,事务不会看到新插入的数据,因为插入数据的TSN比当前事务的TSN大;
  • 事务能够看到被其他事务删除的数据,前提是删除数据的事务的TSN比当前事务的TSN大,这是因为其他事务将行版本保存到tempdb中,当前事务从tempdb中读取行版本数据;

数据库级别设置快照隔离级别
alter database Test set allow_snapshot_isolation on;

select不对读取的数据加锁,会有脏读出现,相当于为select语句添加了with nolock选项。DML语句正常加锁。

  • 当其他事务未提交更新时,读取行版本化的数据,即读取修改之前的数据值;
  • 当其他事务提交数据更新后,读取修改后数据值;
  • 由于该隔离级别不会申请共享锁,因此不会阻塞其他事务的更新操作;
  • 能够更新由其他事务修改的数据;

可序列化隔离级别解决了幻读的问题,也就是说,当前事物的两次读中间,其他Session对当前Session读取数据范围之内的数据修改的时候,会被阻塞,直到当前事物提交。

6.序列化读

当启用Snapshot隔离级别时,每一个更新数据的操作都会在tempdb中存储该行的原始副本,术语叫作行版本(RowVersion),SQL Server为每个行版本添加事务的TSN,该TSN能够唯一标识更新操作所在的事务。读操作在读数据时,按照以下顺序进行:

 分分快三全天计划网站 12

  • Oracle只支持已提交读和序列化读。
  • Mysql默认的的可重复读隔离级别通过范围锁实现了避免幻读。

Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. 

  分分快三全天计划网站 13

1.未提交读

 分分快三全天计划网站 14

分分快三全天计划网站 15
--将当前事物设置为已提交读快照隔离级别
set transaction isolation level read committed

同上,select也只加Sch-S锁,唯一区别在于实现的一致性读是事务级别的,即快照在tempdb中保留的时间更长。DML正常加锁。

一,事务的隔离级别

(1)快照隔离级别(snapshot)

SNAPSHOT隔离级别与上述的区别在于,如果你在同一个事务内执行两次相同的select语句,那么即便在这两次select语句之间发生了数据更改且提交,两次读到的数据也是一样的。

alter database current
set allow_snapshot_isolation on;

alter database current 
set read_committed_snapshot on;

 

二、脏读、不可重复读、幻读的区别:

  • 如果设置选项READ_COMMITTED_SNAPSHOT为OFF,那么事务在执行读操作时申请共享锁,阻塞其他事务的写操作;
  • 如果设置选项READ_COMMITTED_SNAPSHOT为ON,那么事务在执行读操作时使用Row Versioning,不会申请共享锁,不会阻塞其他事务的写操作;

 

总结:

The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

如下是未提交度的存在脏读一种演示(当然也会存在不一致读,幻读等情况)。

这里猜测快照读隔离级别下会在事务第一次select时在tempdb中建立一个完整的快照,而不是仅依赖于MVCC的行版本机制。

  • A new transaction is initiated, and it is assigned a transaction sequence number.

  • The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

  • The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

  • The transaction reads the version of the row from tempdb that was current as of the start of the transaction. It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

  • The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

  如下是可重复读隔离级别的幻读的现象,也即在同一个事物的两次读取期间,其他事物可以写入当前事物读取的数据(范围)

分分快三全天计划网站 16

step1,设置数据库选项

(2)基于行版本控制的已提交读隔离级别(read_committed_snapshot)

开始说过事务隔离级别主要就是控制读操作加什么锁,锁占用多长时间的的,因此只有搞清各事务隔离级别下的加锁机制才能彻底搞清事务隔离级别的概念和他们的不同。

三,启用快照隔离级别

未提交读

两者的区别在于:

四,引用徐海蔚老师的例子,测试隔离级别的行为

快照隔离级别最大的特点是,当前Session读取其他事物修改的数据的时候,不会被阻塞,读取的是其他事物已经修改,但是尚未提交的数据
但是当前事物尝试修改“在其他其他事物中提交修改之后的数据”,会报错(快照隔离事务由于更新冲突已终止)。
具体过程如下,从时间的维度来看,步骤如下
1)Session2 开启事物,修改Id =1的数据,暂不提交
2)Session1 读取id=1的数据,不会被阻塞,读取到的是Session2修改之前的数据的版本
3)Session2修改Id =1的数据之后,事物提交
4)Session1尝试修改Id=1的数据,报错

  • 一个是默认的read committed隔离级别下的snapshot实现,严格来说并不算一个事务隔离级别,只是read committed的一个特殊形态。
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

  默认隔离级别,也即已提交读隔离级别下,存在一个明显的问题就是写会阻塞读,也就是说,一个写数据的事物未提交之前,会阻塞其他事物对当前操作数据的读取,直到当前写事物的操作提交。
  基于行版本控制的已提交读隔离级别下,写不会阻塞读,写数据的事物未提交之前,会将修改的数据之前的版本,写入临时数据库,
  读数据的事物在读取的时候,发现要读取的数据被修改,会转向临时库中读取出来一个写事物修改数据之前的版本,这样可以在一定程度上提高并发性(当然临时库会承担一定的压力)。
  SQL Server有两种基于行版本控制的隔离级别:快照隔离级别(snapshot)和基于行版本控制的已提交读隔离级别(read_committed_snapshot)
     两种行版本控制分别要基于数据级别开启allow_snapshot_isolation和read_committed_snapshot

可重复读加的锁与已提交读完全一致,区别在于只有在整个事务完成后才会释放锁,而不是读完一个页就释放,此种加锁方式也避免了不可重复读,因为事务期间其他DML无法获取资源上的锁。

 

  实际操作上看,如下

SQL SERVER – Difference Between Read Committed Snapshot and Snapshot Isolation Level

运行当前Session读取其他事务已修改但是尚未提交的数据,也即当前Session可以读取到“脏数据”。
当前Session不会对读取的数据加共享锁。
set transaction isolation level read uncommitted;
或者
select * from table with(nolock)

snapshot隔离级别不会阻塞其他事务的写操作,该隔离级别忽略数据的修改操作,只读取row versioning的数据,就是说,读取到的是数据修改之前的版本,当snapshot事务尝试修改由其他事务修改的数据时,产生更新冲突,写操作异常终止。

 

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

数据库中的事物是具有原子性(Atomicity),一致性(Consistemcy),隔离性(Isolation),持久性(Durability)四个特征。
在上述四个特性中的一致性和隔离性的实现中,是通过锁来实现对相同数据的访问隔离的。
事物的隔离级别又可以影响锁的申请和时间的时机。
因此,不同的事物隔离级别又可以对锁的申请和释放产生不同的影响,因此,在对数据库做事物控制的时候需要了解隔离级别对事物的影响。
SQL Server实现SQL99标准规定的事务的四个隔离级别(未提交读,已提交读,可重复读,序列化)之外,另外增加了两个隔离级别(快照个基于行版本的已提交读隔离级别)。
不同的隔离级别对控制脏读,不可重复读,幻读有一定的控制,也会并发有一定程度的影响,
隔离级别越低,并发性越高,但是产生脏读,不可重复读,幻读等可能性越大;随着事物隔离级别的提交,可以控制脏读,不可重复读,以及幻读的现象,但是并发性也会随之降低。
事物隔离级别和执行计划都可以影响锁(范围)的申请和释放时机,本文暂不讨论执行计划对锁申请的影响,仅在隔离级别上说明锁的申请和释放。
以下简单介绍SQL Server中的六个隔离级别以及每个隔离级别的特征,在此基础上说明每个隔离级别可能存在的问题解决方法。

设置数据库选项 ALLOW_SNAPSHOT_ISOLATION 为 ON,没有改变Session-Level的事务隔离级别,需要修改Session-Level的事务隔离级别为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 OFF; 
ALTER DATABASE CURRENT SET MULTI_USER;

set transaction isolation level read committed;
或者
select * from table  默认人就是已提交读
运行当前Session不能读取其他事务已修改但是尚未提交的数据。
如果其他事务提对当前Session读取的数据有修改且尚未提交,当前Session被阻塞。
原因是在以已提交读隔离级别情况下:当前Session会对读取的数据加共享锁,如果遇到读取的数据尚未提交,当前查询被阻塞。

在默认的隔离级别Read Committed下,使事务能够访问Row Versioning数据,需要将数据库选项READ_COMMITTED_SNAPSHOT设置为ON:

   从时间维度上看如下图所示

read committed snapshot隔离级别,读取行版本化的已提交数据:

View Code

在使用Snapshot隔离级别时,必须将当前Session的隔离级别设置为Snapshot,只有这样,当前事务才能访问Row Versioning的数据:

2,Snapshot隔离实现事务一致性

隔离级别定义事务处理数据读取操作的隔离程度,在SQL Server中,隔离级别只会影响读操作申请的共享锁(Shared Lock),而不会影响写操作申请的互斥锁(Exclusive Lock),隔离级别控制读操作的行为:

二,使用行版本(Row Version)的隔离级别

set transaction isolation level snapshot

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划