澳门金沙vip 10

SQL Server 事务隔离级别

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

  • select语句检测要锁定的索引记录上是否有独占锁。
  • 如果有独占锁那么到undo中寻找最近的前镜像。
  • 如果没有独占锁那么添加S模式的record
    lock。

set name=’Jack_upd_快照’

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

 

–5打开另一条连接,设置事务隔离级别为(已提交读)

ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;
SET [GLOBAL | SESSION] TRANSACTION
transaction_characteristic [, transaction_characteristic] ...
transaction_characteristic:
ISOLATION LEVEL level
| READ WRITE
| READ ONLY
level:
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE

 

提到事物隔离级别就不能不提这3个概念,可以说事务隔离级别就是为了避免这3种情况出现的。

1.READ
UNCOMMITTED

where ID=2

3.已提交读快照

第一部分:概述

select ID,name as “修改前数据”

澳门金沙vip 1

关于具体的MVCC实现方式,MySQL官网并未提供具体的实现步骤,可以选择去查看源码,也可以参考Oracle和SQL
Server的实现机制。

(四)快照

这种隔离级别下可以避免脏读、不可重复读和幻读。

SQL Server 还支持使用行版本控制的两个事务隔离级别。一个是已提交读隔离的新实现,另一个是新事务隔离级别(快照)。

第三部分:总结

select 1,’Tom’

参考文档:

 

· 未提交读(隔离事务的最低级别,只能保证不读取物理上损坏的数据)

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

提到事务隔离级别就必须先明确以下三种读:

–新增数据,无法插入数据

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

此外:如果你使用READ
COMMITTED事物隔离级别,那么binlog模式必须修改为row模式!

 

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

这种隔离级别下可以避免脏读、不可重复读和幻读。

go

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

第二部分:事务隔离级别

–2新增记录

脏读:读到了其他事务已修改但未提交的数据
不可重复读:由于其他事务的修改,导致同一事务中两次查询读到的数据不同
幻读:由于其他事务的修改,导致同一事务中两次查询读到的记录数不同

use read_committed_SNAPSHOT_Test

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

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

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

你也可以在启动时添加–transaction-isolation启动项或者将其写入配置文件,来设置相应的全局事务隔离级别。

create table tbUnRead

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

3.REPEATABLE
READ

 

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

这种事务隔离级下select语句即便不加lock
in share
mode也使用lock_mode=S的行锁,select自成事务,锁直到事务结束才释放。

–2新增记录

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

同REPEATABLE
READ一样,这种隔离级别下也实现了一致性非锁定读,但区别在于此隔离级别下的一致性读是语句级的,即只能避免脏读,不能避免不可重复读和幻读。其实现方式大致是:

—4查询事务数量(由于没有回滚或提交事务)

两者的开启方式为:

MySQL遵循SQL:1992标准,提供READ
UNCOMMITTED, READ COMMITTED, REPEATABLE
READ和SERIALIZABLE四种事务隔离级别。InnoDB默认使用的事务隔离级别是REPEATABLE
READ。

select 2,’Jack’

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

这是MySQL的默认事务隔离级别。在一个事务当中第一次读会建立一个snapshot,同事务的相同select语句会读取这个snapshot来实现一致性非锁定读。

 澳门金沙vip 2

举例如下:

对于select
for update/select lock in share
mode/update/delete这些锁定读,加行锁模式取决于索引的类型:

—8,修改数据,无法修改数据

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

在这种隔离级别下,InnoDB只使用record
lock类型的行锁,不使用gap锁。

(五)可序列化

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

DML语句的加锁模式与REPEATABLE
READ一样。

–3开启事务,并进行更新

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

官网对于这个隔离级别的解释是只有将autocommit设置为0后select才会被隐式转换为lock
in share
mode的加锁模式,但是经测验发现在此模式下只要为select语句开启事务就会阻塞其他事物的更改,因此官网解释有误。

)

两者的区别在于:

参考自:https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

 澳门金沙vip 3

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

这种隔离级别下select语句是不加事务锁的,因此会产生脏读,这种事务隔离级别是应当完全避免的。除select语句以外的其他语句加锁模式与READ
COMMITTED一样。

澳门金沙vip 4

1.未提交读

一般来说我们没必要去修改默认的事务隔离级别,当然如果你的数据库并不在意幻读和不可重复读,可以修改未read
committed隔离级别,这样可以增加并发减少阻塞,据说淘宝也是这么干的。Oracle默认的事务隔离级别也是read
committed,同样不可避免幻读和不可重复读。

union

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

READ
UNCOMMITTED, READ COMMITTED, REPEATABLE
READ和SERIALIZABLE这四种事务隔离级别所提供的事务一致性是越来越强的,但是并发性却是却来越差的。

(三)可重复读

  • 一个是全新的事务隔离级别—-快照隔离级别。
  • 对唯一索引的访问只会添加record
    lock,而不会使用gap lock(即也没有next-key lock)。
  • 对非唯一索引的访问使用gap
    lock或者next-key lock,如果访问的记录不存在就是gap
    lock,否则就是next-key lock。

name nvarchar(20)

总结:

关于MySQL的锁机制,可以参考:

—创建测试数据库

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

2.READ
COMMITTED

select 1,’Tom’

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

用户可以自己修改会话或全局级别的事务隔离级别,语法如下:

union

4.快照

4.SERIALIZABLE

–查询数据,查询到的数据是上一次提交的数据

6.序列化读

SELECT @@TRANCOUNT

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

SELECT @@TRANCOUNT

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

 澳门金沙vip 5

5.可重复读

澳门金沙vip 6

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

set name=’Jack_upd’

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

set name=’Jack_upd’

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

select ID,name as “修改前数据”  from tbReadLevel

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

select 2,’快照测试’

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

 澳门金沙vip 7

澳门金沙vip 8

澳门金沙vip, 

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

go

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

set Transaction isolation level read committed

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

 澳门金沙vip 9

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

union

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

update tbReadLevel

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

 

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

–5开启事务,并进行更新

2.已提交读

go

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

insert tbUnRead 

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

–6查询数据,查询的数据是上一次提交的数据

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

SELECT @@TRANCOUNT

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

select 1,’Tom’

—7开启另一条连接,查询数据与修改数据 

name nvarchar(20)

union

begin tran

go

insert tbUnRead 

select 2,’Jack’

· 已提交读(数据库引擎的默认级别)

name nvarchar(20)

go

–3开启事务,并进行更新

)

5、6执行结果如下图。

快照隔离级别是SQL SERVER 2005之后版本新增的隔离级别,开启之后,允许事务过程中读取操作不受异动影响,事务中任一语句所读取的数据,均予事务激活时,就已经完成提交,符合事务一致性的数据行版本。所以只能查核事务激活之前已经完成提交的数据,也就是说可以查询已经完成提交的数据行快照集,但看不见已激活的事务正在进行修改的数据行。当使用快照隔离级别读取数据时不会要求对数据进行锁定,如果所读取的记录正在被某事务进行修改,它也会读取此记录之前已经提交的数据。故当某记录被事务进行修改时,SQL SERVER的TEMPDB数据库会存储最近提交的数据行,以供快照隔离级别的事务读取数据时使用。将Allow_SNAPSHOT_isolation设为ON,事务就会设置快照隔离级别。

—4查询事务数量(由于没有回滚或提交事务)

 

SET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED

    | READ COMMITTED

    | REPEATABLE READ

    | SNAPSHOT

    | SERIALIZABLE

    }

[ ; ]

–1.创建测试表

–1.创建测试表

 

澳门金沙vip 10

 

–7、8的执行结果如下,可以查询数据,但无法更新数据,如下图。

go